Output top few records by date using kdb

Question

Suppose I have a table, with date, sym, and size as columns. Dates are in ascending order and sizes are in descending order for each date. How do I abridge the table, such that each date, only top few, say 10, records are kept? The simpler the command the better.

Thanks in advance!

score 4 · Answer 1 · edited Dec 03 '17 at 08:37

Given a table:

q)show tbl:`date`val!/:(.z.d+til 3) cross 100+til 25;
    date       val
    --------------
    2016.10.31 100
    2016.10.31 101
    2016.10.31 102
    2016.10.31 103
    2016.10.31 104
    ..

Select first N rows by date: (N=2 in below example)

q)select from tbl where i in{raze y sublist/:group x}[date;2]
    date       val
    --------------
    2016.10.31 100
    2016.10.31 101
    2016.11.01 100
    2016.11.01 101
    2016.11.02 100
    2016.11.02 101

Uses group function - which will return a dictionary containing indices where each unique value occurs. The function then retrieves the first N indices (sublist) for each unique value.

NB: i is the implicit row index in a kdb table.

score 2 · Answer 2 · edited Dec 04 '17 at 04:10

2

'fby' is also a good option and is generally used in such cases where operation/aggregation on group is required.

http://code.kx.com/q/ref/qsql/#fby

In your example, since table already has 'size' col in decending order for each date so fetching top 10 rows will give the desired result.

q) select from tbl where ({x in 10#x};i) fby date

edited Dec 04 '17 at 04:10

Thomas Smyth - Treliant

4,993
6
25
36

answered Nov 01 '16 at 23:16

Rahul

3,914
1
14
25

score 0 · Answer 3 · answered Jun 21 '18 at 19:58

Though fby is the best solution, there is another way to get the same results :

ungroup select sublist[3] size by date from tbl

However, with multiple columns this won't be a cleaner approach :

ungroup select sublist[3] size ,sublist[3] sym ,sublist[3] price  by date from tbl

Output top few records by date using kdb

3 Answers3