Segments and query with chunks

[skuvvv]

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 CME
2015.05.15 06:35:54.960 @ACDM15 0.9624  1     00  0  818294 CME
2015.05.15 10:50:57.783 @ACDM15 0.9647  1     00  0  889144 CME
2015.05.15 10:50:57.783 @ACDM15 0.9646  1     00  0  889146 CME
2015.05.15 10:50:57.783 @ACDM15 0.9652  0     00  0  176945 CME
2015.05.15 09:30:01.005 A       42.14   75483 00  0  1349   NYSE
2015.05.15 09:30:03.461 A       42.25   200   00  0  1788   NYSE
2015.05.15 09:30:25.854 A       42.24   182   00  0  2565   NYSE
2015.05.15 09:30:29.285 A       42.25   100   00  0  2655   NYSE
2015.05.15 09:30:29.359 A       42.24   200   00  0  2657   NYSE
2015.05.15 14:07:01.846 AA-     88.2566 193   00  0  382014 NYSE_MKT
2015.05.15 14:07:01.847 AA-     88.95   193   00  0  382016 NYSE_MKT
2015.05.15 14:07:01.848 AA-     88.2566 252   00  0  382017 NYSE_MKT
2015.05.15 09:30:54.182 AAMC    183.28  231   00  0  5340   NYSE_MKT
2015.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?

[mkeenan1]

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

[mkeenan1]

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

[Joseph_Edwards]

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

[skuvvv]

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.