我有一个表:
q)data:([]dt:2017.01.05D19:45:00.238248239 2017.01.05D20:46:00.282382392 2017.01.05D21:47:00.232842342 2017.01.05D22:48:00.835838442 2017.01.05D20:49:00.282382392;sym:`AAPL`GOOG`AAPL`BBRY`GOOG;price:101.20 800.20 102.30 2.20 800.50;shares:500 100 500 900 100)
q)data
dt sym price shares
2017.01.05D19:45:00.238248239 AAPL 101.20 500
2017.01.05D20:46:00.282382392 GOOG 800.20 100
2017.01.05D21:47:00.232842342 AAPL 102.30 500
2017.01.05D22:48:00.835838442 BBRY 2.20 900
2017.01.05D20:49:00.282382392 GOOG 800.50 100
我需要创建一列,其中包含价格*股票的总和,以最新观察到每个单独的股票。
要使用上述数据演示,我们正在寻找:
data:
dt sym price shares index
2017.01.05D19:45:00.238248239 AAPL 101.20 500 50,600
2017.01.05D20:46:00.282382392 GOOG 800.20 100 130,620
2017.01.05D21:47:00.232842342 AAPL 102.30 500 131,170
2017.01.05D22:48:00.835838442 BBRY 2.20 900 133,150
2017.01.05D20:49:00.282382392 GOOG 800.50 100 133,180
要进一步澄清,在第1行中,仅包括1个符号,在第2行2、2符号,然后是2个符号,然后是2行,然后是3,然后在第5行中再次3。
。在另一个线程中回答:将公式应用于当前和以前的行(q/kdb(
乔纳森解决方案的轻微变化,使用向量条件:
q)delete dict from update index:?[all flip distinct[sym]in/: key'[dict]; {sum[x]*sum[y]%sum z} ./: flip each value each dict;0N] from update dict:@[;;:;][()!();sym;flip (price;shares;divisor)] from data
dt sym price shares divisor index
----------------------------------------------------------------
2018.02.05D22:47:22.175914000 AAPL 101.2 500 2
2018.02.05D22:21:10.175914000 GOOG 800.2 500 1
2018.02.05D22:58:00.175914000 AAPL 102.3 500 2
2018.02.05D22:19:27.175914000 BBRY 2.2 500 1 339262.5
给定以来的问题发生了很大变化,我以前的答案是一个更新的解决方案:
q)delete ind from update index:sum@'ind from (update ind:@[()!();sym;:;shares*price] from data) where i>=max(first;i)fby sym
dt sym price shares index
------------------------------------------------------
2017.01.05D19:45:00.238248239 AAPL 101.2 500
2017.01.05D20:46:00.282382392 GOOG 800.2 100
2017.01.05D21:47:00.232842342 AAPL 102.3 500
2017.01.05D22:48:00.835838442 BBRY 2.2 900 133150
2017.01.05D20:49:00.282382392 GOOG 800.5 100 133180
或没有其他初始条件,仅一旦所有诉讼都勾选了它:
q)delete ind from update index:sum@'ind from update ind:@[()!();sym;:;shares*price] from data
dt sym price shares index
------------------------------------------------------
2017.01.05D19:45:00.238248239 AAPL 101.2 500 50600
2017.01.05D20:46:00.282382392 GOOG 800.2 100 130620
2017.01.05D21:47:00.232842342 AAPL 102.3 500 131170
2017.01.05D22:48:00.835838442 BBRY 2.2 900 133150
2017.01.05D20:49:00.282382392 GOOG 800.5 100 133180
(请注意,这些只是我昨天发布的解决方案的微小修改,已更新了问题的要求(
q)delete ind from update index:sum@'ind from (update ind:@[()!();sym;:;shares*price%divisor] from data) where i>=max(first;i)fby sym
dt sym price shares divisor index
--------------------------------------------------------------
2017.01.05D19:45:00.238248239 AAPL 101.2 500 2
2017.01.05D20:45:00.282382392 GOOG 800.2 500 1
2017.01.05D21:45:00.232842342 AAPL 102.3 500 2
2017.01.05D22:45:00.835838442 BBRY 2.2 500 1 426775
对您获得的答案略有不同,这是在做总和(股票*价格%Divisor(,而不是单独求和。
一个更混乱且复杂的版本,与您似乎期望的答案相同:
q)delete ind from update index:sum'[ind[;;0]]*sum'[ind[;;1]]%sum'[ind[;;2]] from (update ind:@[()!();sym;:;shares,'price,'divisor] from data) where i>=max(first;i)fby sym
dt sym price shares divisor index
----------------------------------------------------------------
2017.01.05D19:45:00.238248239 AAPL 101.2 500 2
2017.01.05D20:45:00.282382392 GOOG 800.2 500 1
2017.01.05D21:45:00.232842342 AAPL 102.3 500 2
2017.01.05D22:45:00.835838442 BBRY 2.2 500 1 339262.5