如何计算单个商品营收及包含该商品的订单总营收？

需求说明 #

需要计算数据集中每个商品的总营收，以及包含该商品的所有订单的总营收。

样本数据 #

预期结果 #

现有代码与问题 #

你已经能通过以下SQL计算单个商品的总营收：

select sum(revenue) from dataset group by item

但在计算包含该商品的订单总营收时，子查询里的dataset where item=item写法存在错误。

错误原因 #

item=item中的两个item都属于子查询内部的dataset表字段，数据库会将其判定为同一个字段的自比较，结果永远为真。这会导致子查询返回所有订单的营收总和，而非仅包含当前商品的订单营收。

修正后的SQL写法 #

写法一：修正子查询的表关联 #

通过给外层表取别名，明确关联当前分组的商品：

SELECT 
    item,
    SUM(revenue) AS item_total,
    (SELECT SUM(revenue) 
     FROM dataset d2 
     WHERE d2.order_id IN (
         SELECT DISTINCT order_id 
         FROM dataset d3 
         WHERE d3.item = d1.item
     )) AS orders_with_item_total
FROM dataset d1
GROUP BY item;

写法二：预计算订单总营收（更高效） #

先计算每个订单的总营收，再通过关联得到商品对应的订单总营收：

WITH order_totals AS (
    SELECT order_id, SUM(revenue) AS order_total
    FROM dataset
    GROUP BY order_id
)
SELECT 
    d.item,
    SUM(d.revenue) AS item_total,
    SUM(ot.order_total) AS orders_with_item_total
FROM dataset d
JOIN order_totals ot ON d.order_id = ot.order_id
GROUP BY d.item;

两种写法都能得到符合预期的结果。

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