CODEWITHSUNDEEP
kdb+
marital_weeping
marital_weeping

Reputation: 876

select statement in KDB

Given ohlcv where each column has the same date: 2024.03.06

q)ohlcv
date       ticker  open   close  high   low    volume time                          entry
-----------------------------------------------------------------------------------------
2024.03.06 IBM     174.3  150.5  174.3  144.1  17110  2024.03.06D09:15:00.000000000 0    
2024.03.06 IBM     153.95 158.7  158.7  153.95 775    2024.03.06D09:16:00.000000000 0    
2024.03.06 IBM     160    157.05 160    157    8455   2024.03.06D09:17:00.000000000 0    
2024.03.06 IBM     157.85 157.1  157.85 155.8  14334  2024.03.06D09:18:00.000000000 0    
2024.03.06 IBM     157.05 157.75 157.8  156.2  11478  2024.03.06D09:19:00.000000000 0    
2024.03.06 IBM     157.5  158    158    157.15 12446  2024.03.06D09:20:00.000000000 0    
2024.03.06 IBM     158    160.2  161    157.6  29500  2024.03.06D09:21:00.000000000 0    
2024.03.06 IBM     160.55 161.25 161.25 159.7  33726  2024.03.06D09:22:00.000000000 1    
2024.03.06 IBM     162    164.4  164.7  161.55 44981  2024.03.06D09:23:00.000000000 0
...
2024.03.06 IBM     154.75 154.4  155    154.05 14003  2024.03.06D15:29:00.000000000 0 / last row

q)distinct ohlcv`date
,2024.03.06

q) count ohlcv
375

q)meta ohlcv
c              | t f a
---------------| -----
date           | d    
ticker         | s    
open           | f    
close          | f    
high           | f    
low            | f    
volume         | f      
time           | p    
entry          | b

why does the following select by date from ohlcv statement yield only one row, which, as it turns out is the last row? My understaning was that we should get all 375 rows since they all have the same date.

q)select by date from ohlcv
date      | ticker  open   close high low    volume time                          entry
----------| ---------------------------------------------------------------------------
2024.03.06  IBM     154.75 154.4 155  154.05 14003  2024.03.06D15:29:00.000000000 0    / last row

q) count ohlcv
1

Upvotes: 0

Views: 60

Answers (2)

cillianreilly
cillianreilly

Reputation: 2076

This is standard and documented behaviour: https://code.kx.com/q/ref/select/#by-phrase A By phrase with no Select phrase returns the last row in each group.

You can use xgroup to get the behaviour you're looking for:

q)`a xgroup ([]a:0 0 1 1 2;b:`a`a`c`d`e;c:til 5)
a| b    c
-| --------
0| `a`a 0 1
1| `c`d 2 3
2| ,`e  ,4

Upvotes: 3

Tim Roberts
Tim Roberts

Reputation: 54767

The kdb Q language is only slightly related to SQL. You need to do some more reading. select by is the same as SQL's group by, so it aggregates all the rows based on the grouped column.

https://code.kx.com/q/ref/select/#select-phrase

Upvotes: 3

Related Questions