HiveQL 进阶之以柔克刚 - 将简单语法运用到极致

Answer 1

前言初衷

如何高效地使用 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_n0123

通过上述 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 提供的 GroupingID 来实现，核心方法是对 GroupingID 进行逆向实现。 详细步骤如下：

一、准备好所有的 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_nameprovincecitydevice_typevisit_count省份湖北NULLNULL3省份湖南NULLNULL2省份四川NULLNULL1省份,城市湖北武汉NULL2省份,城市湖南长沙NULL1省份,城市湖南邵阳NULL1省份,城市湖北孝感NULL1省份,城市四川成都NULL1省份,城市,设备类型湖北孝感Mac1省份,城市,设备类型湖南长沙IOS1省份,城市,设备类型湖南邵阳Android1省份,城市,设备类型四川成都Windows1省份,城市,设备类型湖北武汉Android1省份,城市,设备类型湖北武汉IOS1区间

相比于数列较多用于表示离散数据，区间往往用于描述连续的数据，虽然两者具有不同的数据特征，不过在实际应用中，数列与区间的处理具有较多相通性。本节将介绍一些常见的区间场景，并抽象出通用的解决方案。

二鬼拍门区间分割

已知一个数值区间 $ [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?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;0

date_start_mergeddate_end_mergedoverlap_date2022-01-012022-01-04["2022-01-02:2","2022-01-03:2"]

? 增加点儿难度 ！

如果有多个日期区间，且区间之间交叉状态未知，上述问题又该如何求解。即：

如何合并多个日期区间，并返回合并后的多个新区间？

如何知道哪些日期是交叉日期，并返回该日期交叉次数？

SQL 实现：

--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;1

date_start_mergeddate_end_mergedoverlap_date2022-01-012022-01-04["2022-01-02:2","2022-01-03:2"]2022-01-062022-01-10["2022-01-07:2","2022-01-08:3"]应用场景举例如何按任意时段统计时间区间数据？场景描述

现有用户还款计划表 user_repayment ，该表内的一条数据，表示用户在指定日期区间内 [date_start, date_end] ，每天还款 repayment 元。

--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;2

如何统计某个时段内，每天所有用户的应还款总额？

解决思路

核心思路是将日期区间转换为日期序列，再按日期序列进行汇总统计。

SQL 实现--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;3

daytotal_repayment2022-01-15602022-01-1650排列组合

排列组合是针对离散数据常用的数据组织方法，实际应用场景中又以组合更为常见，本节将分别介绍排列、组合的实现方法，并结合实例着重介绍通过组合对数据的处理。

双马饮泉排列

已知字符序列 [ 'A', 'B', 'C' ] ，每次从该序列中可重复地选取出 2 个字符，如何获取到所有的排列？

--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;4

permAAABACBABBBCCACBCC

整体实现比较简单。

组合

已知字符序列 [ 'A', 'B', 'C' ] ，每次从该序列中可重复地选取出 2 个字符，如何获取到所有的组合？

--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;5

combAAABACBBBCCC

整体实现比较简单。

应用场景举例如何对比统计所有组合？场景描述

现有用户访问日志表 visit_log ，该表定义见 快速制造测试数据。 如何按省份两两建立对比组，按对比组展示省份的用户访问量？

对比组省份用户访问量湖北-湖南湖北xxx湖北-湖南湖南xxx解决思路

核心思路是从所有省份列表中不重复地取出 2 个省份，生成所有的组合结果，然后关联 visit_log 表分组统计结果。

SQL 实现--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;6

对比组省份用户访问量四川-湖北四川1四川-湖北湖北3四川-湖南四川1四川-湖南湖南2湖北-湖南湖北3湖北-湖南湖南2连续

本节主要介绍连续性问题，重点描述了连续活跃场景。对于静态类型的连续活跃、动态类型的连续活跃，分别阐述了不同的实现方案。 本节内容直接贴近具体的应用，大部分篇幅以 SQL 内容为主。

静态连续活跃场景统计场景描述

现有用户访问日志表 visit_log ，该表定义见 快速制造测试数据。 如何获取连续登录大于或等于 2 天的用户？

上述问题在分析连续性时，获取连续性的结果以超过固定阈值为准，可归类为 连续活跃大于 N 天的静态连续活跃场景统计。

SQL 实现基于相邻日期差实现（ lag / lead 版）--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;7

user_id101102

整体实现比较简单。

基于相邻日期差实现（排序版）--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;8

user_id101102

整体实现比较简单。

基于连续活跃天数实现--?SQL?-?2select????t.pos?as?a_nfrom?(????select?posexplode(split(space(3),?space(1))))?t;9

user_id101102

可以视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如连续活跃天数。

基于连续活跃区间实现--?SQL?-?3select????a_1?+?t.pos?*?d?as?a_nfrom?(????select?posexplode(split(space(n?-?1),?space(1))))?t;0

user_id101102

可以视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如连续活跃区间。

动态连续活跃场景统计场景描述

现有用户访问日志表 visit_log ，该表定义见 快速制造测试数据。 如何获取最长的 2 个连续活跃，输出用户、最长连续活跃天数、最长连续活跃日期区间？

上述问题在分析连续性时，获取连续性的结果不是且无法与固定的阈值作比较，而是各自以最长连续活跃作为动态阈值，可归类为 动态连续活跃场景统计。

SQL 实现

基于 静态连续活跃场景统计 的思路进行扩展即可，此处直接给出最终 SQL ：

--?SQL?-?3select????a_1?+?t.pos?*?d?as?a_nfrom?(????select?posexplode(split(space(n?-?1),?space(1))))?t;1

user_idcont_dayscont_date_startcont_date_end10132022-01-012022-01-0310222022-01-012022-01-02结语

通过灵活的、散发性的数据处理思维，就可以用最简单的语法，解决复杂的数据场景 是本篇文章贯穿全文的思想。文中针对数列生成、区间变换、排列组合、连续判别等常见的场景，给出了相对通用的解决方案，并结合实例进行了实际运用的讲解。

本篇文章尝试独辟蹊径，强调灵活的数据处理思维，希望能让读者觉得眼前一亮，更希望真的能给读者产生帮助。同时毕竟个人能力有限，思路不一定是最优的，甚至可能出现错误，欢迎提出意见或建议。为了便于交流探讨，文中的每个 SQL 都标记了编号，可以直接在评论区 @SQL编号 沟通。