kdb/q中如何关联两表计算销售统计字段？是否为内连接？

云上老周

2026-6-26

原始销售记录表（sales_data）

Name	Item	Action	Price	Num_Items
Ann	Apple	Buy	5.00	10
Ann	Apple	Sell	6.00	12
Ann	Bread	Sell	9.00	9
Bob	Apple	Buy	5.00	14
Bob	Salt	Buy	1.00	10
Bob	Salt	Buy	1.00	1
Bob	Salt	Sell	2.00	2

对应的KDB+定义代码：

sales_data: ([] Name: `Ann`Ann`Ann`Bob`Bob`Bob`Bob; Item: `Apple`Apple`Bread`Apple`Salt`Salt`Salt; Action: `Buy`Sell`Sell`Buy`Buy`Buy`Sell; Price: 5.00 6.00 9.00 5.00 1.00 1.00 2.00; Num_Items: 10 12 9 14 10 1 2)

筛选后的Name-Item对（distinct_data）

Name	Item
Ann	Apple
Bob	Salt

对应的KDB+定义代码：

distinct_data: ([] Name: `Ann`Bob; Item: `Apple`Salt)

目标表

Name	Item	Num_Buy	Num_Sold	Weighted_Avg_Price_Bought	Weighted_Avg_Price_Sold
Ann	Apple	10	12	5	6
Bob	Salt	11	2	1	2

关联实现方法

在KDB+中可以通过分组聚合+等值连接完成需求，具体步骤如下：

先对sales_data按Name和Item分组，计算所需的聚合字段：

agg_sales: select 
    Num_Buy: sum Num_Items where Action=`Buy,
    Num_Sold: sum Num_Items where Action=`Sell,
    Weighted_Avg_Price_Bought: sum[Price*Num_Items where Action=`Buy] % sum[Num_Items where Action=`Buy],
    Weighted_Avg_Price_Sold: sum[Price*Num_Items where Action=`Sell] % sum[Num_Items where Action=`Sell]
by Name, Item from sales_data

将distinct_data与聚合后的agg_sales做内连接，保留目标Name-Item对的聚合结果：

target_table: distinct_data lj `Name`Item xkey agg_sales

也可以合并为一步，直接在连接时完成分组聚合：

target_table: select 
    Num_Buy: sum Num_Items where Action=`Buy,
    Num_Sold: sum Num_Items where Action=`Sell,
    Weighted_Avg_Price_Bought: sum[Price*Num_Items where Action=`Buy] % sum[Num_Items where Action=`Buy],
    Weighted_Avg_Price_Sold: sum[Price*Num_Items where Action=`Sell] % sum[Num_Items where Action=`Sell]
from distinct_data, sales_data where distinct_data.Name=sales_data.Name, distinct_data.Item=sales_data.Item
group by distinct_data.Name, distinct_data.Item