理解条件聚合的需求
在数据库查询中，我们经常需要对数据进行汇总，但有时这种汇总需要基于特定的条件。例如，在一个员工和预订系统中，我们可能需要计算每个员工在“已结束”状态的预订中的总时长，而不是所有预订的总时长。传统的WHERE子句虽然可以过滤数据，但它会在聚合之前过滤，导致无法同时获取不同条件下的聚合结果。此时，条件聚合技术就显得尤为重要。
假设我们有以下两张表：
staff 表 (员工信息)
| StaffID | First_name | Last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Mary | Doe |
booking 表 (预订信息)
| BookingID | StaffID | Status | duration |
|---|---|---|---|
| 1 | 1 | cancelled | 20 |
| 2 | 1 | ended | 20 |
| 3 | 1 | ended | 10 |
| 4 | 2 | cancelled | 30 |
| 5 | 1 | confirmed | 40 |
我们的目标是：查询每个员工的“已结束”预订的总时长，同时可能还需要统计“已取消”预订的数量。
使用 CASE 表达式实现条件聚合
MySQL中的CASE表达式允许我们在SUM、COUNT、AVG等聚合函数内部进行条件判断。这是实现条件聚合最强大和灵活的方式。
核心原理
CASE表达式根据指定的条件返回不同的值。当它与聚合函数结合时，只有满足条件的值才会被纳入聚合计算。例如，在计算总和时，如果条件不满足，我们可以让CASE表达式返回0，这样就不会影响总和。
示例查询
以下SQL查询展示了如何实现上述需求：
SELECT
s.StaffID,
s.First_name,
s.Last_name,
SUM(CASE
WHEN b.Status = 'ended' THEN b.duration
ELSE 0
END) AS EndedBookingDuration,
COALESCE(SUM(b.Status = 'cancelled'), 0) AS CancelledBookingCount
FROM
staff s
LEFT JOIN
booking b ON s.StaffID = b.StaffID
GROUP BY
s.StaffID, s.First_name, s.Last_name
ORDER BY
s.StaffID;查询解析
- SELECT s.StaffID, s.First_name, s.Last_name: 选取员工的基本信息。
- FROM staff s LEFT JOIN booking b ON s.StaffID = b.StaffID: 使用LEFT JOIN将staff表与booking表连接起来。LEFT JOIN确保即使某个员工没有任何预订记录，他们仍然会出现在结果中（其聚合值将为0或NULL）。
-
SUM(CASE WHEN b.Status = 'ended' THEN b.duration ELSE 0 END) AS EndedBookingDuration:
- 这是实现条件聚合的关键部分。
- CASE WHEN b.Status = 'ended' THEN b.duration ELSE 0 END：对于booking表中的每一行，如果Status是'ended'，则返回该行的duration值；否则，返回0。
- SUM(...)：然后对CASE表达式返回的所有值进行求和。这样，只有“已结束”状态的duration会被累加，其他状态的duration则被忽略（因为它们返回0）。
-
COALESCE(SUM(b.Status = 'cancelled'), 0) AS CancelledBookingCount:
- 这是一个巧妙的条件计数方法。在MySQL中，布尔表达式（如b.Status = 'cancelled'）在数值上下文中会被隐式转换为1（如果为真）或0（如果为假）。
- SUM(b.Status = 'cancelled')：因此，这个SUM函数实际上是在计算Status为'cancelled'的记录数量。
- COALESCE(..., 0)：当LEFT JOIN的右侧（booking表）没有匹配记录时，SUM函数会返回NULL。COALESCE函数的作用是，如果SUM的结果是NULL，则将其替换为0，确保结果的健壮性。
- GROUP BY s.StaffID, s.First_name, s.Last_name: 按照员工ID和姓名进行分组，以便为每个员工计算独立的聚合值。
- ORDER BY s.StaffID: 对结果进行排序，提高可读性。
预期结果
运行上述查询，将得到以下结果：
| StaffID | First_name | Last_name | EndedBookingDuration | CancelledBookingCount |
|---|---|---|---|---|
| 1 | John | Doe | 30 | 1 |
| 2 | Mary | Doe | 0 | 1 |
-
John Doe (StaffID 1)：
- Ended bookings: (ID 2, duration 20) + (ID 3, duration 10) = 30
- Cancelled bookings: (ID 1) = 1
-
Mary Doe (StaffID 2)：
- Ended bookings: None = 0
- Cancelled bookings: (ID 4) = 1
注意事项与最佳实践
- LEFT JOIN 的使用: 当你需要包含所有左表（staff）的记录，即使它们在右表（booking）中没有匹配项时，LEFT JOIN是必要的。在这种情况下，聚合函数的结果可能会是NULL，所以使用COALESCE(SUM(...), 0)来处理NULL值非常重要。
-
CASE 表达式的灵活性: CASE表达式不仅可以用于SUM，还可以与COUNT、AVG、MAX、MIN等其他聚合函数结合，实现各种复杂的条件聚合逻辑。
- 例如，计算特定状态的平均值：AVG(CASE WHEN b.Status = 'ended' THEN b.duration ELSE NULL END)。注意这里ELSE NULL，因为AVG函数会自动忽略NULL值，而ELSE 0会把0也计入平均值。
- 性能考虑: 对于非常大的数据集，虽然CASE表达式功能强大，但频繁使用复杂的CASE逻辑可能会对查询性能产生一定影响。确保连接条件和WHERE子句（如果适用）都有合适的索引。
- 可读性: 尽管CASE表达式会使查询稍微复杂，但它比多次子查询或多次连接更简洁高效，并且更容易理解不同条件下的聚合逻辑。
总结
通过在SUM等聚合函数内部巧妙地运用CASE表达式，我们可以在MySQL中实现强大的条件聚合功能。这种技术使得从单个查询中获取多维度、基于特定条件的汇总数据成为可能，极大地提高了数据分析的效率和灵活性。理解并熟练运用CASE表达式是每个SQL开发者和数据分析师必备的技能之一。
