「SQL 数据分析系列」11. 条件逻辑

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第十一章条件逻辑

在某些情况下，你可能希望 SQL 逻辑分支的方向取决于特定列或者表达式的值。本章重点介绍如何编写这样的语句：该语句在执行期间能够根据遇到的不同数据采取不同的执行方式。SQL 语句中用于条件逻辑的机制是 case 表达式，它可以用于 select、insert、update 和 delete 语句。

什么是条件逻辑

简而言之，条件语句是在程序执行过程中从多个路径中选择其一的能力。例如，在查询客户信息时，你可能希望包括 customer.active 列，其值为 1 表示活跃，为 0 表示不活跃。如果要将查询结果用于生成报表，则可能需要转换该值以提高可读性。虽然每个数据库都包含用于处理这种情况的内置函数，但并没有统一的标准，所以你需要记住哪个数据库使用的是哪些函数。幸运的是，每个数据库的 SQL 实现都支持 case 表达式，可以用于各种常见情况：

mysql> SELECT first_name, last_name,

 -> CASE

 -> WHEN active = 1 THEN 'ACTIVE'

 -> ELSE 'INACTIVE'

 -> END activity_type

 -> FROM customer;

+-------------+--------------+---------------+

| first_name | last_name | activity_type |

+-------------+--------------+---------------+

| MARY | SMITH | ACTIVE |

| PATRICIA | JOHNSON | ACTIVE |

| LINDA | WILLIAMS | ACTIVE |

| BARBARA | JONES | ACTIVE |

| ELIZABETH | BROWN | ACTIVE |

| JENNIFER | DAVIS | ACTIVE |

...

| KENT | ARSENAULT | ACTIVE |

| TERRANCE | ROUSH | INACTIVE |

| RENE | MCALISTER | ACTIVE |

| EDUARDO | HIATT | ACTIVE |

| TERRENCE | GUNDERSON | ACTIVE |

| ENRIQUE | FORSYTHE | ACTIVE |

| FREDDIE | DUGGAN | ACTIVE |

| WADE | DELVALLE | ACTIVE |

| AUSTIN | CINTRON | ACTIVE |

+-------------+--------------+---------------+

599 rows in set (0.00 sec)

此查询包含一个 case 表达式，用于为 activity_type 列生成一个值——它根据 customer.active 列的值返回字符串“ACTIVE”或“INACTIVE”。

case 表达式

所有主流数据库服务器都包含内置函数，这些函数旨在模拟大多数编程语言中的 if-then-else 语句（示例包括 Oracle 的 decode()函数、MySQL 的 if()函数和 SQL Server 的 coalesce()函数）。case 表达式的设计也便于实现 if-then-else 逻辑，它与内置函数相比有两个优点：

• case 表达式是 SQL 标准（SQL92 发行版）的一部分，并已在 Oracle Database、SQL Server、MySQL、PostgreSQL、IBM UDB 和其他数据库服务器中实现；

• case 表达式内置于 SQL 语法，可以用于 select、insert、update 和 delete 语句。

下面两小节介绍两种不同类型的 case 表达式，并会演示一些 case 表达式的实例来进行说明。

查找型 case 表达式

本章前面演示的 case 表达式就是一种查找型 case 表达式的示例，其语法如下：

CASE

WHEN C1 THEN E1

WHEN C2 THEN E2

 ... 

WHEN CN THEN EN

[ELSE ED]

END

在如上定义中，符号 C1、C2、…、CN 表示条件（condition），符号 E1、E2、…、EN 表示由 case 表达式返回的表达式（expression）。如果 when 子句中条件的计算结果为 true，则 case 表达式返回相应的表达式。此外，ED 符号表示默认表达式，如果条件 C1、C2、…、CN 的计算结果都是 false，case 表达式将返回该默认表达式（else 子句是可选的，这就是将其括在方括号中的原因）。所有 when 子句返回表达式的计算结果必须是同一类型（例如，date、number、varchar 等）。

下面是一个查找型 case 表达式的示例：

CASE 

WHEN category.name IN ('Children','Family','Sports','Animation') 

THEN 'All Ages' 

WHEN category.name = 'Horror' 

THEN 'Adult' 

WHEN category.name IN ('Music','Games') 

THEN 'Teens' 

ELSE 'Other'

END

此 case 表达式返回一个字符串，可用于根据电影的类别对其进行分类。执行 case 表达式时，when 子句将按从上到下的顺序进行执行，只要有一个的条件计算为 true，就返回它相应的表达式，并忽略其余的 when 子句。如果 when 子句条件的计算结果没有一个是 true，则返回 else 子句中的表达式。

尽管前面的示例返回的是字符串表达式，但其实 case 表达式可以返回任何类型的表达式，甚至还包括子查询。本章前面有一个针对活跃客户使用子查询返回租赁数量的例子，下面是这个例子的另一个版本：

mysql> SELECT c.first_name, c.last_name, 

-> CASE 

-> WHEN active = 0 THEN 0 

-> ELSE 

-> (SELECT count(*) FROM rental r 

-> WHERE r.customer_id = c.customer_id) 

-> END num_rentals 

-> FROM customer c;

+-------------+--------------+-------------+

| first_name | last_name | num_rentals |

+-------------+--------------+-------------+

| MARY | SMITH | 32 |

| PATRICIA | JOHNSON | 27 |

| LINDA | WILLIAMS | 26 |

| BARBARA | JONES | 22 |

| ELIZABETH | BROWN | 38 |

| JENNIFER | DAVIS | 28 |

...

| TERRANCE | ROUSH | 0 |

| RENE | MCALISTER | 26 |

| EDUARDO | HIATT | 27 |

| TERRENCE | GUNDERSON | 30 |

| ENRIQUE | FORSYTHE | 28 |

| FREDDIE | DUGGAN | 25 |

| WADE | DELVALLE | 22 |

| AUSTIN | CINTRON | 19 |

+-------------+--------------+-------------+

599 rows in set (0.01 sec)

该版本的查询使用一个关联子查询检索每个活跃客户的租赁数量。根据活跃客户的占比可以知道，使用这种方法可能比连接 customer 和 rental 表并在 customer_id 列上进行分组更有效。

简单 case 表达式

简单 case 表达式与查找型 case 表达式非常相似，但灵活性稍差。其语法如下：

CASE V0 

WHEN V1 THEN E1 

WHEN V2 THEN E2 

... 

WHEN VN THEN EN 

[ELSE ED]

END

在前面的定义中，V0 表示一个值，符号 V1、V2、…、VN 表示要与 V0 进行比较的值。符号 E1、E2、…、EN 表示 case 表达式要返回的表达式，ED 表示在 V1、V2、…、VN 中的值都不匹配 V0 值 case 表达式的情况下要返回的默认表达式。

下面是一个简单 case 表达式的示例：

CASE category.name 

WHEN 'Children' THEN 'All Ages' 

WHEN 'Family' THEN 'All Ages' 

WHEN 'Sports' THEN 'All Ages' 

WHEN 'Animation' THEN 'All Ages'

WHEN 'Horror' THEN 'Adult' 

WHEN 'Music' THEN 'Teens' 

WHEN 'Games' THEN 'Teens' 

ELSE 'Other'

END

简单 case 表达式不如查找型 case 表达式灵活，因为你不能指定自己的条件，而查找型 case 表达式可以包括范围条件、不等条件和基于 and/or/not 操作符构造的复合条件，所以我建议对除了最简单逻辑以外的所有逻辑都使用查找型 case 表达式。

case 表达式范例

以下各节提供了一些示例，用以说明 SQL 语句中条件逻辑的实用性。

结果集变换

你可能遇到过这样的情况：你在对有限值集（例如一周中的几天）执行聚合操作时，希望结果集只有一行而每个值对应一列，而非每个值一行。例如，如果要编写一个查询，显示 2005 年 5 月、6 月和 7 月的电影租赁数量：

mysql> SELECT monthname(rental_date) rental_month, 

-> count(*) num_rentals 

-> FROM rental 

-> WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01' 

-> GROUP BY monthname(rental_date);

+--------------+-------------+

| rental_month | num_rentals |

+--------------+-------------+

| May | 1156 |

| June | 2311 |

| July | 6709 |

+--------------+-------------+

3 rows in set (0.01 sec)

但如果要求返回一个单行三列的数据（每列表示一个月）又要怎么办呢？要将此结果集转换为一行，则需要创建三列，并且在每列中仅对与所求月份相关的行求和：

mysql> SELECT 

-> SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1 

-> ELSE 0 END) May_rentals, 

-> SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1 

-> ELSE 0 END) June_rentals, 

-> SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1 

-> ELSE 0 END) July_rentals -> FROM rental 

-> WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01'; 

+-------------+--------------+--------------+

| May_rentals | June_rentals | July_rentals |

+-------------+--------------+--------------+

| 1156 | 2311 | 6709 |

+-------------+--------------+--------------+

1 row in set (0.01 sec)

从上面的查询可以看到，除了月份值，其三列中的每一列表达式都相同。当 monthname()函数返回该列所对应的月份时，case 表达式返回 1，否则返回 0。当对所有行求和时，每列返回当月对应的租赁数。显然，这样的转换只适用于小数目的值，如果统计要从 1905 年开始，那么这个变换就相当枯燥乏味了。

注意：尽管有些超出了本书范围，但是我还是要指出，SQL Server 和 Oracle Database 都包含专用于这种类型的 pivot 子句。

存在性检查

有时，你需要确定两个实体之间是否存在关系，而不关心数量的多少。例如，你可能想知道一个演员是否至少出演过一部 G 级电影，而不考虑他/她参演 G 级电影的具体数量。下面的查询使用多个 case 表达式生成三个输出列，一列用于显示演员是否有出现在 G 级影片中，另一列用于 PG 级影片，第三列用于 NC-17 级影片：

mysql> SELECT a.first_name, a.last_name, 

-> CASE 

-> WHEN EXISTS (SELECT 1 FROM film_actor fa 

-> INNER JOIN film f ON fa.film_id = f.film_id 

-> WHERE fa.actor_id = a.actor_id 

-> AND f.rating = 'G') THEN 'Y' 

-> ELSE 'N' -> END g_actor, 

-> CASE -> WHEN EXISTS (SELECT 1 FROM film_actor fa 

-> INNER JOIN film f ON fa.film_id = f.film_id 

-> WHERE fa.actor_id = a.actor_id 

-> AND f.rating = 'PG') THEN 'Y' 

-> ELSE 'N' -> END pg_actor, 

-> CASE 

-> WHEN EXISTS (SELECT 1 FROM film_actor fa 

-> INNER JOIN film f ON fa.film_id = f.film_id 

-> WHERE fa.actor_id = a.actor_id 

-> AND f.rating = 'NC-17') THEN 'Y'  

-> ELSE 'N' -> END nc17_actor 

-> FROM actor a 

-> WHERE a.last_name LIKE 'S%' OR a.first_name LIKE 'S%';

+------------+-------------+---------+----------+------------+

| first_name | last_name | g_actor | pg_actor | nc17_actor |

+------------+-------------+---------+----------+------------+

| JOE | SWANK | Y | Y | Y |

| SANDRA | KILMER | Y | Y | Y |

| CAMERON | STREEP | Y | Y | Y |

| SANDRA | PECK | Y | Y | Y |

| SISSY | SOBIESKI | Y | Y | N |

| NICK | STALLONE | Y | Y | Y |

| SEAN | WILLIAMS | Y | Y | Y |

| GROUCHO | SINATRA | Y | Y | Y |

| SCARLETT | DAMON | Y | Y | Y |

| SPENCER | PECK | Y | Y | Y |

| SEAN | GUINESS | Y | Y | Y |

| SPENCER | DEPP | Y | Y | Y |

| SUSAN | DAVIS | Y | Y | Y |

| SIDNEY | CROWE | Y | Y | Y |

| SYLVESTER | DERN | Y | Y | Y |

| SUSAN | DAVIS | Y | Y | Y |

| DAN | STREEP | Y | Y | Y |

| SALMA | NOLTE | Y | N | Y |

| SCARLETT | BENING | Y | Y | Y |

| JEFF | SILVERSTONE | Y | Y | Y |

| JOHN | SUVARI | Y | Y | Y |

| JAYNE | SILVERSTONE | Y | Y | Y |

+------------+-------------+---------+----------+------------+

22 rows in set (0.00 sec)

每个 case 表达式都包含一个针对 film_actor 和 film 表的关联子查询，一个用于查找 G 级影片，第二个用于查找 PG 级影片，第三个用于查找 NC-17 级影片。由于每个 when 子句都使用 exists 操作符，所以只要演员参演过至少一部对应级别的电影，条件即为真。

在其他情况下，你可能会关心涉及了多少行，但也仅限于一定程度上。例如，下一个查询使用一个简单的 case 表达式来计算每部电影的库存拷贝数，然后返回'Out Of Stock'，'Scarce'，'Available'或'Common'：

mysql> SELECT f.title,  

-> CASE (SELECT count(*) FROM inventory i   

-> WHERE i.film_id = f.film_id)  

-> WHEN 0 THEN 'Out Of Stock'  

-> WHEN 1 THEN 'Scarce'  

-> WHEN 2 THEN 'Scarce'  

-> WHEN 3 THEN 'Available'  

-> WHEN 4 THEN 'Available'  

-> ELSE 'Common'  

-> END film_availability  

-> FROM film f  -> ;

+-----------------------------+-------------------+

| title | film_availability |

+-----------------------------+-------------------+

| ACADEMY DINOSAUR | Common |

| ACE GOLDFINGER | Available |

| ADAPTATION HOLES | Available |

| AFFAIR PREJUDICE | Common |

| AFRICAN EGG | Available |

| AGENT TRUMAN | Common |

| AIRPLANE SIERRA | Common |

| AIRPORT POLLOCK | Available |

| ALABAMA DEVIL | Common |

| ALADDIN CALENDAR | Common |

| ALAMO VIDEOTAPE | Common |

| ALASKA PHANTOM | Common |

| ALI FOREVER | Available |

| ALICE FANTASIA | Out Of Stock |

... | YOUNG LANGUAGE | Scarce |

| YOUTH KICK | Scarce |

| ZHIVAGO CORE | Scarce |

| ZOOLANDER FICTION | Common |

| ZORRO ARK | Common |

+-----------------------------+-------------------+

1000 rows in set (0.01 sec)

对于这个查询，我在 5 之后停止计数，因为每一个大于 5 的值都会被打上'Common'的标签。

除 0 错误

在执行包含除法的计算时，应始终注意确保分母永远不为 0。有些数据库服务器（如 Oracle Database）在遇到分母为 0 的情况时会抛出错误，但是 MySQL 只是简单地将计算结果设置为 null，如下所示：

mysql> SELECT 100 / 0;

+---------+

| 100 / 0 |

+---------+

| NULL |

+---------+

1 row in set (0.00 sec)

为了防止计算遇到错误，或者避免莫名其妙地被设为了空值 null，你应该将所有分母包装在条件逻辑中，如下所示：

mysql> SELECT c.first_name, c.last_name, 

-> sum(p.amount) tot_payment_amt, 

-> count(p.amount) num_payments, 

-> sum(p.amount) / 

-> CASE WHEN count(p.amount) = 0 THEN 1 

-> ELSE count(p.amount) 

-> END avg_payment 

-> FROM customer c 

-> LEFT OUTER JOIN payment p 

-> ON c.customer_id = p.customer_id 

-> GROUP BY c.first_name, c.last_name;

+------------+------------+-----------------+--------------+-------------+

| first_name | last_name | tot_payment_amt | num_payments | avg_payment |

+------------+------------+-----------------+--------------+-------------+

| MARY | SMITH | 118.68 | 32 | 3.708750 |

| PATRICIA | JOHNSON | 128.73 | 27 | 4.767778 |

| LINDA | WILLIAMS | 135.74 | 26 | 5.220769 |

| BARBARA | JONES | 81.78 | 22 | 3.717273 |

| ELIZABETH | BROWN | 144.62 | 38 | 3.805789 |

...

| EDUARDO | HIATT | 130.73 | 27 | 4.841852 |

| TERRENCE | GUNDERSON | 117.70 | 30 | 3.923333 |

| ENRIQUE | FORSYTHE | 96.72 | 28 | 3.454286 |

| FREDDIE | DUGGAN | 99.75 | 25 | 3.990000 |

| WADE | DELVALLE | 83.78 | 22 | 3.808182 |

| AUSTIN | CINTRON | 83.81 | 19 | 4.411053 |

+------------+------------+-----------------+--------------+-------------+

599 rows in set (0.07 sec)

此查询计算每个客户的平均付款额。因为有些可能是新客户，还没有租过电影，所以最好使用 case 表达式，以确保分母永远不为零。

条件更新

在更新表中的行时，有时需要根据条件逻辑来生成列的值。例如，假设你每周执行一项工作：对于过去 90 天内没有租过任何电影的所有客户，每次将他们 customer.active 列的值设置为 0。下面的语句将为每个客户设置值为 0 或 1：

UPDATE customer SET active =  CASE  WHEN 90 <= (SELECT datediff(now(), max(rental_date))  FROM rental r  WHERE r.customer_id = customer.customer_id)  THEN 0  ELSE 1  END WHERE active = 1;

此语句使用关联子查询来确定每个客户距离最新一次租赁的日期相隔的天数，并将该值与 90 进行比较。如果子查询返回的天数大于等于 90，则该客户将被标记为“不活跃”客户。

处理空值 null

如果列的值未知，一般用空值 null 存储，但检索的时候显示空值或者让空值参与表达式可能就不太合适了。例如，你可能希望在数据输入屏幕上显示单词 unknown，而不是显示空屏。检索数据时，如果值为 null，则可以使用 case 表达式替换字符串，如下所示：

SELECT c.first_name, c.last_name,  

CASE  

WHEN a.address IS NULL THEN 'Unknown'  

ELSE a.address  

END address,  

CASE  

WHEN ct.city IS NULL THEN 'Unknown'  

ELSE ct.city  

END city,  

CASE  

WHEN cn.country IS NULL THEN 'Unknown'  

ELSE cn.country  

END country

FROM customer c  

LEFT OUTER JOIN address a  

ON c.address_id = a.address_id  

LEFT OUTER JOIN city ct  

ON a.city_id = ct.city_id  

LEFT OUTER JOIN country cn  

ON ct.country_id = cn.country_id;

计算的时候，空值往往会导致 null 结果，如下所示：

mysql> SELECT (7 * 5) / ((3 + 14) * null);

+-----------------------------+

| (7 * 5) / ((3 + 14) * null) |

+-----------------------------+

| NULL |

+-----------------------------+

1 row in set (0.08 sec)

在执行计算时，case 表达式可用于将空值转换为一个数字，从而允许计算产生非空值的结果（通常为 0 或 1）。