'As-of' behaviour in q-sql?

Community Support
1

   Is there a way to retrieve a quote valid ‘as-of’ a time point using q-sql? Here is my problem: we are thinking of storing market quotes in splayed partitioned tables (one table per instrument). Given an in-memory table of trades, what would be the best way to retrieve the quote information (bid/offer) from the disk? The aj join works fine (aj[datetime;select from trades;select date, time, bid, offer from mdata_instrument]) if quotes for all instruments are stored in the same table but we will have one table per instrument. If we create an API  utility function retrieving the right quote for a given instrument, date and time, what is the best way of replicating the as-of behaviour of the aj join in this function? This function would determine which table to query based on the instrument passed to it but I am not sure how to retrieve the ‘most recent’ quote.

   Thanks in advance  

2

Hi,

asof will still allow you to do what you’re trying to do:

http://code.kx.com/q/ref/joins/#asof

for example:


qt asof symtime!(`IBM;2018.03.22D09:22:46.028495000)

bid | 105.16

ask | 106.06

bsize| 69

asize| 86

mode | “L”

ex | “N”

This has found the most recent IBM quote in my qt table as of the time I gave it.

However, you should still be able to use aj in this manner, by providing the right-hand select statement (i.e. the select on the on-disk data) with the exact, instrument-specific quote table:

    aj[symtime;trades;select from ibmquotetable]

Hope this helps,

James

3

  James, thank you, the asof operator is exactly what I was looking for.