如何在kdb/q中计算净额并为每行添加分类标签？
2026-6-22
KDB+ 分组净额计算与标签生成方案
问题背景
初始数据表结构如下：
| Name | Region | Code | Type | Neg2 | Neg1 | Day0 | Pos1 | Pos2 |
|---|---|---|---|---|---|---|---|---|
| Bob | AA | 1A | Buy | 100 | 1 | 1 | 80 | 30 |
| Bob | AA | 1A | Sell | -30 | -2 | -1 | -10 | -10 |
| Ann | ER | 5D | Sell | -60 | -3 | -1 | -4 | -10 |
| Art | AA | WA | Buy | 300 | 4 | 1 | 300 | 300 |
| Art | AA | WA | Sell | -500 | -5 | -1 | -100 | -100 |
对应KDB+定义：
a: ([] Name:`Bob`Bob`Ann`Art`Art; Region:`AA`AA`ER`AA`AA; Code: `1A`1A`5D`WA`WA; Type: `Buy`Sell`Sell`Buy`Sell; Neg2: 100 -30 -60 300 -500; Neg1: 1 -2 -3 4 -5; Day0: 1 -1 -1 1 -1; Pos1: 80 -10 -4 300 -100; Pos2: 30 -10 -10 300 -100)
已完成预处理：按Name, Region, Code, Type分组计算Day0前及Day0后合计金额：
a: update preday0: Neg2 + Neg1 by Name, Region, Code, Type from a; a: update postday0: Day0 + Pos1 + Pos2 by Name, Region, Code, Type from a;
处理后的数据表：
| Name | Region | Code | Type | Neg2 | Neg1 | Day0 | Pos1 | Pos2 | preday0 | postday0 |
|---|---|---|---|---|---|---|---|---|---|---|
| Bob | AA | 1A | Buy | 100 | 1 | 1 | 80 | 30 | 101 | 111 |
| Bob | AA | 1A | Sell | -30 | -2 | -1 | -10 | -10 | -32 | -21 |
| Ann | ER | 5D | Sell | -60 | -3 | -1 | -4 | -10 | -63 | -15 |
| Art | AA | WA | Buy | 300 | 4 | 1 | 300 | 300 | 304 | 601 |
| Art | AA | WA | Sell | -500 | -5 | -1 | -100 | -100 | -505 | -201 |
当前需求：按Name, Region, Code分组计算净额，为每行添加neg和post标签：
neg标签：分组内preday0总和为正标记buy，负标记sellpost标签：分组内postday0总和为正标记buy，负标记sell
期望最终结果：
| Name | Region | Code | Type | Neg2 | Neg1 | Day0 | Pos1 | Pos2 | preday0 | postday0 | neg | post |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Bob | AA | 1A | Buy | 100 | 1 | 1 | 80 | 30 | 101 | 111 | buy | buy |
| Bob | AA | 1A | Sell | -30 | -2 | -1 | -10 | -10 | -32 | -21 | buy | buy |
| Ann | ER | 5D | Sell | -60 | -3 | -1 | -4 | -10 | -63 | -15 | sell | sell |
| Art | AA | WA | Buy | 300 | 4 | 1 | 300 | 300 | 304 | 601 | sell | buy |
| Art | AA | WA | Sell | -500 | -5 | -1 | -100 | -100 | -505 | -201 | sell | buy |
分步实现方案
1. 计算分组净额并生成标签
按Name, Region, Code分组统计净额，根据正负生成标签：
// 计算分组净额并生成标签 group_tags: select neg_sum:sum preday0, post_sum:sum postday0 by Name, Region, Code from a; group_tags: update neg:?[neg_sum>0;`buy;`sell], post:?[post_sum>0;`buy;`sell] from group_tags;
2. 关联标签到原表
通过左连接将标签映射回原表的每一行：
// 关联标签到原表 a: a lj `Name`Region`Code xkey group_tags;
执行后即可得到目标结果表。
优雅的一站式解决方案
直接从初始表出发，一步完成所有计算与标签生成，避免中间步骤：
a: ([] Name:`Bob`Bob`Ann`Art`Art; Region:`AA`AA`ER`AA`AA; Code: `1A`1A`5D`WA`WA; Type: `Buy`Sell`Sell`Buy`Sell; Neg2: 100 -30 -60 300 -500; Neg1: 1 -2 -3 4 -5; Day0: 1 -1 -1 1 -1; Pos1: 80 -10 -4 300 -100; Pos2: 30 -10 -10 300 -100) | update preday0:Neg2+Neg1, postday0:Day0+Pos1+Pos2 by Name,Region,Code,Type | lj (update neg:?[sum preday0>0;`buy;`sell], post:?[sum postday0>0;`buy;`sell] by Name,Region,Code from x);
这种链式写法紧凑高效，一次性完成所有需求。
内容的提问来源于stack exchange，提问作者mmv456
相关产品推荐
相关产品推荐
