case when、连续区间查询

最近为了帮其他组赶项目，着实当了一把hive sql工具人，最大的收获还是系统的了解了case when、开窗函数的应用。为了便于以后回顾使用，特整理成文章进行记录分享。

一、case when

1.1 定义

hive官网（CASE Expression - PL/HQL Reference - HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS）一般不是叫case when，而是称为case表达式，其官方语法格式有两类，如下：

1) Simple CASE expression: 简单case表达式

CASE expr
  WHEN expr THEN expr
  ...
  [ELSE expr] 
END

2) Searched CASE expression: 搜索类case表达式

CASE 
  WHEN boolean_expr THEN expr
  ...
  [ELSE expr] 
END

注意：
1）如果when表达式未匹配，且else没有定义，则会返回null。
2）Case函数只返回第一个符合条件的值，剩下的when部分将会被自动忽略。
3）简单case表达式仅支持直接匹配，不支持判断式的选择，在搜索类Case表达式中，可以使用BETWEEN、LIKE、IS NULL、IN、EXISTS等进行条件判断。因此实际使用中搜索类表达式用的比较多（概念比较抽象，下面举个例子）

/** 简单case表达式，支持直接匹配 **/
select 
    id,
    name,
    (CASE sex WHEN 'male' THEN '男' ELSE '女' END ) as sex
from
    persons

/** 搜索类case表达式，支持判断式 **/
select 
    id,
    name,
    (CASE WHEN sex = 'male' THEN '男' ELSE '女' END ) as sex
from
    persons

1.2、使用场景

1）列值的简单修改替换
简单case表达式和搜索类case表达式都可以实现，如上面的性别替换

select 
    id,
    name,
    (CASE sex WHEN 'male' THEN '男' ELSE '女' END ) as sex
from
    persons

2）根据不同条件对数据进行不同的 普通处理 / 聚合处理
因为简单case表达式不支持条件判断，所以这种一般只能用搜索类case表达式，如对不同成绩段的学生打上优良差三种标签。

select 
    id,
    name,
    (case
        when grade > 90 then 'excellent' 
        when grade > 70 then 'good'
        else 'poor' end
    ) as grade_label
from
    persons

上面是普通处理，下面举个聚合处理的例子，比如统计男生女生的人数

select
    /** 统计男生数量，如果表中数据重复不能用该方式统计 */
    sum(case when sex = 'male' then 1 else 0 end) as boy_nums,
    /** 统计女生数量，如果表中数据重复则在case 前面加distinct字段进行去重 */
    /** count(distinct case when sex = 'female' then name else null end) as girl_nums */
    count(case when sex = 'female' then name else null end) as girl_nums
from 
    persons

注意：
上面这个例子有几个点可以留意下，工作中较常用。
        一是在某些情况下sum和count的相互转换；
        二是聚合函数与case when的整合；
        三是distinct字段在case when中的使用方式（不匹配返回null表示不纳入计算）；

3）分组数据进行不同的聚合处理
比如统计男生年龄的总和和女生分数的总和

select
    sum(case when sex = 'male' then age else 0 end) as boy_age_total,
    sum(case when sex = 'female' then grade else 0 end) as girl_grade_total,
    sex
from 
    persons
group by
    sex
    

1.3 优势

目前感受比较明显的有两点，一是sql编辑更加灵活，二是提升性能，在一轮过滤条件下计算多种条件匹配下的数据。

二、连续区间查询

比如说统计年龄段[0-10)，[10-20)，[20-30)的连续区间人数，如下：

select
    //注意这里不要用between语法，因为between左右区间都是闭区间，与我们的左闭右开不符合
    count(distinct case when age >=0  and age<10 then id else null end) as stage1,
    count(distinct case when age >=10 and age<20 then id else null end) as stage2,
    count(distinct case when age >=20 and age<30 then id else null end) as stage3
from 
    persons 

可以看到，使用case when 可以很灵活的定义查询的空间，而且结构清晰。
但是该方式不够智能自动化，一般是有多少区间就要加多少个case when，自然而然的也就要有多少个区间字段。如果能直接查询区间值然后group by，比如mysql的internal和elt结合可以几乎做到区间的自动统计。但是在hive sql中我目前还未发现该使用方式，如果有哪位道友知道，欢迎留言指导，谢谢！