Segments and query with chunks

Community Support
1

Hi,
My storage has following format: 

segmented by Exchange,

partitioned by date.

q v3.5 32bit

Next I make simple query for reading chunk of table by date:

select from Trades where date=2015.05.15, i within(0,4)

result:

date Time Symbol Price Size Dir OI Id Ex--------------------------------------------------------------------2015.05.15 06:35:54.882 @ACDM15 0.9624 1 00 0 818289 CME2015.05.15 06:35:54.960 @ACDM15 0.9624 1 00 0 818294 CME2015.05.15 10:50:57.783 @ACDM15 0.9647 1 00 0 889144 CME2015.05.15 10:50:57.783 @ACDM15 0.9646 1 00 0 889146 CME2015.05.15 10:50:57.783 @ACDM15 0.9652 0 00 0 176945 CME2015.05.15 09:30:01.005 A 42.14 75483 00 0 1349 NYSE2015.05.15 09:30:03.461 A 42.25 200 00 0 1788 NYSE2015.05.15 09:30:25.854 A 42.24 182 00 0 2565 NYSE2015.05.15 09:30:29.285 A 42.25 100 00 0 2655 NYSE2015.05.15 09:30:29.359 A 42.24 200 00 0 2657 NYSE2015.05.15 14:07:01.846 AA- 88.2566 193 00 0 382014 NYSE_MKT2015.05.15 14:07:01.847 AA- 88.95 193 00 0 382016 NYSE_MKT2015.05.15 14:07:01.848 AA- 88.2566 252 00 0 382017 NYSE_MKT2015.05.15 09:30:54.182 AAMC 183.28 231 00 0 5340 NYSE_MKT2015.05.15 10:21:38.385 AAMC 178.31 100 00 0 98654 NYSE_MKT

Instead of 5 record in query I got 15 records, 5 records x 3 segments.

My question is, how can I make correct query to read table chunk-by-chunk?

2

Each has it’s own virt i column - i=0 is the first record for each.
How does that compare to this? select first date,first Time,first Price by sym from Trades where date=2015.05.15

3

This might be relevant: http://code.kx.com/q/ref/dotq/#qind-partitioned-index

4

Hi Vadim,

Given you have segmented your database by Exchange before partitioning by sym, as Michael mentioned beforehand the use of .Q.ind should work to index into your table.

.Q.ind works by accessing the absolute index of a partioned table, so it should work across your entire segmented Trades table. 

Something along the lines of this should work:

{[st;ed].Q.ind[Trades;st+til ed]}

where you can pass in the starting index and the number of records you want returning, for you example of the first 5 records, this would correspond to [0;5].

However with .Q.ind you must pass it the table as it is stored on-disk, i.e. Trades, and doesn’t allow for a the filtering by date beforehand.

Using your original select statement, you can order the resulting table by time and take the first 5 records to achieve your desired result:

5#`time xasc select from Trades where date=2015.05.15, i within (0 4)

For more information on .Q.ind see the link below:

http://code.kx.com/q/ref/dotq/#qind-partitioned-index

Also check out these wiki pages on databases/segmented databases:

http://code.kx.com/mediawiki/index.php?title=JB:KdbplusForMortals/kdbplus\_database

http://code.kx.com/wiki/JB:KdbplusForMortals/segments#1.4.2.3\_Trades\_Segmented\_by\_Exchange

Hope this helps!

Joseph

5

Thanks for replies.
I think that .Q.ind is not acceptable for me without using of dates. 
In addition, index computation is not a good approach (in terms of performance) for simple iteration.

I think that I need to somehow iterate the query using the segment name.

Even when query comes without Exchange(in my case) I know all the segments.