多组滚动窗口 [英] Rolling window in multi groups

问题描述

我有以下交易表:

  time ticker side price qty
  --------------------------
  2018.01.01T13:00:20 AAPL BUY  10.0  100
  2018.01.01T13:01:30 AAPL SELL 12.0  300
  2018.01.01T13:01:45 AAPL BUY  11.0  500  
  2018.01.01T13:02:13 AAPL BUY  10.5  100
  2018.01.01T13:05:00 AAPL SELL 13.0  200

我需要具有1分钟回溯时间的滚动窗口功能，以区分股票的买/卖

I need a rolling window function with a lookback of 1 minute to seperate the buy/sells of a stock price

  time ticker BUYs SELLs TOTAL
  --------------------------------
  2018.01.01T13:00:20 AAPL 1 0 1
  2018.01.01T13:01:30 AAPL 0 1 1
  2018.01.01T13:01:45 AAPL 1 1 2
  2018.01.01T13:02:13 AAPL 1 1 2 
  2018.01.01T13:05:00 AAPL 0 1 1

我决定使用"wj"功能，因为滚动功能适合我的目的.但是我无法使它起作用:

I have decided on using the "wj" function, because the rolling function suit my purpose. However I can't get it to work:

  w: -00:01 00:00 +:/ select time from table
  wj[w;'ticker'time;table;(table;(count;ticker);(count;ticker))]

因此，至少我希望首先对每次购买/出售进行计数，然后再对它们进行分组.但是我什至无法在没有出现类型错误的情况下运行初始查询.

So at least I want the count every buy/sell first then group them later. But I cannot even get the initial query to run without getting a type error.

有人可以指出我正确的方向吗?

Can someone point me in the right direction?

其他问题

我知道必须对多个帐户执行滚动总和/计数，直到运行时才知道.

I know would have to perform a rolling sum/count over several accounts which is not known until runtime.

  time ticker side price qty account
  ----------------------------------
  2018.01.01T13:00:20 AAPL BUY  10.0  100 ACCT123
  2018.01.01T13:01:30 AAPL SELL 12.0  300 ACCT456 
  2018.01.01T13:01:45 AAPL BUY  11.0  500 ACCT789  
  2018.01.01T13:02:13 AAPL BUY  10.5  100 ERRORACCT123
  2018.01.01T13:05:00 AAPL SELL 13.0  200 TESTACCT123

我知道我可以将桌子转到:

I know I can pivot the table to:

  time ticker side price qty ACCT123 ACCT456 ACC789 ERRORACCT123 TESTACCT23
  ---------------------------------

但是我可以使用滚动功能在1分钟的回溯期内对尺寸进行求和吗?

but can I using the rolling function to sum the sizes in a 1 minute lookback period?

推荐答案

窗口w必须是一对列表:

The window w is required to be a pair of lists:

   w: -00:01 00:00 +\: exec time from t

您还需要使用wj1，因为您只想考虑进入窗口时或之后的行.

You'll also need to use wj1 as you only want to consider rows on or after entry to the window.

http://code.kx.com/q /ref/joins/#wj-wj1-window-join

    q)table,'exec side from wj1[w;`ticker`time;table;(table;({`BUY`SELL!count each (group x)`BUY`SELL};`side))]

一元lambda: {`BUY`SELL!count each (group x)`BUY`SELL}

The monadic lambda: {`BUY`SELL!count each (group x)`BUY`SELL}

使用组返回BUY和SELL值的索引，并确保所有键中都存在BUY和SELL.

Uses group to return the indices of BUY and SELL values and also ensures that BUY and SELL are present in all keys.

exec创建一个表:

exec creates a table:

     q)exec side from wj1[w;`ticker`time;table;(table;({{`BUY`SELL!count each x`BUY`SELL}group x};`side))]
    BUY SELL
    --------
     1   0
     0   1
     1   1
     2   1
     0   1

然后我们使用join each获得最终结果:

And then we use join each to get the final result:

    q)update TOTAL:BUY+SELL from table,'exec side from wj1[w;`ticker`time;table;(table;({`BUY`SELL!count each (group x)`BUY`SELL};`side))]
    time                          ticker side price qty BUY SELL TOTAL
    ------------------------------------------------------------------
    2018.01.01D13:00:20.000000000 AAPL   BUY  10    100 1   0    1
    2018.01.01D13:01:30.000000000 AAPL   SELL 12    300 0   1    1
    2018.01.01D13:01:45.000000000 AAPL   BUY  11    500 1   1    2
    2018.01.01D13:02:13.000000000 AAPL   BUY  10.5  100 2   1    3
    2018.01.01D13:05:00.000000000 AAPL   SELL 13    200 0   1    1

对于根据侧面求和的数量，更容易做到以下几点: 首先使用向量条件更新两个新列，然后使用wj1将它们求和.

For summing quantities depending on side it is easier to the following: First update two new columns using vector conditional and then sum these using wj1.

http://code.kx.com/q/ref/lists /#vector-conditional

    q)wj1[w;`ticker`time;table;(update BUYQUANTITY:?[`BUY=side;qty;0],SELLQUANTITY:?[`SELL=side;qty;0]from table;(sum;`BUYQUANTITY);(sum;`SELLQUANTITY))]
    time                          ticker side price qty BUYQUANTITY SELLQUANTITY
    ----------------------------------------------------------------------------
    2018.01.01D13:00:20.000000000 AAPL   BUY  10    100 100         0           
    2018.01.01D13:01:30.000000000 AAPL   SELL 12    300 0           300         
    2018.01.01D13:01:45.000000000 AAPL   BUY  11    500 500         300         
    2018.01.01D13:02:13.000000000 AAPL   BUY  10.5  100 600         300         
    2018.01.01D13:05:00.000000000 AAPL   SELL 13    200 0           200         

这篇关于多组滚动窗口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！