qsql select first last by group - Kdb+ / qStudio

Home Forums Kdb+ / qStudio qsql select first last by group

Tagged: 

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #166

    bigdata-dan
    Guest

    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

    #173

    admin
    Keymaster

    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

    #213

    admin
    Keymaster

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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.