问题描述
我有以下交易表:
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
这篇关于多组滚动窗口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！