fby with variable grouped columns

Community Support
1

if i want to wrap a fby select statement in to a function with a variable grp key

let’s say i have a select queries with fby on 3 columns, ab`c

select from tbl where time(max;time) fby ( a ; b ; c)

now if i want to generize to group by a variable of column

let’s say grp:ab`c, pass into a function

f:{[tbl ; grp]
 select from tbl where time=(max;time) fby …
}

how should I do it

2

q)show t:(time:asc 4?.z.T;a:1 2 1 2;b:1 2 1 2;c:1 2 1 2;d:4?100)

time         a b c d

02:59:16.636 1 1 1 66

07:14:12.294 2 2 2 36

08:31:52.958 1 1 1 37

10:50:05.304 2 2 2 44

q)select from t where time=(max;time) fby (a;b;c)

time         a b c d

08:31:52.958 1 1 1 37

10:50:05.304 2 2 2 44

q){[tbl;grp] ?[tbl;enlist(=;time;(fby;(enlist;max;time);(flip;(!;enlist grp;enlist,grp))));0b;()]}[t;`a`b`c]

time         a b c d

08:31:52.958 1 1 1 37

10:50:05.304 2 2 2 44

Tip: Use ‘parse’ to inspect the functional form of select statements and tinker with them to your liking :)

3

A functional select can achieve this. Something like:

wClause:enlist(=;time;(fby;(enlist;max;time);(flip;(!;enlist grp;enlist,grp))))

?[tbl;wClause;0b;()]

This starts with a dictionary, flips it into the desired table, and then applies they fby as usual.

 

4

i like to avoid a functional select if possible

q){[tbl;grp] select from t where time=(max;time) fby grp#0!t}[t;`a`b`c]

time         a b c d

05:50:33.545 1 1 1 1

09:36:00.090 2 2 2 90

Cheers,

   Attila