CODEWITHSUNDEEP
kdb+
P Emt
P Emt

Reputation: 35

query for selecting N records

I have a table tab that has cols date,sym,value and is sorted from oldest date to the recent.

I am trying to select the past N records for each sym and am not sure of the query for this. I know that I can select based on date being within a range but I needed it based on sym irrespective if value appeared on consecutive dates or not.

Upvotes: 1

Views: 664

Answers (2)

Cathal O'Neill
Cathal O'Neill

Reputation: 3179

Matt's suggestions using an fby and functional select are best if you want all columns in the table returned. If you only need the date, sym & price columns returned you could use

q)ungroup select -2#date,-2#price by sym from trade
sym  date       price
----------------------
APPL 2021.03.13 111.77
APPL 2021.03.13 111.85
CAT  2021.03.13 246
CAT  2021.03.13 246.27
GOOG 2021.03.13 206.24
GOOG 2021.03.13 206.21
NYSE 2021.03.13 60.67
NYSE 2021.03.13 60.97

Note that this can become tedious when selecting a large number of columns. In those cases it's better to stick with Matt's suggestions.

Upvotes: 2

Matt Moore
Matt Moore

Reputation: 2775

You could do this with fby and the virtual row number column i:

https://code.kx.com/q/ref/fby/

q){ select from tab where ({y in x#y}[x];i) fby sym }[-2]

date       sym  time                          src price size
------------------------------------------------------------
2014.04.21 AAPL 2014.04.21D16:29:03.253000000 N   24.98 3561
2014.04.21 AAPL 2014.04.21D16:29:03.558000000 N   24.98 2733
2014.04.21 CSCO 2014.04.21D16:28:56.265000000 O   35.6  8390
2014.04.21 CSCO 2014.04.21D16:29:44.572000000 L   35.61 2286
2014.04.21 DELL 2014.04.21D16:29:35.374000000 L   29.57 1444
2014.04.21 DELL 2014.04.21D16:29:39.979000000 N   29.56 216
2014.04.21 GOOG 2014.04.21D16:29:50.569000000 N   41.87 722
2014.04.21 GOOG 2014.04.21D16:29:58.633000000 O   41.9  437

Edit: Faster way would be to use functional exec with the 5th argument n(number of records) for each sym.

raze{

    //[table;where;by;cols;rows]
    ?[tab;enlist (in;`sym;enlist x);0b;();y]

}[;-2]'[distinct tab[`sym]]

https://code.kx.com/q/basics/funsql/

Upvotes: 2

Related Questions