数据治理 | SQL中的CASE表达式有什么用？

启研学社由知名学者担任学术顾问，高校师生与企研数据科学团队联合组建，以大数据资源及相关技术助力中国学术与智库研究为宗旨的研究组织。团队当前的主要目标是挖掘经济社会大数据资源在学术和智库领域的应用价值，开展学术大数据治理研究，以及探索大数据分析技术融入中国经济社会研究的可行进路。

前言

CASE表达式作为SQL里非常重要而且使用起来非常便利的技术，我们可以用它来描述条件分支。本文将通过五个例子来介绍CASE表达式的用法，包括转换统计方式，在SELECT、UPDATE语句里进行条件分支，在CASE表达式中使用聚合函数，利用CASE表达式进行表之间数据匹配。

1、转换统计方式

当我们统计数据时经常遇到的情况是，数据库中已有数据排列方式并不能满足我们分析的需求。我们需要将其转换为另外一种便于分析的方式进行统计，这时CASE表达式会非常有用。例如，现在有一张按照“杭州市”、“宁波市”...“丽水市"排序的农作物播种面积表，见下图：

但是现在我们想按照浙东、浙南、浙西、浙北和浙中等区域来分组，并统计这些区域的农作物播种面积。可以利用CASE表达式将城市编号转换成地区编号进行统计，效果如下：

代码如下：

SELECT CASE WHEN city IN ('温州市','台州市') THEN '浙南'
            WHEN city IN ('绍兴市','金华市') THEN '浙中'
            WHEN city IN ('丽水市','衢州市') THEN '浙西'
            WHEN '宁波市' THEN '浙东'
            ELSE '浙北' END AS district,
            SUM(area)
FROM MIANJI
GROUP BY CASE WHEN city IN ('温州市','台州市') THEN '浙南'
            WHEN city IN ('绍兴市','金华市') THEN '浙中'
            WHEN city IN ('丽水市','衢州市') THEN '浙西'
            WHEN '宁波市' THEN '浙东'
            ELSE '浙北' END AS district,
            SUM(area);

2、在SELECT语句里进行条件分支

如果我们想按照农作物播种面积为各个城市打上“大”或“小”标签，即小于200千公顷的划分到“小”，大于200千公顷的划分到“大”，并以“1”表示属于这一分类，“0”表示不属于这一分类。效果如下图，相当于按分组条件添加标签。

代码如下：

SELECT city,
       area,
       CASE WHEN area > '0' AND area <= '200' THEN '1' 
            ELSE '0' 
            END AS '小',
       CASE WHEN area > '200' THEN '1' 
            ELSE '0' 
            END AS '大'
FROM MIANJI;

3、在UPDATE语句里进行条件分支

假设我们现在希望把农作物播种面积大于250千公顷的城市播种面积减少10%，把农作物播种面积大于200千公顷小于250千公顷的城市播种面积扩大20%。如果我们先执行农作物播种面积大于250千公顷的城市播种面积缩小10%的语句，再执行农作物播种面积大于200千公顷小于250千公顷的城市播种面积扩大20%的语句，会得到错误结果。比如嘉兴市为263.47千公顷，满足大于250千公顷的条件，缩小10%后面积为237.75千公顷，此时它又符合大于200千公顷小于250千公顷的条件，因此又会扩大20%变成285.30千公顷，反而比我们想要的结果更大了。在这种情况下，我们可以在UPDATE语句里进行条件分支，以实现目的，效果见下图：

代码如下：

UPDATE MIANJI
SET area = (CASE WHEN area > '250' THEN TO_NUMBER(area)*0.9
                 WHEN area > '200' AND area < '250 THEN THEN TO_NUMBER(area)*1.2
                 ELSE TO_NUMBER(area) END);

4、在CASE表达式中使用聚合函数

假设我们现在有一张表包含出差人员编号，城市和是否是主要出差地这三个字段的路线记录表ROUTE。有的人员只去一个城市出差，如2号和5号，有的会去多个城市出差，如1号、3号和4号。对于去多个城市出差的人员，其主要城市记为Y，非主要城市记为N，而只去一个城市出差的人员都记为N。

如果我们想要将每个人员的主要出差地筛选出来，我们既可以用HAVING来统计，也可以采用CASE表达式。通过在CASE表达式中加入聚合函数，即可用SELECT字句进行分支，效果如下：

代码如下：

SELECT person_id,
       CASE WHEN COUNT(*) = 1 THEN MAX(city)
       THEN MAX(CASE WHEN main_city = 'Y' THEN city
                     ELSE NULL END)
       END AS main
FROM ROUTE
GROUP BY person_id
ORDER BY person_id;

5、利用CASE表达式进行表之间数据匹配

假设我们现在有一张包含月份和城市两个字段的出差记录表TRIP，表结构如下图：

还有一张包含城市和餐补费用的费用记录表EXPEN，表结构如下图：

我们现在想用这两张表生成下面的交叉表，以便知道各个月在各个城市的费用分布情况，我们可以利用CASE表达式来写匹配条件，效果见下图：

代码如下：

SELECT city,
       CASE WHEN city IN (SELECT city FROM TRIP WHERE month = '3月')
            THEN TO_NUMBER(expense)
            ELSE 0 END AS mar,
       CASE WHEN city IN (SELECT city FROM TRIP WHERE month = '4月')
            THEN TO_NUMBER(expense)
            ELSE 0 END AS apr,
       CASE WHEN city IN (SELECT city FROM TRIP WHERE month = '7月')
            THEN TO_NUMBER(expense)
            ELSE 0 END AS jul,
       CASE WHEN city IN (SELECT city FROM TRIP WHERE month = '10月')
            THEN TO_NUMBER(expense)
            ELSE 0 END AS oct,
       CASE WHEN city IN (SELECT city FROM TRIP WHERE month = '11月')
            THEN TO_NUMBER(expense)
            ELSE 0 END AS nov
FROM EXPEN;

结语

经过以上部分的实例介绍，我们知道CASE表达式在很多方面都有用途。它可以写在SELECT字句、GROUP BY字句和ORDER BY字句中，也可以写在WHERE字句中，高度自由的写法使得CASE表达式应用起来非常灵活。另外，相比于依赖于具体数据库的函数，CASE表达式有着更好的可移植性。作为支撑SQL声明式编程的根基之一，是广大SQL使用者不可或缺的基础技能，熟练掌握它的用法会对数据工作起到不小的帮助作用。

点击阅读原文，进入CCAD数据库

·END·

星标⭐我们不迷路！

想要文章及时到，文末“在看”少不了！

点击搜索你感兴趣的内容吧

往期推荐

数据Seminar

这里是大数据、分析技术与学术研究的三叉路口

文丨瓜瓜（王倪）校对 | 威武哥（叶武威） 

推荐丨青酱

    欢迎扫描👇二维码添加关注    

点击下方“阅读全文”了解更多