SQL基础能力-CASE用法

这是我参与8月更文挑战的第2天，活动详情查看： 8月更文挑战
现在的计算机语言都具备有完善的逻辑分支判断结构。作为结构化查询的标准，SQL语法中也存在相应的语句方便逻辑处理允许我们想在程序中使用IF...ELSE的形式来执行表达式以获取不同的结果。这种语法就是CASE语法。
以下是节选自某条ETL的SQL实例
select
channel_id,ifnull(p.place_type,2) as place_type,
sum(case when (o.total_price/100<=5) and time_first_order_success between unix_timestamp('${day_start}','yyyy-MM-dd')*1000 and unix_timestamp('${day_end}','yyyy-MM-dd')*1000  then 1 else 0 end) as user_less_5_cnt,
sum(case when (o.total_price/100<=10) and time_first_order_success between unix_timestamp('${day_start}','yyyy-MM-dd')*1000 and unix_timestamp('${day_end}','yyyy-MM-dd')*1000  then 1 else 0 end) as user_less_10_cnt,
sum(case when (o.total_price/100<=15) and time_first_order_success between unix_timestamp('${day_start}','yyyy-MM-dd')*1000 and unix_timestamp('${day_end}','yyyy-MM-dd')*1000  then 1 else 0 end) as user_less_15_cnt,
table1 c join
table2 u on c.id=u.channel_id
left join  (select * from table3 where cur_date between ${start_ymd} and ${end_ymd}  and status=20 ) o  on u.id=o.buyer_id and u.time_first_order_success=o.time_create
left join (select * from table3 where cur_date between ${start_ymd} and ${end_ymd} and status = 20 ) o2 on u.id = o2.buyer_id and u.time_second_order_success = o2.time_create
left join (select *,(case when tag like '%住宅%' then 1 else 2 end) as place_type from 4) p on p.id=u.place_id
where  IFNULL(o.total_price,ifnull(o2.total_price,0)) > 0
group by channel_id,place_type
因为其使用方便，在ETL中存在大量的CASE语句。
CASE [表达式]
WHEN 表达式1 THEN result_1
WHEN 表达式2 THEN result_2 
WHEN 表达式n THEN result_n
[ELSE] [result]
END case_name
[] 表示可选
由于上面“表达式”的可选性，所以CASE语句可以分成下面两种情况。
当CASE后紧跟着表达式，这种情况下被称为简单表达式。此时，该表达式的值将会与其余剩下的"表达式1"，"表达式2"，"表达式n"所有的值进行判断,这里的表达式1..n可以是常量，也可是表达式,但是最终是相等(=)判断，注意不是(==)。如涉及到比较判断(<,>,<=等)，则需要使用后面的查询表达式。
MySQL 测试结果
mysql> SELECT
    -> CASE 2>1
    -> WHEN 3>2 THEN "true"
    -> WHEN 3<2 THEN "false"
    -> END
    -> FROM dual;
+---------------------------------------------------------+
| CASE 2>1
WHEN 3>2 THEN "true"
WHEN 3<2 THEN "false"
END |
+---------------------------------------------------------+
| true                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)
spark 实际测试
spark-sql> SELECT CASE 2>1 WHEN 3>2 THEN "true" WHEN 3<2 THEN "false" END ;
Time taken: 3.708 seconds, Fetched 1 row(s)
flink 实际测试
SELECT CASE 1 WHEN 1 THEN "true" WHEN 3<2 THEN "false" END FROM dual
由于太简单，就写个demo，不再贴引擎的测试结果。值得注意的是，在此种写法中，当表达的值或者结果是可数字化的时候，SQL在不同的引擎下会自动进行隐式的类型转化。例如在MySQL中
SELECT CASE 1 WHEN "1" THEN "true" WHEN 3<2 THEN "false" END FROM dual
SELECT CASE '1' WHEN 1 THEN "true" WHEN '1' THEN "false" END FROM dual
的结果一致。
但是到了flink中
Flink SQL> SELECT CASE 1 WHEN '1' THEN 'true' WHEN 3<2 THEN 'false' END;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '=' to arguments of type '<INTEGER> = <BOOLEAN>'. Supported form(s): '<COMPARABLE_TYPE> = <COMPARABLE_TYPE>'
再看spark的表现
spark-sql>  SELECT CASE 1 WHEN '1' THEN 'true' WHEN 3<2 THEN 'false' END;
Time taken: 0.283 seconds, Fetched 1 row(s)
结果与MySQL一致。所以这里要记笔记 - 在flink中不支持隐式的类型转化。
MySQL与spark都支持。显然这样会有一个很有意思的问题，也就是在表达式1中让引擎进行隐式转化，在表达式2中填写与表达式相同的值，那么会有什么效果？如果大家有认真看上面的sql会发现在隐式转化后，无论怎么执行，都是会优先输出第一个匹配上的表达式结果。这带来了另外一个神奇的问题 - CASE 表达式的执行顺序，经过多次的重试实验表明，证明CASE的执行顺序也是根据表达式的顺序顺序执行。
查询表达式
系统根据表达式的顺序进行表达式计算。- 这在上面已经被证明了。在查询表达式中也是按照这样的顺序执行。当所在的表达式结果为true的时候，则会返回相应表达式then后面的结果。这是最常见的CASE的用法。举个例子
MySQL
SELECT CASE WHEN 2>1 THEN 'true' WHEN 2<1 THEN 'false' END
spark
spark-sql> SELECT CASE WHEN 2>1 THEN 'true' WHEN 2<1 THEN 'false' END;
Time taken: 0.064 seconds, Fetched 1 row(s)
flink
好吧，案例是在太无聊了。那我们来点有意思的话题。 - 如果都匹配不上怎么办?
ELSE&NULL
通常情况下，CASE语句中ELSE语句就是用来解决匹配不上的问题。这无论在简单表达式还是查询表达式中都可以直接使用。
mysql> SELECT CASE WHEN 2<1 THEN 'true' WHEN 2<1 THEN 'false' ELSE 'hello' END;
+------------------------------------------------------------------+
| CASE WHEN 2<1 THEN 'true' WHEN 2<1 THEN 'false' ELSE 'hello' END |
+------------------------------------------------------------------+
| hello                                                            |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
spark
spark-sql> SELECT CASE WHEN 2<1 THEN 'true' WHEN 2<1 THEN 'false' ELSE 'hello' END;
hello
Time taken: 0.08 seconds, Fetched 1 row(s)
flink
感觉有点意思。这就和编程语言中的if..else..完美契合了。还记得最开始介绍语法时候，可以看到ELSE与result实际上是可以省略的。现如今我们已经知道了ELSE的效果,那么当完全匹配不上，又没有ELSE的时候，SQL会返回啥呢？继续测试
mysql> SELECT CASE WHEN 2<1 THEN 'true' WHEN 2<1 THEN 'false'  END;
+------------------------------------------------------+
| CASE WHEN 2<1 THEN 'true' WHEN 2<1 THEN 'false'  END |
+------------------------------------------------------+
| NULL                                                 |
+------------------------------------------------------+
1 row in set (0.00 sec)
spark
spark-sql> SELECT CASE WHEN 2<1 THEN 'true' WHEN 2<1 THEN 'false'  END;
Time taken: 0.098 seconds, Fetched 1 row(s)
flink
俗话说的好，空指针是万恶的源头。养成良好的编码习惯，应该要求开发同学在编写的过程极力避免丢失ELSE的情况，或者需要对null值进行ifnull异常处理。这在审查测试ETL SQL的过程中是必备的能力，可以在编码阶段就发现数据质量问题。
另类查询表达式
上面的查询案例都是单次的逻辑运算。即类似于A<B的判断，而最近有同事问了我一个奇怪的问题。像诸如
select case when 1<table.id<10 end from table
这种形式的SQL是可以正常执行的吗？我的第一反应就是，不行。在从接触SQL以来就没遇到过这种SQL。但是没有试验过，就无法证明这个结论是对的还是错的。所以我也分别在MySQL、spark、flink上执行相关的语句来验证了一番。
首先在MySQL中进行尝试，结论是，支持！
再在spark中
看报错，是说无法对布尔类型与数值进行比较，这让我想起了，MySQL的隐式转换，因为当flase与数值比较的时候，MySQL会将false进行隐式转换为0.如
所以这种写法的布尔表达式在MySQL中才成立。
再看flink
同样不行，根据报错信息，同样也证明了真是由于隐式转化的存在，这种写法才可以。而spark与flink均不支持隐式转化的操作。
CASE嵌套
和在编程语言中一样，SQL中的CASE语句也支持嵌套。为了解决更加复杂性的问题，在SQL中使用嵌套形如
SELECT CASE 1 WHEN "1" THEN (CASE 2 WHEN 2 THEN 'true' END) WHEN 3<2 THEN "false" END FROM dual
在spark中
spark-sql> SELECT CASE 1 WHEN '1' THEN (CASE 2 WHEN 2 THEN 'true' END) WHEN 3<2 THEN 'false' END;
Time taken: 0.153 seconds, Fetched 1 row(s)
flink,由于不支持隐式转化，我们把SQL修改为
 SELECT CASE 1 WHEN 1 THEN (CASE 2 WHEN 2 THEN 'true' END) WHEN 2 THEN 'false' END;
where 条件中的CASE
最后还有一个case语句的用法，在where条件中进行动态的使用。当然这种写法在ETL中非常的少见，因为抽取的条件一般都是固定的，不会出现变更的情况，也就不需要根据表达式的值去动态调整查询的过滤条件。如
SELECT * FROM (select 1 as `name` UNION SELECT 2 as `name`) t WHERE (CASE 1 when 1 THEN `name` END) = 2
为了相应的演示，在写法上使用了最简单的写法，只有一个WHEN条件表达式。实际上，这里的CASE语句和在select中的用法一致。
flink
spark
spark-sql> SELECT * FROM (select 1 as `name` UNION SELECT 2 as `name`) t WHERE (CASE 1 when 1 THEN `name` END) = 2;
Time taken: 1.604 seconds, Fetched 1 row(s)
看完本章，应该对CASE的大致用法以及使用场景有了更为深入的了解；
对于CASE的隐式转化，在flink中要求强类型一致，所以不支持，而MySQL与spark的支持度都很
需要留意ELSE漏写的情况，可能对数据质量产生严重的影响。
由于不同引擎的隐式转化能力，可能会导致表达式写法上存在一定的偏差，所以为了统一编码样式，建议大家用and或者or等逻辑运算符对多个布尔表达式进行连接。这样一定不会出错。