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

原创2021-06-11 10:12·DatabriAI

数据与智能 本公众号关注大数据与人工智能技术。由一批具备多年实战经验的技术极客参与运营管理，持续输出大数据、数据分析、推荐系统、机器学习、人工智能等方向的原创文章，每周至少输出7篇精品原创。同时，我们会关注和分享大数据与人工智能行业动态。欢迎关注。

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L


全文共5948字，预计阅读时间30分钟。


第十一章 条件逻辑

1. 什么是条件逻辑

2. case 表达式

2.1 查找型 case 表达式

2.2 简单 case 表达式

3. case 表达式范例

3.1 结果集变换

3.2 存在性检查

3.3 除 0 错误

3.4 条件更新

3.5 处理空值 null


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



1. 什么是条件逻辑


简而言之，条件语句是在程序执行过程中从多个路径中选择其一的能力。例如，在查询客户信息时，你可能希望包括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”。



2. 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表达式的实例来进行说明。


2.1 查找型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列上进行分组更有效。


2.2 简单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表达式。



3. case表达式范例


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


3.1 结果集变换


你可能遇到过这样的情况：你在对有限值集（例如一周中的几天）执行聚合操作时，希望结果集只有一行而每个值对应一列，而非每个值一行。例如，如果要编写一个查询，显示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子句。




3.2 存在性检查


有时，你需要确定两个实体之间是否存在关系，而不关心数量的多少。例如，你可能想知道一个演员是否至少出演过一部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'的标签。


3.3 除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表达式，以确保分母永远不为零。


3.4 条件更新


在更新表中的行时，有时需要根据条件逻辑来生成列的值。例如，假设你每周执行一项工作：对于过去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，则该客户将被标记为“不活跃”客户。


3.5 处理空值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）。

举报
评论 0
    热门：