如何合并两组SQL销售统计查询并为结果添加唯一标识？

嗨，这里有几个实用的方案帮你合并两个查询、添加分组标识，还能直接算出百分比差异：

方法1：用UNION ALL合并查询（直观易读） #

这种方式把你原来的两个查询直接拼接，同时给每组加一个明确的标识列，一目了然：

SELECT 
    '千禧一代' AS Customer_Group,
    SUM(f.Quantity * f.Unit_Price) AS Total_Sales
FROM Date_Dimension d, Order_Fact f, Item_Dimension i, Customer_Dimension c 
WHERE i.Item_key = f.Item_key 
  AND c.DOB BETWEEN '1984-01-01' and '2000-12-25'

UNION ALL

SELECT 
    '非千禧一代' AS Customer_Group,
    SUM(f.Quantity * f.Unit_Price) AS Total_Sales
FROM Date_Dimension d, Order_Fact f, Item_Dimension i, Customer_Dimension c 
WHERE i.Item_key = f.Item_key 
  AND c.DOB NOT BETWEEN '1984-01-01' and '2000-12-25'

小提示：你的原查询里关联了Date_Dimension但没用到它的过滤条件，要是不需要的话可以去掉，能稍微提升点查询性能。

方法2：条件聚合（更高效，仅扫描一次表） #

如果你的数据集比较大，上面的方法会扫描两次表，效率偏低。用CASE WHEN做条件聚合，一次查询就能搞定所有结果：

SELECT
    CASE 
        WHEN c.DOB BETWEEN '1984-01-01' and '2000-12-25' THEN '千禧一代'
        ELSE '非千禧一代'
    END AS Customer_Group,
    SUM(f.Quantity * f.Unit_Price) AS Total_Sales
FROM Date_Dimension d, Order_Fact f, Item_Dimension i, Customer_Dimension c 
WHERE i.Item_key = f.Item_key
GROUP BY Customer_Group

扩展：直接计算百分比差异 #

如果需要直接输出两组的销售额百分比差异（比如以非千禧一代为基准），可以用CTE先算出两组的销售额，再计算差异：

WITH SalesSummary AS (
    SELECT
        CASE 
            WHEN c.DOB BETWEEN '1984-01-01' and '2000-12-25' THEN '千禧一代'
            ELSE '非千禧一代'
        END AS Customer_Group,
        SUM(f.Quantity * f.Unit_Price) AS Total_Sales
    FROM Date_Dimension d, Order_Fact f, Item_Dimension i, Customer_Dimension c 
    WHERE i.Item_key = f.Item_key
    GROUP BY Customer_Group
)
SELECT
    *,
    -- 计算相对于非千禧一代的百分比差异
    ROUND(
        (Total_Sales - (SELECT Total_Sales FROM SalesSummary WHERE Customer_Group = '非千禧一代')) 
        / (SELECT Total_Sales FROM SalesSummary WHERE Customer_Group = '非千禧一代') * 100,
        2
    ) AS Percentage_Difference
FROM SalesSummary

要是你想以千禧一代为基准计算差异，只需要把子查询里的分组标识换成千禧一代就行。

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