专栏首页算法研习社算法工程师-SQL进阶:强大的Case表达式

算法工程师-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表达式的分享到这里就告一段落了,很灵活&很强大,有木有!那你学会了没啊?

本文分享自微信公众号 - 算法研习社(gh_5b436407aff6),作者:wuli小萌哥

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-07-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL面试必刷题(1) Case When

    Mysql数据库中CASE WHEN语句,是用于计算条件列表并返回多个可能结果表达式之一。

    小萌哥
  • 特征工程(中)- 特征表达

    从一个完整的机器学习任务来看,在选择完特征之后,特征表达的任务就是要将一个个的样本抽象成数值向量,供机器学习模型使用。因此,特征表达就要兼顾特征属性和模型需求这...

    小萌哥
  • 机器学习必刷题-手撕推导篇(3):FM与softmax

    本文是【机器学习必刷题-手撕推导篇】第3篇文章,主要介绍FM和softMax的算法原理和推导过程,内容较干,优点是面试前方便快速回忆。

    小萌哥
  • SQL中CASE表达式的妙用

    case 表达式分为搜索表达式和简单表达式,由于搜索表达式包含了简单表达式的所有用法,此处仅介绍搜索表达式的用法。

    披头
  • 惊心:只有40%的企业有正式集中的云安全策略

    现在随着云计算的不断进步和发展,很多云计算供应商也开始作为第三方云服务商对用户进行各种云计算的服务,在云端,数据的安全性一直是用户和企业管理者一直尤为关注的,很...

    静一
  • spark save parquet in impala dir not support add columns

    1.问题: 项目中使用 spark save parquet 到 impala( impala 为分区表 ) 的目录下面,然后在 impala 中添加字段,报...

    shengjk1
  • v-html指令渲染出的内容如何添加样式

      在vue使用中,指令 v-html渲染页面经常用到,类似于jQuery的$('x').html( )去渲染。通过指令 v-html渲染出来的内容还会带有原来...

    流眸
  • 责任链模式(Chain of Responsibility)

    Avoid coupling the sender of a request to its receiver by giving more than one o...

    刘开心_1266679
  • 【Jetson开发项目展示】利用Jetson NANO和TensorRT做一个道路智能坑洞检测器

    随着社会经济的发展,汽车已经成为人们不可或缺的代步工具。然而由于汽车数量的大幅度增加,以及道路未及时养护,产生了大量严重损毁的道路。而这些损毁状况严重的路面对驾...

    GPUS Lady
  • 打造高效的Mac开发环境

    plugins=(composer cp iterm2 docker git git-extras git-flow go golang autojump sv...

    jerryteng

扫码关注云+社区

领取腾讯云代金券