基于唯一条目创建滚动计算（Q / KDB +）(Create rolling calculation based unique entries (Q/KDB+))

我有一张桌子：

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个符号，然后是3，然后在第5行再次包含3个符号。

在另一个主题中回答：仅将公式应用于当前行和前一行（Q / KDB）

I have a table:

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

I need to create a column containing the sum of price*shares for the latest observation of each individual ticker.

To demonstrate using the above data, we're looking for:

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

To further clarify, at row 1, only 1 symbol is included, at row 2, 2 symbols, then 2 again, then 3, then 3 again at row 5.

Answered in a different thread: Apply formula to current and previous rows only (Q/KDB)

最满意答案

鉴于问题自最初发布和我之前的回答以来发生了很大变化，这里有一个更新的解决方案：

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

（注意这些只是我昨天发布的解决方案的微小修改，针对问题中更改的要求进行了更新）

Given the question has changed considerably since the initial posting and my previous answer, here is an updated solution:

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

Or without the other initial condition that it should only be populated once all tickers have ticked:

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

(Note these are only minor modifications to the solution I posted yesterday, updated for the changed requirements in the question)

更多推荐