运维开发网

SQLCASE表达式的具体用法

运维开发网 https://www.qedev.com 2022-05-28 16:10 出处:网络
CASE表达式分为简单表达式与搜索表达式,本文就详细的介绍一下SQLCASE表达式的具体使用,文中根据实例编码详细介绍的十分详尽,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

CASE表达式分为简单表达式与搜索表达式,本文就详细的介绍一下SQLCASE表达式的具体使用,文中根据实例编码详细介绍的十分详尽,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

CASE表达式分为简单表达式和搜索表达式,其中搜索表达式可以覆盖简单表达式的所有能力。我也建议只写搜索表达式,不写简单表达式。

简单表达:

SELECT CASE cityWHEN '北京' THEN 1WHEN '天津' THEN 2ELSE 0END AS abcFROM test

搜索表达式:

SELECT CASEWHEN city = '北京' THEN 1WHEN city = '天津' THEN 2ELSE 0END AS abcFROM test

很明显,简单表达式只是搜索表达式a = b的一个特例,因为没有符号可以写,只要把条件改成一个gt;b不胜任,搜索表达式不仅可以轻松胜任,甚至可以写出聚合函数。


CASE 表达式里的聚合函数

为什么聚合函数可以写成CASE表达式?

因为表达式本身支持聚合函数,比如下面的语法,所以我们不会感到奇怪:

SELECT sum(pv), avg(uv) from test

SQL本身支持多种不同的聚合方法同时计算,所以在CASE表达式中使用它是很自然的:

SELECT CASEWHEN count(city) = 100 THEN 1WHEN sum(dau) gt; 200 THEN 2ELSE 0END AS abcFROM test

只要SQL表达式中有一个聚合函数,整个表达式就是聚合的,访问非聚合变量没有任何意义。所以上面的例子,即使在CASE表达式中使用了聚合,实际上也只是根据聚合一次后的条件进行判断。

这个特性可以解决很多实际问题,比如用SQL结构输出一些复杂的聚合判断条件的结果,所以大概是这样写的:

SELECT CASEWHEN 聚合函数(字段) 符合什么条件 THEN xxx... 可能有 N 个ELSE NULLEND AS abcFROM test

这也可以认为是一个行到列的转换过程,即通过一系列的CASE表达式将行聚合的结果形成新的列。


聚合与非聚合不能混用

我们希望用CASE表达式找出那些pv大于平均值的线。以下假设是错误的:

SELECT CASEWHEN pv gt; avg(pv) THEN 'yes'ELSE 'no'END AS abcFROM test

原因是只要SQL中有聚合表达式,整个SQL都是聚合的,所以返回的结果只有一个。但是,我们期望查询结果是非聚集的,但是判断条件使用聚集的结果,所以我们必须使用子查询。

为什么子查询可以解决问题?因为子查询的聚合发生在子查询中,不影响当前的父查询,了解这一点后,你就知道为什么下面的写法是正确的了:

SELECT CASEWHEN pv gt; ( SELECT avg(pv) from test ) THEN 'yes'ELSE 'no'END AS abcFROM test

这个示例还显示了子查询可以用在CASE表达式中。因为首先计算子查询,所以查询结果可以在任何地方使用,CASE表达式也不例外。


WHERE 中的 CASE

WHERE后面还可以跟CASE表达式,用于进行一些需要特殊枚举处理的过滤。

例如,下面的例子:

SELECT * FROM demo WHERECASEWHEN city = '北京' THEN trueELSE ID gt; 5END

本来我们是要查询ID大于5的数据,但是我想对北京这个城市进行特殊处理,这样就可以在判断条件中进行案件分支判断。

这种场景相当于在BI工具中创建一个CASE expression字段,可以拖拽到过滤条件中生效。


GROUP BY 中的 CASE

出乎意料的是,CASE表达式可以用GROUP BY编写:

SELECT isPower, sum(gdp) FROM test GROUP BY CASEWHEN isPower = 1 THEN city, areaELSE cityEND

上面的例子说明,在计算gdp的时候,对于非常发达的城市,按照各区的粒度来看聚合结果,即粒度更细,而对于欠发达地区,GDP本身就不高,所以直接按照城市的粒度来看聚合结果。

这样,根据不同的条件对数据进行分组和聚合。由于返回的行结果是混合的,如本例所示,可以判断isPower字段是否为1,以及是否按照城市或地区进行了聚合。如果没有其他更突出的标识,可能会导致无法区分不同行的聚合粒度,所以要谨慎使用。


ORDER BY 中的 CASE

同样,ORDER BY使用CASE表达式,排序结果根据CASE分类进行分组,每组根据自己的规则进行排序,例如:

SELECT * FROM test ORDER BY CASEWHEN isPower = 1 THEN gdpELSE peopleEND

上例中发达地区按gdp排序,否则按人口排序。


总结

案例表达有以下特点:

支持简单与搜索两种写法,推荐搜索写法。支持聚合与子查询,需要注意不同情况的特点。可以写在 SQL 查询的几乎任何地方,只要是可以写字段的地方,基本上就可以替换为 CASE 表达式。除了 SELECT 外,CASE 表达式还广泛应用在 INSERT 与 UPDATE,其中 UPDATE 的妙用是不用将 SQL 拆分为多条,所以不用担心数据变更后对判断条件的二次影响。

关于SQL CASE表达式的具体使用,本文到此为止。更多相关SQL CASE表达式

0

精彩评论

暂无评论...
验证码 换一张
取 消