kdb - KDB+ 中具有多个值列的数据透视表

Question

我想改造以下两行表格生成的:

tb: ([] time: 2010.01.01 2010.01.01; side:`Buy`Sell; price:100 101; size:30 50)

time        side    price   size
--------------------------------
2010.01.01  Buy     100     30
2010.01.01  Sell    101     50

到下表单行:

tb1: ([] enlist time: 2010.01.01; enlist price_buy:100; enlist price_sell:101; enlist size_buy:30; enlist size_sell:50)

time        price_buy price_sell size_buy size_sell
-----------------------------------------------------
2010.01.01  100       101        30       50

实现这一目标的最有效方法是什么？

Answer 1

(select price_buy:price, size_buy:size by time from tb where side = `Buy) lj select price_sell:price, size_sell:size by time from tb where side = `Sell

time      | price_buy size_buy price_sell size_sell
----------| ---------------------------------------
2010.01.01| 100       30       101        50

如果您想避免 2 个 select 语句:

raze each select `price_buy`price_sell!(side!price)@/:`Buy`Sell, `size_buy`size_sell!(side!size)@/:`Buy`Sell by time from tb

作为附加说明，将日期列标记为时间可能会产生误导。 kdb 中的典型财务表格式为 date time sym etc

编辑:动态列生成的函数形式:

{x[0] lj x[1]}[{?[`tb;enlist (=;`side;enlist `$x);(enlist `time)!enlist `time;(`$("price",x;"size",x))!(`price;`size)]} each ("Sell";"Buy")]
time      | priceSell sizeSell priceBuy sizeBuy
----------| -----------------------------------
2010.01.01| 101       50       100      30