如何在kdb表中实现列转置，按日期与算法聚合比例数据？

你之前用左连接实现时出现数据丢失，是因为左表仅包含存在特定时区数据的date-algo组合，那些没有该时区数据的组合会被过滤掉，导致结果不完整。以下是正确的实现方式，确保所有date-algo组合都被保留，缺失时区的比例补0：

步骤1：补全所有可能的组合 #

先生成date、algo、timeZone的所有笛卡尔积组合，再左连接原表，填充缺失的计数为0：

// 生成所有date-algo-timezone组合
allCombos: ([] date:distinct tab.date; algo:distinct tab.algo) cross ([] timeZone:distinct tab.timeZone)
// 左连接原表，补全缺失记录
tabFull: allCombos lj `date`algo`timeZone xkey tab
// 填充缺失的cnt为0
update cnt:0^cnt from `tabFull

步骤2：重新计算比例 #

基于补全后的表，重新计算每个date-algo分组的总数和比例：

update total:sum cnt by date,algo from `tabFull
update proportion:cnt%total from `tabFull

步骤3：透视转列 #

将不同timeZone对应的proportion转为列，并按需重命名：

// 透视生成结果表
pivoted: select date, algo, exec proportion by timeZone from tabFull
// 重命名时区对应的列
renameMap: `00:00:00`00:05:00`00:10:00!`proportionIn0s`proportionIn5m`proportionIn10m
pivoted: renameMap xcol pivoted

更简洁的实现方式 #

如果不需要显式补全缺失值（kdb会自动为缺失的组合填充0n，可以后续替换为0），也可以直接用分组透视：

// 按date和algo分组，提取每个分组内的timeZone和proportion并转列
pivoted: select date, algo, {exec proportion by timeZone from x} each (`date`algo xgroup tab).data
// 替换空值为0，并重命名列
update `proportionIn0s`proportionIn5m`proportionIn10m:0^each `00:00:00`00:05:00`00:10:00 from pivoted
pivoted: `date`algo`proportionIn0s`proportionIn5m`proportionIn10m xcol pivoted

这样就能得到你需要的结果，所有date-algo组合都会被保留，缺失时区的比例会显示为0。

内容的提问来源于stack exchange，提问作者Terry