Re: SQL group by help

What

> On Dec 11, 2020, at 10:24 AM, Chris Stephens <cstephens16@gmail.com> wrote:
>
> 
> I'm trying to create a visual representation of a 6x8 grid of samples on a rack using the following SQL format:
>
> with rack_display as (
>     select sr.ts rack_ts
>         , sr.rack_id
>         , r.rack_barcode
>         , 1 as row_pos
>         , max(case when rack_well = 0 then 'A1: '||sample_barcode end) as col1
>         , max(case when rack_well = 1 then 'A2: '||sample_barcode end) as col2
>         , max(case when rack_well = 2 then 'A3: '||sample_barcode end) as col3
>         , max(case when rack_well = 3 then 'A4: '||sample_barcode end) as col4
>         , max(case when rack_well = 4 then 'A5: '||sample_barcode end) as col5
>         , max(case when rack_well = 5 then 'A6: '||sample_barcode end) as col6
>         from rack r
>                         , sample_rack sr
>             , sample s
>         where r.rack_id = sr.rack_id
>            and sr.sample_id = s.sample_id
>            and sr.rack_well < 6
>         group by sr.ts, sr.rack_id, r.rack_barcode, row_pos
>     union all
>     select sr.ts rack_ts
>         , sr.rack_id
>         , r.rack_barcode
>         , 2 as row_pos
>         , max(case when rack_well = 6 then 'B1: '||sample_barcode end) as col1
>         , max(case when rack_well = 7 then 'B2: '||sample_barcode end) as col2
>         , max(case when rack_well = 8 then 'B3: '||sample_barcode end) as col3
>         , max(case when rack_well = 9 then 'B4: '||sample_barcode end) as col4
>         , max(case when rack_well = 10 then 'B5: '||sample_barcode end) as col5
>         , max(case when rack_well = 11 then 'B6: '||sample_barcode end) as col6
>         from rack r
>                         , sample_rack sr
>             , sample s
>         where r.rack_id = sr.rack_id
>            and sr.sample_id = s.sample_id
>            and sr.rack_well >= 6
>            and sr.rack_well < 12
>         group by sr.ts, sr.rack_id, r.rack_barcode, row_pos
>     union all
> ...
> )
> select * from rack_display order by rack_ts, rack_id, row_pos;
>
> the "union all"s continue for another 6 blocks. reports would filter on rack_id and timestamp.
>
> if timestamps for each load of a rack were guaranteed to be the same, this would work. however, the "sr.ts" values
mayvary by a few seconds so there is potential for the "group by" to break. ts differences will be a minimum of 5
minutesfor each distinct load of a rack.  
>
> what i think i need is to manufacture a group by column based off rows in "sample_rack" that have "ts" values that
are< 1 minute from each other and rack_id is the same.  i'm coming up blank on how to accomplish that though. my first
thoughtwas to create an interval of +/- 1 min then find all rows that overlap and assign a group number but i'm not
surehow to accomplish that.  
>
> there's also no guarantee an entire rack is full of samples so some "cells" of display might be null. i think that
makesthe use of tablefunc crosstab a little harder. if i remember correctly, it does not handle missing values well.
i'mopen to any pivoting strategy. 
>
> anyways, i thought i'd reach out for ideas while i do my own digging.
>
> thanks for any input! let me know if i'm not being clear on the problem and desired outcome.
>
>

What stack are you usinglr. I would hope you could hand data off to front end which should have tools necessary