课程 4.4: 条件聚合

SQL 中的条件聚合允许您在一个查询中计算多个指标，而无需运行多个单独的查询。这个想法很简单：在聚合函数（SUM、COUNT、AVG）内部，您使用一个条件表达式（通常是 CASE，但在某些数据库管理系统中可以是其他条件运算符），该表达式仅包括符合条件的行进行计算。

这种方法在需要同时获取多个指标的报告、仪表板和分析中尤其有用：计数、总和、份额、状态细分等等。

在本课程中，我们将涵盖：

• 条件聚合的工作原理；
• 如何计算条件计数、总和和平均值；
• 如何使用 CASE 构建透视风格的报告（将行转换为列）。

核心思想

经典的条件聚合模板：

AGGREGATION_FUNCTION(CASE WHEN condition THEN value ELSE 0 END)

或简短版本：

AGGREGATION_FUNCTION(CASE WHEN condition THEN 1 END)

发生的事情：

• CASE 根据条件返回一个值。在简短版本中，如果条件不满足，它返回 NULL；
• 聚合函数按组累积结果；
• 输出是基于条件的指标。

条件总和

示例：按金额范围划分的员工销售总额

SELECT
    staff_id,
    SUM(CASE WHEN amount < 2 THEN amount ELSE 0 END) AS low_amount_total,
    SUM(CASE WHEN amount BETWEEN 2 AND 6 THEN amount ELSE 0 END) AS medium_amount_total,
    SUM(CASE WHEN amount > 6 THEN amount ELSE 0 END) AS high_amount_total
FROM payment
GROUP BY staff_id;

结果： 一个查询返回每个员工的三种不同总和。

条件平均值

示例：按员工计算大额支付的平均金额

SELECT
    staff_id,
    AVG(CASE WHEN amount >= 5 THEN amount END) AS avg_big_payment
FROM payment
GROUP BY staff_id;

结果： 对于每个员工，平均金额仅计算 amount >= 5 的支付。

为什么这里通常不需要 ELSE 0：

• AVG 是通过值的总和除以它们的计数来计算的；
• 如果您对不满足条件的行放置 0，这些零将被包括在内并降低平均值；
• 这就是为什么条件 AVG 通常使用 ELSE NULL 或完全省略 ELSE。

条件计数

示例：每个金额范围内的支付数量

SELECT
    customer_id,
    COUNT(CASE WHEN amount < 2 THEN 1 END) AS low_payments,
    COUNT(CASE WHEN amount BETWEEN 2 AND 6 THEN 1 END) AS medium_payments,
    COUNT(CASE WHEN amount > 6 THEN 1 END) AS high_payments
FROM payment
GROUP BY customer_id;

结果： 对于每个客户，查询返回低、中、高支付的数量。

为什么这里不需要 ELSE：

• 如果条件为真，CASE 返回 1；
• 如果条件为假且未指定 ELSE，CASE 返回 NULL；
• COUNT(expression) 仅计算非 NULL 值，因此仅包括条件为真的行。

重要提示：在此模式下不要对 COUNT 使用 ELSE 0，因为 0 也不是 NULL，然后 COUNT 开始几乎计算所有行。

示例：计算已归还和未归还的租赁

SELECT
    staff_id,
    COUNT(return_date) AS returned_count,
    COUNT(CASE WHEN return_date IS NULL THEN 1 END) AS not_returned_count
FROM rental
GROUP BY staff_id;

这里发生的事情：

• COUNT(return_date) 仅计算非 NULL 值，即已归还租赁的数量；
• COUNT(CASE WHEN return_date IS NULL THEN 1 END) 仅计算缺少归还日期的行，即未归还的租赁；
• GROUP BY staff_id 为每个员工构建单独的计数器。

结果：在一个查询中，您为每个员工获得两个指标。

使用 CASE 的透视技术

SQL 中的透视是什么

透视将行转换为列。通常，源数据在行中包含类别，但在报告中，您需要将这些类别作为单独的列。

许多数据库管理系统都有专用的 PIVOT 运算符，但通用和可移植的方法是使用 CASE 的条件聚合。

基本透视模板

SELECT
    group_column,
    SUM(CASE WHEN pivot_key = 'A' THEN measure ELSE 0 END) AS col_a,
    SUM(CASE WHEN pivot_key = 'B' THEN measure ELSE 0 END) AS col_b,
    SUM(CASE WHEN pivot_key = 'C' THEN measure ELSE 0 END) AS col_c
FROM source_table
GROUP BY group_column;

示例：按电影评级透视

以下是一个示例，对于每个电影类别，我们按评级在单独的列中计数电影：

SELECT
    c.name AS category,
    COUNT(CASE WHEN f.rating = 'G' THEN 1 END) AS g_films_count,
    AVG(CASE WHEN f.rating = 'G' THEN length ELSE 0 END) AS g_films_average_length,
    COUNT(CASE WHEN f.rating = 'PG' THEN 1 END) AS pg_films_count,
    AVG(CASE WHEN f.rating = 'PG' THEN length ELSE 0 END) AS pg_films_average_length,
    COUNT(CASE WHEN f.rating = 'PG-13' THEN 1 END) AS pg13_films_count,
    AVG(CASE WHEN f.rating = 'PG-13' THEN length ELSE 0 END) AS pg13_films_average_length,
    COUNT(CASE WHEN f.rating = 'R' THEN 1 END) AS r_films_count,
    AVG(CASE WHEN f.rating = 'R' THEN length ELSE 0 END) AS r_films_average_length,
    COUNT(CASE WHEN f.rating = 'NC-17' THEN 1 END) AS nc17_films_rating,
    AVG(CASE WHEN f.rating = 'NC-17' THEN length ELSE 0 END) AS nc17_films_average_length
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY c.name;

结果： 每一行是一个类别，列显示每个评级的电影数量及其平均时长。

实用建议

• 对于 SUM，通常使用 ELSE 0，以便条件外的行贡献为零。
• 对于 COUNT(CASE ...)，通常不需要 ELSE：COUNT 已经忽略 NULL。
• 对于 AVG(CASE ...)，更常用 ELSE NULL 或不使用 ELSE，以免降低平均值。
• 如果有许多条件指标，请使用清晰的别名（*_count、*_total）。
• 检查 CASE 条件在类别应互斥时是否不重叠。
• 对于大型查询，先在小数据集上或使用 LIMIT 验证逻辑。

实用用法

1. 按星期几透视：

    SELECT
        MONTH(rental_date) AS rental_month,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Monday' THEN 1 ELSE 0 END) AS monday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Tuesday' THEN 1 ELSE 0 END) AS tuesday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Wednesday' THEN 1 ELSE 0 END) AS wednesday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Thursday' THEN 1 ELSE 0 END) AS thursday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Friday' THEN 1 ELSE 0 END) AS friday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Saturday' THEN 1 ELSE 0 END) AS saturday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Sunday' THEN 1 ELSE 0 END) AS sunday_rentals
    FROM rental
    GROUP BY MONTH(rental_date);
   

   此查询显示每个月按星期几的租赁数量。

2. 条件份额计算：

   SELECT
       customer_id,
       SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS high_payment_share
   FROM payment
   GROUP BY customer_id;
   

关于 FILTER 语法的快速说明

在某些数据库管理系统中（例如 PostgreSQL），您可以将条件从 CASE 移动到 FILTER：

COUNT(*) FILTER (WHERE condition)
SUM(amount) FILTER (WHERE condition)

其含义与使用 CASE 的条件聚合相同：聚合函数处理的不是所有行，而是仅处理在 FILTER 内通过条件的行。

这种语法通常更易于阅读，尤其是在一个 SELECT 中需要计算多个不同条件的指标时。

例如：

SELECT
    customer_id,
    COUNT(*) AS total_payments,
    COUNT(*) FILTER (WHERE amount >= 5) AS big_payments_count,
    SUM(amount) FILTER (WHERE amount >= 5) AS big_payments_total
FROM payment
GROUP BY customer_id;

在这个例子中：

• COUNT(*) 计算所有客户支付；
• COUNT(*) FILTER (WHERE amount >= 5) 仅计算大额支付；
• SUM(amount) FILTER (WHERE amount >= 5) 仅对这些支付求和。

因此，FILTER 的作用与 CASE 相同，但形式更紧凑。同时，重要的是要记住，这种语法并不是所有数据库管理系统都支持。

本课的关键要点

• 条件聚合是一个聚合函数加上一个条件表达式，通常是 CASE。
• 使用 SUM(CASE ...)、COUNT(CASE ...) 和 AVG(CASE ...)，您可以在一个查询中获得多个指标。
• 使用 CASE 的透视是将行转换为列的通用方法。
• 这种方法非常适合分析报告和仪表板。

通过掌握条件聚合，您将能够为业务分析编写更紧凑和更具表现力的 SQL 查询。