如何在单表中关联两个日期的产品营收数据进行对比查询

需要对比product_inventory表中两个不同日期的产品营收，目前已能分别通过两个SQL获取单日期的产品营收数据：

SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
FROM product_inventory 
WHERE date = '2021-11-17' 
GROUP BY date1 , product1, product_id_1 
ORDER BY rev1 DESC

SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
FROM product_inventory 
WHERE date = '2022-11-17' 
GROUP BY date2 , product2, product_id_2 
ORDER BY rev2 DESC

期望将两个日期的日期、营收字段合并到同一结果集，格式如下：

尝试过无关联的子查询连接SQL，但得到笛卡尔积（记录数呈平方增长）的错误结果：

SELECT A.date1, A.product1, A.rev1, B.date2, B.product2, B.rev2 FROM
(
SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 FROM product_inventory WHERE date = '2021-11-17' GROUP BY date1 , product1, product_id_1 ORDER BY rev1 DESC
) A,
(
SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 FROM product_inventory WHERE date = '2022-11-17' GROUP BY date2, product2, product_id_2 ORDER BY rev2 DESC
) B;

正确实现方法 #

方法1：通过产品ID关联子查询

核心是用product_id（唯一标识产品）关联两个日期的子查询，避免无关联导致的笛卡尔积：

SELECT 
    A.date1,
    A.product1,
    A.product_id_1,
    A.rev1,
    B.date2,
    B.rev2
FROM
    (SELECT 
        date AS date1,
        product AS product1,
        product_id AS product_id_1,
        SUM(revenue) AS rev1
     FROM product_inventory
     WHERE date = '2021-11-17'
     GROUP BY date1, product1, product_id_1) A
INNER JOIN
    (SELECT 
        date AS date2,
        product AS product2,
        product_id AS product_id_2,
        SUM(revenue) AS rev2
     FROM product_inventory
     WHERE date = '2022-11-17'
     GROUP BY date2, product2, product_id_2) B 
ON A.product_id_1 = B.product_id_2
ORDER BY A.rev1 DESC;

• 若要保留仅某一天有数据的产品，可将INNER JOIN替换为LEFT JOIN（保留2021-11-17的所有产品）或RIGHT JOIN（保留2022-11-17的所有产品）。

方法2：条件聚合（更高效）

无需拆分多个子查询，一次扫描表即可完成计算，性能更优：

SELECT 
    product AS product1,
    product_id AS product_id_1,
    MAX(CASE WHEN date = '2021-11-17' THEN date END) AS date1,
    SUM(CASE WHEN date = '2021-11-17' THEN revenue END) AS rev1,
    MAX(CASE WHEN date = '2022-11-17' THEN date END) AS date2,
    SUM(CASE WHEN date = '2022-11-17' THEN revenue END) AS rev2
FROM product_inventory
WHERE date IN ('2021-11-17', '2022-11-17')
GROUP BY product, product_id
ORDER BY rev1 DESC;

• 该写法自动按产品分组聚合两个日期的营收，不会出现笛卡尔积问题，同时减少表扫描次数。

内容的提问来源于stack exchange，提问作者Jorge Diaz Jr