算法工程师-SQL进阶：强大的Case表达式

别叫我算法工程师，我是sql-boy！

相信大家对SQL都非常熟悉了，可能有些小伙伴会有疑问，算法工程师不是跑模型的吗？还需要学SQL？其实，很有必要！原因大概有以下几点吧：

（1）算法工程师很重要的一个能力就是探索数据、挖掘价值的能力，而数据从哪里来：SQL；

（2）目前很多厂都已经对机器学习pipeline做了很好的封装，其实几年前就可以通过拖拽搭建一个推荐系统了。那项目的大框架既然都已经搭好了，算法工程师还做什么呢？其实很大一部分时间还是：写SQL、分析数据、挖特征。

那你说，SQL能力重要不？

本节先介绍一下SQL中高频使用的case表达式，请认真感受它的灵活与强大！

一、case表达式是什么

case 表达式是 SQL 里非常重要而且使用起来非常便利的技术，我们常用它来描述条件分支。

case表达式的写法如下：

（1）简单case表达式

CASE sex
   WHEN '1' THEN '男'
   WHEN '2' THEN '女'
ELSE '其他' END

（2）搜索case表达式（功能覆盖了简单case,因此更常用）

CASE WHEN sex = '1' THEN '男'
    WHEN sex = '2' THEN '女'
ELSE '其他' 
END

case表达式在书写时的注意事项：

• 统一各分支的返回数据类型：千万不要有的分支返回字符串，有的分支返回数值；
• 不要忘了写 END
• 写上 ELSE 子句：虽然不写不报错（返回NULL)，但非常不利于问题排查

二、case有什么用

1、重命名

eg1: 假如有如下一张"学生成绩表"，其中存储了每个学生的成绩，现在想根据一些规则将学生分为不同的等级，映射关系如下：

• score<60 : 不及格
• 60=<score<80 : 良好
• score>=80 : 优秀

参考SQL：

SELECT name,
       CASE
           WHEN score < 60 THEN '不及格'
           WHEN score < 80 THEN '良好'
           ELSE '优秀'
       END AS '级别'
FROM student_score

解析：这是case表达式最初级的用法，它的主要原理是：依据不同的条件规则(可以是很复杂的逻辑)，产生不同的值。你也可以同时写多个case表达式，但是每个case表达式的结果都将作为一列返回到最终的查询结果中。

2、自定义分组规则

case表达式可以用在group子句中，作用是：自定义分组规则。

eg2: 这里还是采用eg1中的学生成绩表，现在想根据这些规则把学生分为几个小组，并输出每组的学生人数。

• score<60 : 不及格
• 60=<score<80 : 良好
• score>=80 : 优秀

参考SQL：

SELECT CASE
            WHEN score < 60 THEN '不及格'
            WHEN score < 80 THEN '良好'
            ELSE '优秀'
        END AS '级别',
       COUNT(*) AS '人数'
FROM student_score
GROUP BY CASE
              WHEN score < 60 THEN '不及格'
              WHEN score < 80 THEN '良好'
              ELSE '优秀'
          END 

解析：当使用case表达式时，你要注意，case表达式的结果是一个值。值在SQL语句中的位置非常灵活，可以放在select、where以及group by等多个地方。

在本题中要注意sql先执行group by，然后最后在执行select。当sql执行group by时，会根据case设定的分支规则将每一行映射为一个值，映射完成后，在根据映射完的值的种类将每行进行归组；当执行select语句时，每个组的组名是什么呢？因为group by映射完的值不会保留给select用，因此select要自己在映射一遍，当然了，此时每个小组内映射完的值应该是同一个值，此时，再用count等聚合函数进行小组内的统计就好啦~

3、case表达式在聚合函数内使用

eg3: 现在有一张城市-人口明细表，请统计每个城市不同性别的人口（交叉表）。

参考SQL：

SELECT pref_name,
       sum(CASE WHEN sex=1 THEN population ELSE 0 END) AS cnt_m,
       sum(CASE WHEN sex=2 THEN population ELSE 0 END) AS cnt_f
FROM population
GROUP BY pref_name;

解析：先按城市分组，然后，在select语句中使用了两个case表达式，第一个case表达式是将所有sex!=1的映射为0，第二个表达式相反。然后在每个case表达式外面套一个sum聚合函数，统计每个sex下的sum值。

4、聚合函数在case表达式内使用

eg4: 假设有一张俱乐部成员明细表，每个学生可以参加一到多个俱乐部，如果参加了多个俱乐部，需要将其中一个设置为主俱乐部（main_club_flag='Y'）。现在请统计每个学生最具代表性的俱乐部。

逻辑如下图：

参考SQL：

SELECT stu_id,
       CASE
           WHEN COUNT(*)=1 THEN MAX(club_name)
           ELSE MAX(
            CASE WHEN main_club_flag='Y' 
            THEN club_name 
            ELSE NULL 
            END)
       END AS '最喜欢的club'
FROM club
GROUP BY stu_id

解析：先按学生分组，并将每个学生参加俱乐部的数量count(*)作为case的第一级判断条件，如果count=1,则就选那一个俱乐部即可；如果有多个，怎么筛选flag='Y'的那个呢？？

可以利用一个case表达式，将flag='Y'作为一个分支，如果符合了，保留，不符合的变为NULL，最后在该小组内max一下就得到非NULL的那行了。max等聚合函数具有过滤NULL的作用。

这个例子的技巧是：分组后将count(*)等聚合函数作为分支判断条件，和having筛选group有相似的作用，但是比having功能更强大的是，case可以多层嵌套使用，在小组内部还可以使用case表达式进行分支过滤和重构，最后在进行聚合，完美！

5、在having中使用

eg5: 有一张工资表如下，请设计一个SQL：求出所有员工工资的中位数。

注意：如果员工有偶数个，中位数应该是中间两个数的平均值。

参考SQL：

SELECT avg(salary)
FROM
  (SELECT s1.salary
   FROM salaries s1, salaries s2
   GROUP BY s1.salary 
   HAVING sum(CASE WHEN s2.salary >= s1.salary THEN 1 ELSE 0 END) >= count(*)/2
   AND sum(CASE WHEN s2.salary <= s1.salary THEN 1 ELSE 0 END) >= count(*)/2) TEMP;

解析：一般说来，当要根据某一列的大小关系求该列的子集时，要用到非等值自连接(后面章节会讲到)。

我们先看 FROM salaries s1, salaries s2，而且没有指定任何连接键，它的作用就是求两个表的笛卡尔积，值得注意的是，这两个表其实是同一张表，在这里只是起的名字不一样，这种连接方法叫做自连接（后面章节会讲到）。在这个例子中，s1和s2自连接后的table应该有4*4=16行。

然后，GROUP BY s1.salary子句将table按s1.salary进行了分组，应该是分为了4组，每个小组都有4行。

这里要注意到having子句对每个小组的筛选逻辑：

• 第一个case表达式是将每个小组内，s2(从表)的salary>=s1的salary(小组标志)的行筛选出来，通过sum统计总有多少行，设置一个过滤逻辑cond1：sum值>=该小组一半行数的小组
• 第二个case表达式与第一个的差异就是将大于号换成了小于号，过滤逻辑cond2：sum值<=该小组一半行数的小组

到这里就可以看出，having对4个小组的筛选逻辑是：cond1 and cond2,也就是说两个条件都满足的小组才能被筛选出来。

最后，将过滤出来的小组的salary求个平均值即得到中位数，因为，如果是偶数个员工，中位树可能是中间不一样的两个数。

本题不太好理解，建议同学们，结合实例，一步一步的打印出来，看一下每步的结果，就明白了。

6、在order by中使用

eg6: 有下面一张表，每个key都有三个记录值x、y和z，现在想找到每个key的最大值，并将key按照B、A、C、D的顺序依次展示出来。

参考SQL：

SELECT `key`,
       CASE
           WHEN (CASE
                     WHEN `x`>`y` THEN `x`
                     ELSE `y`
                 END) < `z` THEN `z`
           ELSE (CASE
                     WHEN `x`>`y` THEN `x`
                     ELSE `y`
                 END)
       END AS greatest -- 这一部分是找每个key的最大值，和排序没有关系~
FROM Greatests
ORDER BY CASE `key`
             WHEN 'B' THEN 1
             WHEN 'A' THEN 2
             WHEN 'D' THEN 3
             WHEN 'C' THEN 4
             ELSE NULL
         END; -- 这一部分才是自定义排序的要点

解析：这道题应该拆解为两个部分：(1)找到每个key的最大值（2）查询结果按照自定义顺序展示。

先说(1)，使用一个case表达式就可以得到两个数的最大值，然后在嵌套一个case表达式就可以得到三个数的最大值。（2）的话，看代码可以知道，order by子句中，利用case表达式给每行的key临时映射了一个值，这个值就是题目中要求的'出场顺序'，要求排在前面的，映射的值小。（当然反序排列也可以~）

7、在update中使用

eg7: 有下面一张员工-工资表，现在老板想调薪，大于等于30万的降10%，25万到28万的涨20%，如何用SQL实现？需要注意的是，如果降薪和涨薪分两步操作，可能会产生逻辑问题。比如，先降薪，那有的员工降薪完，其薪资可能符合下一步要涨薪的范围，薪资又会涨回去了。咋办呢？

参考SQL：

UPDATE salaries
SET salary = CASE
                 WHEN salary>=300000 THEN salary*0.9
                 WHEN salary >=250000
                      AND salary <280000 THEN salary *1.2
                 ELSE salary
             END

解析：update中，可以利用case语句设置不同情况的处理逻辑，update是按行更新，同时进行，不会导致上面所说的逻辑前后混乱的问题。

三、case表达式总结

首先，恭喜坚持看到这里的小伙伴，相信你一定收获了很多。下面，我们对本节的内容做个总结。

• case表达式在SQL中是很常用的，它可以通过设置多组条件映射出不同的值；
• 时刻记得case表达式的结果是一个值，因此，它在sql中的位置相当灵活，不要忘了用end去结束一个case;
• case表达式用在group by子句，可以实现自定义分组逻辑；
• case表达式可以结合统计函数使用，可以在统计函数内使用，也可以在统计函数外使用，因为，它是一个值。
• 当case表达式用在having子句中，可以对小组设置灵活的过滤逻辑；
• 当需要自定义排序顺序时，可以在order by中通过使用case表达式来实现；
• case表达式还可以应用在update中，根据不同的分支条件采取不同的更新策略，而且是一步到位！

好啦，case表达式的分享到这里就告一段落了，很灵活&很强大，有木有！那你学会了没啊？