如何统计购买过travel产品的客户各product的revenue总和

可以通过筛选目标客户+条件聚合的方式实现需求，以下是两种可行的SQL写法：

方法一：子查询筛选客户 #

SELECT
    customer_id,
    SUM(CASE WHEN product = 'books' THEN revenue ELSE 0 END) AS revenue_books,
    SUM(CASE WHEN product = 'music' THEN revenue ELSE 0 END) AS revenue_music,
    SUM(CASE WHEN product = 'food' THEN revenue ELSE 0 END) AS revenue_food
FROM orders
WHERE customer_id IN (
    SELECT DISTINCT customer_id FROM orders WHERE product = 'travel'
)
GROUP BY customer_id
ORDER BY customer_id;

方法二：CTE（公共表达式）筛选客户 #

WITH travel_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE product = 'travel'
)
SELECT
    o.customer_id,
    SUM(CASE WHEN o.product = 'books' THEN o.revenue ELSE 0 END) AS revenue_books,
    SUM(CASE WHEN o.product = 'music' THEN o.revenue ELSE 0 END) AS revenue_music,
    SUM(CASE WHEN o.product = 'food' THEN o.revenue ELSE 0 END) AS revenue_food
FROM orders o
JOIN travel_customers tc ON o.customer_id = tc.customer_id
GROUP BY o.customer_id
ORDER BY o.customer_id;

逻辑说明 #

1. 筛选目标客户：通过子查询或CTE先找出所有购买过travel产品的客户ID；
2. 条件聚合统计：使用CASE WHEN配合SUM函数，分别统计每个客户在books、music、food三类产品上的收入总和，无对应订单的类别自动填充0；
3. 分组排序：按customer_id分组，并排序输出，与期望结果一致。

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