KDB Converting Subselect to Q Query

Question

We have a Q query running on tick data which consolidates to OHLC on 1-minute bars.

select subsel:(
         exec last datetime.date+1 xbar datetime.minute.z.Z
           from `base
           where instrument=`GBPUSD,
             datetime=datetime.date+1 xbar datetime.minute.z.Z), 
       max(datetime),
       min(datetime),
       Open:first price,
       High:max price,
       Low:min price,
       Close:last price,
       Volume:count(i)
  by DT:($)datetime.date+1 xbar datetime.minute.z.Z
  from `base
  where instrument=`GBPUSD,
    datetime>=2017.07.03T10:20:00.00,
    datetime<2017.07.03T10:20:59.999

The problem is the xbar date is synthetic on both the main table and the 'subselect', the exec "datetime=" needs to reference the main table and cannot find the alias approach to use. Considered an ej but as both sides are synthetic also could not find the construct.

Alexander Belopolsky · Accepted Answer · 2017-07-11 16:16:20Z

There are several issues with your query before we even get to the subselect. First, datetime.minute.z.Z is invalid syntax. You probably don't need the .z.Z suffix there. Second, 1 xbar is redundant: 1 xbar x is x for integer x and datetime.minute is integer. You can just do datetime.date+datetime.minute to get datetimes rounded to minutes. (Note that if you use timestamps, as you should, rounding would simply be 0D00:01 xbar timestamp, for datetime, you would have to precompute a minute as U:reciprocal 24*60 and use that with xbar - U xbar timestamp.) Fourth, you cast xbar'd timestamps to strings in the by clause. If you really want them as strings - do it as a separate update after the aggregation. Finally, there are some minor issues such as redundant parentheses and ($) which in q should be spelled as string.

Now, back to the subselect. I think once you resolve the issues I highlighted above, you will find out that you don't need the subquery at all. The result will already have the xbar'd timestamps in the key column. If you want the result as a regular table - just use 0!.

Collectives™ on Stack Overflow

KDB Converting Subselect to Q Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related