同结构双表聚合查询优化：实现跨表汇总与差值计算

我有两张结构完全相同的数据表，分别存储当日数据（Current表）和前日数据（Previous表），表结构和数据如下：

Current表 #

CREATE TABLE Current (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO Current (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12345, 10.50),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23456, 20.75),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34567, 30.80),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45678, 40.25),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56789, 50.60),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67890, 60.10),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78901, 70.95),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89012, 15.35);

Previous表 #

CREATE TABLE Previous (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO Previous (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12350, 10.55),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23461, 20.80),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34572, 30.85),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45683, 40.30),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56794, 50.65),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67905, 60.15),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78916, 70.90),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89027, 15.40);

我之前执行了以下查询：

SELECT Col1, SUM(Col5) AS 'sum', 'C' AS 'Flag'
FROM Current
GROUP BY Col1
UNION
SELECT Col1, SUM(Col5) AS 'sum', 'P' AS 'Flag'
FROM Previous
GROUP BY Col1
ORDER BY Col1;

得到结果：

Col1    Sum    Flag
ItemA   35801   C
ItemA   35811   P
ItemB   34567   C
ItemB   34572   P
ItemC   102477  P
ItemC   102467  C
ItemD   67905   P
ItemD   67890   C
ItemE   167913  C
ItemE   167943  P

现在需要修改查询，返回如下格式的结果：

Col1    Current Sum Previous Sum    Difference
ItemA   35801         35811           10
ItemB   34567         34572            5
ItemC   102467        102477          10
ItemD   67890         67905           15
ItemE   167913        167943          30

补充说明：两张表可能存在某张表有匹配数据而另一张没有的情况（比如数据过期仅存在于Previous表，或新增数据仅存在于Current表），此时缺失值需填充为0。

可以通过先分别聚合两张表，再用全外连接合并结果的方式实现需求，具体SQL如下：

WITH CurrentAgg AS (
    -- 聚合当日表的Col5求和
    SELECT Col1, SUM(Col5) AS CurrentSum
    FROM Current
    GROUP BY Col1
),
PreviousAgg AS (
    -- 聚合前日表的Col5求和
    SELECT Col1, SUM(Col5) AS PreviousSum
    FROM Previous
    GROUP BY Col1
)
SELECT 
    -- 取两边存在的Col1，确保不遗漏任何条目
    COALESCE(c.Col1, p.Col1) AS Col1,
    -- 缺失的当日求和值填0
    COALESCE(c.CurrentSum, 0) AS "Current Sum",
    -- 缺失的前日求和值填0
    COALESCE(p.PreviousSum, 0) AS "Previous Sum",
    -- 计算前日与当日的差值
    COALESCE(p.PreviousSum, 0) - COALESCE(c.CurrentSum, 0) AS Difference
FROM CurrentAgg c
-- 全外连接覆盖所有可能的条目存在情况
FULL OUTER JOIN PreviousAgg p ON c.Col1 = p.Col1
ORDER BY Col1;

逻辑说明 #

1. CTE聚合：先用两个公共表表达式（CTE）分别对Current和Previous表按Col1分组求和，得到各自的汇总结果，简化后续连接逻辑。
2. 全外连接：使用FULL OUTER JOIN确保不管是仅存在于当日表的新增数据，还是仅存在于前日表的过期数据，都能被包含在结果中。
3. 缺失值填充：用COALESCE函数将连接后出现的NULL值替换为0，满足补充说明的要求。
4. 差值计算：直接用前日的求和值减去当日的求和值，与示例中的差值结果一致。

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