SQL 中的 CASE 语句详解

如果您使用过数据库，您很可能注意到数据很少是完美的，往往需要经过处理才能提炼出有意义的洞见。

为满足这一需求，SQL 提供了一个强大的结构——CASE 语句，它允许您根据应用于现有列的条件来创建新列或转换数据。

在本文中，我们将解释什么是 CASE 语句、为什么它至关重要，并通过多个使用场景帮助您发挥其潜力。让我们开始吧！

什么是 CASE 语句？

SQL 中的 Case 语句是一种条件表达式，可将决策逻辑引入查询。它的工作方式类似于Python 中的 if-elif-else 语句，允许您评估多个条件，并根据这些条件返回特定结果。

其通用语法如下：

CASE
	WHEN boolean_condition1 THEN result1
	WHEN boolean_condition2 THEN result2
	ELSE result3
END技术支持

其工作原理如下：

• CASE 关键字标志着表达式的开始，END 标志着结束，将整个条件语句像框架一样包裹起来。

• 每个 WHEN 子句用于评估一个条件。若条件为真，则对应的 THEN 子句返回指定结果。

• 如果所有 WHEN 子句中的条件均不满足，ELSE 子句将提供一个默认结果。

为什么 CASE 语句很重要？

CASE WHEN 语句在 SQL 查询中非常有用，具有以下关键优势：

• 数据转换：可基于现有列的取值创建新列。
• 条件聚合：可基于条件为特定数据子集创建聚合值（如求和、计数、最大值）。
• 数据筛选：您可以根据特定标准包含或排除行。
• 查询简化与可读性：有助于将逻辑整合到单个查询中，减少多次查询、临时表或复杂连接的需要。
• 性能优化：通过降低 SQL 查询的复杂度，CASE 语句也有助于优化性能并尽量缩短执行时间，尤其是在避免额外计算和转换时。

简单 CASE 语句

本文将使用 cinema 数据库中的 film 表。假设我们希望基于 ROI（票房与预算之比）的取值创建一个新的分类型列：

SELECT 
	title,
	gross,
	budget,
	gross / budget AS ROI,
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END as ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;技术支持

给定票房和预算，我们计算了 ROI，这是一项常见的盈利能力指标，用于衡量一项投资所产生的收益。

除了 ROI 字段，我们还对其进行了分组以便更好地传达：

• 如果影片的 ROI 小于 1，则取值为“低 ROI”
• ROI 介于 1 到 2 之间的影片属于“中等 ROI”类别
• “高 ROI”用于标记 ROI 大于或等于 2 的影片。

我们还过滤掉了预算或票房为零或负值的影片。此外，仅保留了 2015 年上映的影片。

在 Group By 子句中使用 CASE

在第一个示例中，我们创建了一个新字段，可用于聚合数据并获得更多洞见。

例如，我们可以计算每个 ROI 组的平均票房和平均预算：

SELECT 
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END AS ROI_group,
avg(gross) as avg_gross,
avg(budget) as avg_budget
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015
GROUP BY
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END
ORDER BY avg(gross) DESC
;技术支持

为实现分组，我们只需将 CASE 语句放入 GROUP BY 子句中。为获得更多洞见，我们按平均票房的降序对行进行排序。这样我们就能发现，最高平均票房对应高 ROI，反之则对应低 ROI。

在 WHERE 子句中使用 CASE

CASE 语句的另一项好处是可以基于特定条件筛选行。例如，我们可以根据影片语言应用不同的 ROI 阈值。众所周知，英文影片的 ROI 一直较高，因此阈值应更高；而我们假设其他语言的 ROI 较低。

SELECT 
    title,
    gross,
    budget,
    language,
    gross / budget AS ROI
FROM cinema.films
WHERE 
    CASE 
        WHEN language = 'English' THEN gross / budget > 2 -- English films need ROI > 2
        WHEN language = 'French' THEN gross / budget > 1.5 -- French films need ROI > 1.5
        ELSE gross / budget > 1.3 -- Default for other languages
    END;技术支持

计算聚合

我们也可能希望统计 2015 年上映的高 ROI 与低 ROI 影片的数量：

SELECT 
    SUM(CASE WHEN gross / budget > 2 THEN 1 ELSE 0 END) AS high_roi_films,
    SUM(CASE WHEN gross / budget BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS medium_roi_films,
    SUM(CASE WHEN gross / budget < 1 THEN 1 ELSE 0 END) AS low_roi_films
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;技术支持

在 SUM 函数内部，我们相当于创建了一个新字段：当 ROI 达到该阈值时取值为 1，否则为 0。对这个二元变量求和即可得到满足该条件的影片数量。

从输出可以看到，大多数影片的 ROI 小于 1，而高利润影片相对较少。

嵌套 Case 语句

更高级的用法之一是嵌套的 case 语句，即在一个 case 中再嵌套一个 case。当您需要将数据划分为子组，并针对每个子组应用不同逻辑时，这非常有用。

与第一个示例类似，我们希望创建一个新的列，该列应受 ROI 值的影响。正如之前所说，与其他语言的影片相比，英语影片在全球范围内的 ROI 更高。

因此，更好的做法是同时考虑 ROI 和语言来将影片划分为 ROI 分组。

SELECT 
    title,
    language,
    gross / budget AS ROI,
    CASE
        WHEN language = 'English' THEN 
            CASE 
                WHEN gross / budget < 2 THEN 'low ROI'
                WHEN gross / budget BETWEEN 2 AND 3 THEN 'medium ROI'
                ELSE 'high ROI'
            END
        ELSE 
            CASE
                WHEN gross / budget < 1 THEN 'low ROI'
                WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
                ELSE 'high ROI'
            END
    END AS ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 and release_year = 2006;技术支持

在该查询中，外层 CASE 语句用于判断语言是否为英语。根据语言不同，内层 CASE 对 ROI 进行评估：英语影片的阈值更高，非英语影片的阈值更低。这种多层条件有助于更好地区分低、中、高 ROI 的影片。

结论

CASE 语句是从数据中提取有意义洞见不可或缺的 SQL 结构。本文将理论与实践示例交替呈现，帮助您深入理解这一强大的条件表达式。若您想进一步学习，请查看SQL 数据操作课程。该课程涵盖 CASE 语句以及子查询和窗口函数等其他关键 SQL 知识点。最后，您还可以查看SQL 基础学习路径，全面了解帮助您掌握 SQL 的课程。