qsql select first last by group

[bigdata-dan]

Hi,

I know I can select all the last entries in a table using the qsql:
select by sym from trade
rather than saying
select last price, last size, last date, last time, last exchange by sym from trade

Is there a similar shortcut for getting the first entry by grouping?

Thanks
Dan

[admin]

Hi Dan,

So here is the short qsql you mentioned for selecting the last entries by a grouping, simple “select by”:


q)t:([] sym:`A`A`B`A`B`C`C`C; ex:`p`p`p`p`o`o`o`o; v:til 8; p:asc 8?100.)
q)t
sym ex v p
-----------------
A p 0 12.53237
A p 1 16.94894
B p 2 26.85287
A p 3 37.97537
B o 4 50.51764
C o 5 56.72789
..
q)select by sym from t
sym| ex v p
---| -------------
A | p 3 37.97537
B | o 4 50.51764
C | o 7 88.14778
q)select by sym,ex from t
sym ex| v p
------| ----------
A p | 3 37.97537
B o | 4 50.51764
B p | 2 26.85287
C o | 7 88.14778


If we wrote this the long way and considered the parse tree:

q)parse "select last v, last p by sym from t"
?
`t
()
(,`sym)!,`sym
`v`p!((last;`v);(last;`p))


We could create the functional query dynamically.
We could then replace last with first, to dynamically generate and run our query.


q){ [t; byCols] c:cols[t] except byCols; ?[t; (); ((),byCols)!(),byCols; c!{(last;x)} each c]} [`t; `sym]
sym| ex v p
---| -------------
A | p 3 37.97537
B | o 4 50.51764
C | o 7 88.14778

q){ [t; byCols] c:cols[t] except byCols; ?[t; (); ((),byCols)!(),byCols; c!{(first;x)} each c]} [`t; `sym`ex]
sym ex| v p
------| ----------
A p | 0 12.53237
B o | 4 50.51764
B p | 2 26.85287
C o | 5 56.72789


Functional queries sometimes have their uses. Aaron Davies has some notes on them: http://www.q-ist.com/2013/03/my-kdb-user-meeting-presentation.html

[admin]

We have added some notes on creating functional select that you may find useful:
http://www.timestored.com/kdb-guides/functional-queries-dynamic-sql

[Author]

Posts