前言
初衷
如何高效地应用 HiveQL ，将 HiveQL 使用到极致。
在大数据如此风行的明天，不只是业余的数据人员，须要常常地跟 SQL 打交道，即便是产品、经营等非技术搭档，也会或多或少地应用过 SQL ，如何高效地施展 SQL 的能力，继而施展数据的能力，变得尤为重要。
HiveQL 倒退到明天曾经颇为成熟，作为一种 SQL 方言，其反对大多数查问语法，具备较为丰盛的内置函数，同时还反对开窗函数、用户自定义函数、反射机制等诸多高级个性。面对一个简单的数据场景，或者有人技术娴熟，抉择应用 HiveQL 高级个性解决，如：编写用户自定义函数扩大 SQL 的数据处理能力；或者有人抉择敬而远之，转向应用其余非 SQL 类型的解决方案。本文并不探讨不同形式的优劣，而是尝试独辟蹊径，不是强调偏远的语法个性或是简单的 UDF 实现，而是强调 通过灵便的、发散性的数据处理思维，就能够用最简略的语法，解决简单的数据场景。
适宜人群
不论是数据开发初学者还是资深人员，本篇文章或者都能有所帮忙，不过更适宜中级、高级读者浏览。
本篇文章重点介绍数据处理思维，并没有波及到过多高阶的语法，同时为了防止主题发散，文中波及的函数、语法个性等，不会破费篇幅进行专门的介绍，读者能够按本身状况自行理解。
内容构造
本篇文章将围绕数列生成、区间变换、排列组合、间断判断等主题进行介绍，并附以案例进行理论使用解说。每个主题之间有轻微的前后依赖关系，顺次浏览更佳。
提示信息
本篇文章波及的 SQL 语句只应用到了 HiveQL 根本的语法个性，实践上能够在目前的支流版本中运行，同时特意注明，运行环境、兼容性等问题不在本篇文章关注范畴内。
疾速制作测试数据
生成用户拜访日志表 visit_log ，每一行数据表示一条用户拜访日志。该表将被用作下文各类场景的测试数据。
-- SQL - 1with visit_log as ( select stack ( 6, '2022-01-01', '101', '湖北', '武汉', 'Android', '2022-01-01', '102', '湖南', '长沙', 'IOS', '2022-01-01', '103', '四川', '成都', 'Windows', '2022-01-02', '101', '湖北', '孝感', 'Mac', '2022-01-02', '102', '湖南', '邵阳', 'Android', '2022-01-03', '101', '湖北', '武汉', 'IOS' ) as (dt, user_id, province, city, device_type))select * from visit_log;数列
数列是最常见的数据模式之一，理论数据开发场景中遇到的根本都是无限数列，也是本节将要重点介绍的内容。本节将从最简略的递增数列开始，找出个别办法并推广到更泛化的场景。
仙人指路
一个简略的递增数列
首先引出一个简略的递增整数数列场景：
- 从数值 \( 0 \) 开始；
- 之后的每个数值递增 \( 1 \) ；
- 至数值 \( 3 \) 完结；
如何生成满足以上三个条件的数列？即 \( [0,1,2,3] \) 。
实际上，生成该数列的形式有多种，此处介绍其中一种简略且通用的计划。
-- SQL - 2select t.pos as a_nfrom ( select posexplode(split(space(3), space(1)))) t;| a_n |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
通过上述 SQL 片段可得悉，生成一个递增序列只须要三个步骤：
① 生成一个长度适合的数组，数组中的元素不须要具备理论含意；
② 通过 UDTF 函数 posexplode 对数组中的每个元素生成索引下标；
③ 取出每个元素的索引下标。
以上三个步骤能够推广至更个别的数列场景：等差数列、等比数列。下文将以此为根底，间接给出最终实现模板。
等差数列
若设首项 \( a_1 = a \) ，公差为 \( d \) ，则等差数列的通项公式为 \( a_n = a_1 + (n - 1)d \) 。
SQL 实现：
-- SQL - 3select a_1 + t.pos * d as a_nfrom ( select posexplode(split(space(n - 1), space(1)))) t;等比数列
若设首项 \( a_1 = a \) ，公比为 \( r \) ，则等比数列的通项公式为 \( a_n = ar^{n-1} \) 。
SQL 实现：
-- SQL - 4select a_1 * pow(r, t.pos) as a_nfrom ( select posexplode(split(space(n - 1), space(1)))) t;利用场景举例
如何还原任意维度组合下的维度列簇名称？
在多维分析场景下，可能会用到高阶聚合函数，如 cube 、 rollup 、 grouping sets 等，能够针对不同的维度组合下的数据进行聚合统计。
场景形容
现有用户拜访日志表 visit_log ，该表定义见 疾速制作测试数据。
如果针对省份 province , 城市 city, 设施类型 device_type 三个维度列，通过高阶聚合函数，统计失去了不同维度组合下的用户访问量。
- 如何晓得一条统计后果是依据哪些维度列聚合进去的？
- 想要输入 聚合的维度列 的名称，用于上游的报表展现等场景，又该如何解决？
解决思路
能够借助 Hive 提供的 Grouping__ID 来实现，外围办法是对 Grouping__ID 进行逆向实现。 具体步骤如下：
一、筹备好所有的 Grouping__ID 。
① 生成一个蕴含 \( 2^x \) 个数值的递增数列，每个数值示意一种 Grouping__ID ，其中 \( x \) 为所有维度列的数量， \( 2^x \) 为所有维度组合的数量。即
\( { 0, 1, 2, ..., 2^x - 1 } \)
② 将递增数列中的每个 Grouping__ID 转为 2 进制字符串，并开展该 2 进制字符串的每个比特位。例如
3 => { 0, 0, 0, 1, 1 }
二、筹备好所有维度列的名称。
③ 生成一个字符串序列，顺次保留每个维度列的名称，即
{ dim_col_1, dim_col_2, ..., dim_col_x }
三、将 Grouping__ID 映射到维度列名称。
④ 对于递增数列中的每个数值，将该数值的 2 进制的每个比特位与维度列的下标进行映射。例如
grouping__id：3 => { 0, 0, 0, 1, 1 }
维度列：{ dim_col_1, dim_col_2, dim_col_3, dim_col_4, dim_col_5 }
映射后果：{ 0:dim_col_1, 0:dim_col_2, 0:dim_col_3, 1:dim_col_4, 1:dim_col_5 }
⑤ 对递增数列中的每个数值进行聚合，输入所有比特位等于 0 的维度列。
dim_col_1,dim_col_2,dim_col_3
留神：不同版本的 Hive 之间， Grouping__ID 实现有差别，以上解决逻辑实用于 2.3.0 及之后的版本。 2.3.0 之前的版本基于上述步骤稍加批改即可，此处不再专门破费篇幅形容。
SQL 实现
-- SQL - 5with group_dimension as ( select -- 每种分组对应的维度字段 gb.group_id, concat_ws(",", collect_list(case when gb.placeholder_bit = 0 then dim_col.val else null end)) as dimension_name from ( select groups.pos as group_id, pe.* from ( select posexplode(split(space(cast(pow(2, 3) as int) - 1), space(1))) ) groups -- 所有分组 lateral view posexplode(split(lpad(conv(groups.pos,10,2), 3, "0"), '')) pe as placeholder_idx, placeholder_bit -- 每个分组的bit信息 ) gb left join ( -- 所有维度字段 select posexplode(split("省份,城市,设施类型", ',')) ) dim_col on gb.placeholder_idx = dim_col.pos group by gb.group_id)select group_dimension.dimension_name as dimension_name, province, city, device_type, visit_countfrom ( select grouping__id as group_id, province, city, device_type, count(1) as visit_count from visit_log b group by province, city, device_type GROUPING SETS( (province), (province, city), (province, city, device_type) )) tjoin group_dimension on t.group_id = group_dimension.group_idorder by dimension_name;| dimension_name | province | city | device_type | visit_count |
|---|---|---|---|---|
| 省份 | 湖北 | NULL | NULL | 3 |
| 省份 | 湖南 | NULL | NULL | 2 |
| 省份 | 四川 | NULL | NULL | 1 |
| 省份,城市 | 湖北 | 武汉 | NULL | 2 |
| 省份,城市 | 湖南 | 长沙 | NULL | 1 |
| 省份,城市 | 湖南 | 邵阳 | NULL | 1 |
| 省份,城市 | 湖北 | 孝感 | NULL | 1 |
| 省份,城市 | 四川 | 成都 | NULL | 1 |
| 省份,城市,设施类型 | 湖北 | 孝感 | Mac | 1 |
| 省份,城市,设施类型 | 湖南 | 长沙 | IOS | 1 |
| 省份,城市,设施类型 | 湖南 | 邵阳 | Android | 1 |
| 省份,城市,设施类型 | 四川 | 成都 | Windows | 1 |
| 省份,城市,设施类型 | 湖北 | 武汉 | Android | 1 |
| 省份,城市,设施类型 | 湖北 | 武汉 | IOS | 1 |
区间
相比于数列较多用于示意离散数据，区间往往用于形容间断的数据，尽管两者具备不同的数据特色，不过在理论利用中，数列与区间的解决具备较多相通性。本节将介绍一些常见的区间场景，并形象出通用的解决方案。
二鬼拍门
区间宰割
已知一个数值区间 \( [a,b] = \{ x | a \leq x \leq b \} \) ，如何将该区间均分成 \( n \) 段子区间？
该问题能够简化为数列问题，数列公式为 \( a_n = a_1 + (n - 1)d \) ，其中 \( a_1 = a \) ， \( d = (b - a) / n \) ：
① 生成一个长度为 \( n \) 的数组，数组中的元素不须要具备理论含意；
② 通过 UDTF 函数 posexplode 对数组中的每个元素生成索引下标；
③ 取出每个元素的索引下标，并进行数列公式计算，得出每个子区间的起始值与完结值。
SQL 实现：
-- SQL - 6select a_1 + t.pos * d as sub_interval_start, -- 子区间起始值 a_1 + (t.pos + 1) * d as sub_interval_end -- 子区间完结值from ( select posexplode(split(space(n - 1), space(1)))) t;区间穿插
已知两个日期区间存在穿插 ['2022-01-01', '2022-01-03'] 、 ['2022-01-02', '2022-01-04']
- 如何合并两个日期区间，并返回合并后的新区间？
- 如何晓得哪些日期是穿插日期，并返回该日期穿插次数？
解决上述问题的办法有多种，此处介绍其中一种简略且通用的计划。
外围思路是联合数列生成、区间宰割办法，先将日期区间合成为最小处理单元，即多个日期组成的数列，而后再基于日期粒度做统计。具体步骤如下：
① 获取每个日期区间蕴含的天数；
② 按日期区间蕴含的天数，将日期区间拆分为相应数量的递增日期序列；
③ 通过日期序列统计合并后的区间，穿插次数；
SQL 实现：
-- SQL - 7with tbl as ( select stack( 2, '2022-01-01', '2022-01-03', '2022-01-02', '2022-01-04' ) as (date_start, date_end))select min(date_item) as date_start_merged, max(date_item) as date_end_merged, collect_set( -- 穿插日期计数 case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end ) as overlap_datefrom ( select -- 拆解后的单个日期 date_add(date_start, pos) as date_item, -- 拆解后的单个日期呈现的次数 count(1) over(partition by date_add(date_start, pos)) as date_item_cnt from tbl lateral view posexplode(split(space(datediff(date_end, date_start)), space(1))) t as pos, val) t;| date_start_merged | date_end_merged | overlap_date |
|---|---|---|
| 2022-01-01 | 2022-01-04 | ["2022-01-02:2","2022-01-03:2"] |
减少点儿难度 ！
如果有多个日期区间，且区间之间穿插状态未知，上述问题又该如何求解。即：
- 如何合并多个日期区间，并返回合并后的多个新区间？
- 如何晓得哪些日期是穿插日期，并返回该日期穿插次数？
SQL 实现：
-- SQL - 8with tbl as ( select stack( 5, '2022-01-01', '2022-01-03', '2022-01-02', '2022-01-04', '2022-01-06', '2022-01-08', '2022-01-08', '2022-01-08', '2022-01-07', '2022-01-10' ) as (date_start, date_end))select min(date_item) as date_start_merged, max(date_item) as date_end_merged, collect_set( -- 穿插日期计数 case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end ) as overlap_datefrom ( select -- 拆解后的单个日期 date_add(date_start, pos) as date_item, -- 拆解后的单个日期呈现的次数 count(1) over(partition by date_add(date_start, pos)) as date_item_cnt, -- 对于拆解后的单个日期，重组为新区间的标记 date_add(date_add(date_start, pos), 1 - dense_rank() over(order by date_add(date_start, pos))) as cont from tbl lateral view posexplode(split(space(datediff(date_end, date_start)), space(1))) t as pos, val) tgroup by cont;| date_start_merged | date_end_merged | overlap_date |
|---|---|---|
| 2022-01-01 | 2022-01-04 | ["2022-01-02:2","2022-01-03:2"] |
| 2022-01-06 | 2022-01-10 | ["2022-01-07:2","2022-01-08:3"] |
利用场景举例
如何按任意时段统计工夫区间数据？
场景形容
现有用户还款计划表 user_repayment ，该表内的一条数据，示意用户在指定日期区间内 [date_start, date_end] ，每天还款 repayment 元。
-- SQL - 9with user_repayment as ( select stack( 3, '101', '2022-01-01', '2022-01-15', 10, '102', '2022-01-05', '2022-01-20', 20, '103', '2022-01-10', '2022-01-25', 30 ) as (user_id, date_start, date_end, repayment))select * from user_repayment;如何统计某个时段内，每天所有用户的应还款总额？
解决思路
外围思路是将日期区间转换为日期序列，再按日期序列进行汇总统计。
SQL 实现
-- SQL - 10select date_item as day, sum(repayment) as total_repaymentfrom ( select date_add(date_start, pos) as date_item, repayment from user_repayment lateral view posexplode(split(space(datediff(date_end, date_start)), space(1))) t as pos, val) twhere date_item >= '2022-01-15' and date_item <= '2022-01-16'group by date_itemorder by date_item;| day | total_repayment |
|---|---|
| 2022-01-15 | 60 |
| 2022-01-16 | 50 |
排列组合
排列组合是针对离散数据罕用的数据组织办法，理论利用场景中又以组合更为常见，本节将别离介绍排列、组合的实现办法，并联合实例着重介绍通过组合对数据的解决。
双马饮泉
排列
已知字符序列 [ 'A', 'B', 'C' ] ，每次从该序列中可反复地选取出 2 个字符，如何获取到所有的排列？
-- SQL - 11select concat(val1, val2) as permfrom (select split('A,B,C', ',') as characters) dummylateral view explode(characters) t1 as val1lateral view explode(characters) t2 as val2;| perm |
|---|
| AA |
| AB |
| AC |
| BA |
| BB |
| BC |
| CA |
| CB |
| CC |
整体实现比较简单。
组合
已知字符序列 [ 'A', 'B', 'C' ] ，每次从该序列中可反复地选取出 2 个字符，如何获取到所有的组合？
-- SQL - 12select concat(least(val1, val2), greatest(val1, val2)) as combfrom (select split('A,B,C', ',') as characters) dummylateral view explode(characters) t1 as val1lateral view explode(characters) t2 as val2group by least(val1, val2), greatest(val1, val2);| comb |
|---|
| AA |
| AB |
| AC |
| BB |
| BC |
| CC |
整体实现比较简单。
利用场景举例
如何比照统计所有组合？
场景形容
现有用户拜访日志表 visit_log ，该表定义见 疾速制作测试数据。
如何按省份两两建设比照组，按比照组展现省份的用户访问量？
| 比照组 | 省份 | 用户访问量 |
|---|---|---|
| 湖北-湖南 | 湖北 | xxx |
| 湖北-湖南 | 湖南 | xxx |
解决思路
外围思路是从所有省份列表中不反复地取出 2 个省份，生成所有的组合后果，而后关联 visit_log 表分组统计后果。
SQL 实现
-- SQL - 13select combs.province_comb, log.province, count(1) as visit_countfrom visit_log logjoin ( -- 所有比照组 select concat(least(val1, val2), '-', greatest(val1, val2)) as province_comb, least(val1, val2) as province_1, greatest(val1, val2) as province_2 from ( select collect_set(province) as provinces from visit_log ) dummy lateral view explode(provinces) t1 as val1 lateral view explode(provinces) t2 as val2 where val1 <> val2 group by least(val1, val2), greatest(val1, val2)) combs on 1 = 1where log.province in (combs.province_1, combs.province_2)group by combs.province_comb, log.provinceorder by combs.province_comb, log.province;| 比照组 | 省份 | 用户访问量 |
|---|---|---|
| 四川-湖北 | 四川 | 1 |
| 四川-湖北 | 湖北 | 3 |
| 四川-湖南 | 四川 | 1 |
| 四川-湖南 | 湖南 | 2 |
| 湖北-湖南 | 湖北 | 3 |
| 湖北-湖南 | 湖南 | 2 |
间断
本节次要介绍连续性问题，重点形容了间断沉闷场景。对于动态类型的间断沉闷、动静类型的间断沉闷，别离论述了不同的实现计划。
本节内容间接贴近具体的利用，大部分篇幅以 SQL 内容为主。
动态间断沉闷场景统计
场景形容
现有用户拜访日志表 visit_log ，该表定义见 疾速制作测试数据。
如何获取间断登录大于或等于 2 天的用户？
上述问题在剖析连续性时，获取连续性的后果以超过固定阈值为准，可归类为 间断沉闷大于 N 天的动态间断沉闷场景统计。
SQL 实现
基于相邻日期差实现（ lag / lead 版）
-- SQL - 14select user_idfrom ( select *, lag(dt, 2 - 1) over(partition by user_id order by dt) as lag_dt from (select dt, user_id from visit_log group by dt, user_id) t0) t1where datediff(dt, lag_dt) + 1 = 2group by user_id;| user_id |
|---|
| 101 |
| 102 |
整体实现比较简单。
基于相邻日期差实现（排序版）
-- SQL - 15select user_idfrom ( select *, dense_rank() over(partition by user_id order by dt) as dr from visit_log) t1where datediff(dt, date_add(dt, 1 - dr)) + 1 = 2group by user_id;| user_id |
|---|
| 101 |
| 102 |
整体实现比较简单。
基于间断沉闷天数实现
-- SQL - 16select user_idfrom ( select *, -- 间断沉闷天数 count(distinct dt) over(partition by user_id, cont) as cont_days from ( select *, date_add(dt, 1 - dense_rank() over(partition by user_id order by dt)) as cont from visit_log ) t1) t2where cont_days >= 2group by user_id;| user_id |
|---|
| 101 |
| 102 |
能够视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如间断沉闷天数。
基于间断沉闷区间实现
-- SQL - 17select user_idfrom ( select user_id, cont, -- 间断沉闷区间 min(dt) as cont_date_start, max(dt) as cont_date_end from ( select *, date_add(dt, 1 - dense_rank() over(partition by user_id order by dt)) as cont from visit_log ) t1 group by user_id, cont) t2where datediff(cont_date_end, cont_date_start) + 1 >= 2group by user_id;| user_id |
|---|
| 101 |
| 102 |
能够视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如间断沉闷区间。
动静间断沉闷场景统计
场景形容
现有用户拜访日志表 visit_log ，该表定义见 疾速制作测试数据。
如何获取最长的 2 个间断沉闷，输入用户、最长间断沉闷天数、最长间断沉闷日期区间？
上述问题在剖析连续性时，获取连续性的后果不是且无奈与固定的阈值作比拟，而是各自以最长间断沉闷作为动静阈值，可归类为 动静间断沉闷场景统计。
SQL 实现
基于 动态间断沉闷场景统计 的思路进行扩大即可，此处间接给出最终 SQL ：
-- SQL - 18select user_id, -- 最长间断沉闷天数 datediff(max(dt), min(dt)) + 1 as cont_days, -- 最长间断沉闷日期区间 min(dt) as cont_date_start, max(dt) as cont_date_endfrom ( select *, date_add(dt, 1 - dense_rank() over(partition by user_id order by dt)) as cont from visit_log) t1group by user_id, contorder by cont_days desclimit 2;| user_id | cont_days | cont_date_start | cont_date_end |
|---|---|---|---|
| 101 | 3 | 2022-01-01 | 2022-01-03 |
| 102 | 2 | 2022-01-01 | 2022-01-02 |
结语
通过灵便的、散发性的数据处理思维，就能够用最简略的语法，解决简单的数据场景 是本篇文章贯通全文的思维。文中针对数列生成、区间变换、排列组合、间断判断等常见的场景，给出了绝对通用的解决方案，并联合实例进行了理论使用的解说。
本篇文章尝试独辟蹊径，强调灵便的数据处理思维，心愿能让读者感觉眼前一亮，更心愿真的能给读者产生帮忙。同时毕竟集体能力无限，思路不肯定是最优的，甚至可能呈现谬误，欢送提出意见或倡议。为了便于交换探讨，文中的每个 SQL 都标记了编号，能够间接在评论区 @SQL编号 沟通。