如何在关联父子表时按子表维度聚合并保留父表全局统计

需求说明 #

• 对子表数据按指定列分组并求和金额；
• 确保父表的全局统计值准确有效。

表结构 #

CREATE TABLE Invoice (
  Id        VARCHAR(3) PRIMARY KEY,
  Pax       INT NOT NULL
)

CREATE TABLE InvoiceLine (
  Id        VARCHAR(3) PRIMARY KEY,
  InvoiceId VARCHAR(3) NOT NULL,
  Activity  VARCHAR(80) NOT NULL,
  Amount    DECIMAL(19,4) NOT NULL,
  CONSTRAINT FK_InvoiceLine_InvoiceId FOREIGN KEY (InvoiceId) REFERENCES Invoice (Id)
)

测试数据 #

Invoice表 #

InvoiceLine表 #

对应的插入语句：

INSERT INTO Invoice VALUES ('001', 2);
INSERT INTO Invoice VALUES ('002', 2);
INSERT INTO Invoice VALUES ('003', 4);
INSERT INTO InvoiceLine VALUES ('001', '001', 'Ticketing',   40);
INSERT INTO InvoiceLine VALUES ('002', '001', 'Reservation', 10);
INSERT INTO InvoiceLine VALUES ('003', '002', 'Ticketing',   30);
INSERT INTO InvoiceLine VALUES ('004', '002', 'Ticketing',   30);
INSERT INTO InvoiceLine VALUES ('005', '002', 'Reservation',  8);
INSERT INTO InvoiceLine VALUES ('006', '002', 'Insurance',    6.6);
INSERT INTO InvoiceLine VALUES ('007', '003', 'Ticketing',  120);
INSERT INTO InvoiceLine VALUES ('008', '003', 'Ticketing',   40);

查询目标 #

通过单个查询获取：

1. Amount：按Activity分组的金额总和；
2. Pax：按Activity分组的乘客数；
3. GlobalPax：全局服务的乘客总数（合计为8）。

当前尝试的查询 #

SELECT
  i.Id,
  il.Activity,
  SUM(il.Amount) AS Amount,
  AVG(i.Pax) AS Pax,
  AVG(CAST(i.Pax AS float)) / COUNT(*) OVER (PARTITION BY i.Id) AS GlobalPax
FROM
  Invoice AS i
  JOIN InvoiceLine AS il ON i.Id = il.InvoiceId
GROUP BY
  i.Id,
  il.Activity

当前查询结果 #

该查询能得到正确数值，但按i.Id分组导致返回行数过多（实际场景有数百万行数据），且不需要Id列。

理想结果 #

解决方案 #

通过CTE提前计算发票的乘客分摊比例，再关联子表分组汇总，避免多余行：

WITH InvoicePaxSplit AS (
    SELECT
        i.Id,
        i.Pax,
        -- 计算当前发票下每条明细的乘客分摊值
        CAST(i.Pax AS FLOAT) / COUNT(il.Id) OVER (PARTITION BY i.Id) AS PaxPerLine
    FROM Invoice i
    JOIN InvoiceLine il ON i.Id = il.InvoiceId
)
SELECT
    il.Activity,
    SUM(il.Amount) AS Amount,
    SUM(ip.Pax) AS Pax,
    SUM(ip.PaxPerLine) AS GlobalPax
FROM InvoiceLine il
JOIN InvoicePaxSplit ip ON il.InvoiceId = ip.Id
GROUP BY il.Activity
ORDER BY il.Activity;

逻辑说明 #

1. CTE InvoicePaxSplit：提前计算每个发票对应的每一行明细应分摊的乘客数，和原查询的分摊逻辑一致，但将计算步骤前置。
2. 主查询：直接按Activity分组，汇总金额、关联的乘客总数，以及分摊后的全局乘客值总和，直接得到目标结果。

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