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

前言

初衷

如何高效地应用 HiveQL，将 HiveQL 使用到极致。

在大数据如此风行的明天，不只是业余的数据人员，须要常常地跟 SQL 打交道，即便是产品、经营等非技术搭档，也会或多或少地应用过 SQL，如何高效地施展 SQL 的能力，继而施展数据的能力，变得尤为重要。

HiveQL 倒退到明天曾经颇为成熟，作为一种 SQL 方言，其反对大多数查问语法，具备较为丰盛的内置函数，同时还反对开窗函数、用户自定义函数、反射机制等诸多高级个性。面对一个简单的数据场景，或者有人技术娴熟，抉择应用 HiveQL 高级个性解决，如：编写用户自定义函数扩大 SQL 的数据处理能力；或者有人抉择敬而远之，转向应用其余非 SQL 类型的解决方案。本文并不探讨不同形式的优劣，而是尝试独辟蹊径，不是强调偏远的语法个性或是简单的 UDF 实现，而是强调 通过灵便的、发散性的数据处理思维，就能够用最简略的语法，解决简单的数据场景。

适宜人群

不论是数据开发初学者还是资深人员，本篇文章或者都能有所帮忙，不过更适宜中级、高级读者浏览。

本篇文章重点介绍数据处理思维，并没有波及到过多高阶的语法，同时为了防止主题发散，文中波及的函数、语法个性等，不会破费篇幅进行专门的介绍，读者能够按本身状况自行理解。

内容构造

本篇文章将围绕数列生成、区间变换、排列组合、间断判断等主题进行介绍，并附以案例进行理论使用解说。每个主题之间有轻微的前后依赖关系，顺次浏览更佳。

提示信息

本篇文章波及的 SQL 语句只应用到了 HiveQL 根本的语法个性，实践上能够在目前的支流版本中运行，同时特意注明，运行环境、兼容性等问题不在本篇文章关注范畴内。

疾速制作测试数据

生成用户拜访日志表 visit_log，每一行数据表示一条用户拜访日志。该表将被用作下文各类场景的测试数据。

-- SQL - 1
with 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 - 2
select
    t.pos as a_n
from (select posexplode(split(space(3), space(1)))
) t;

通过上述 SQL 片段可得悉，生成一个递增序列只须要三个步骤：
① 生成一个长度适合的数组，数组中的元素不须要具备理论含意；
② 通过 UDTF 函数 posexplode 对数组中的每个元素生成索引下标；
③ 取出每个元素的索引下标。
以上三个步骤能够推广至更个别的数列场景：等差数列、等比数列。下文将以此为根底，间接给出最终实现模板。

等差数列

若设首项 \(a_1 = a \)，公差为 \(d \)，则等差数列的通项公式为 \(a_n = a_1 + (n – 1)d \)。
SQL 实现：

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

等比数列

若设首项 \(a_1 = a \)，公比为 \(r \)，则等比数列的通项公式为 \(a_n = ar^{n-1} \)。
SQL 实现：

-- SQL - 4
select
    a_1 * pow(r, t.pos) as a_n
from (select posexplode(split(space(n - 1), space(1)))
) t;

利用场景举例

如何还原任意维度组合下的维度列簇名称？

在多维分析场景下，可能会用到高阶聚合函数，如 cube、rollup、grouping sets 等，能够针对不同的维度组合下的数据进行聚合统计。

场景形容

现有用户拜访日志表 visit_log，该表定义见 疾速制作测试数据 。
如果针对省份 province , 城市 city, 设施类型 device_type 三个维度列，通过高阶聚合函数，统计失去了不同维度组合下的用户访问量。

1. 如何晓得一条统计后果是依据哪些维度列聚合进去的？
2. 想要输入 聚合的维度列 的名称，用于上游的报表展现等场景，又该如何解决？

解决思路

能够借助 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 - 5
with 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_count
from (
    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)
    )
) t
join group_dimension on t.group_id = group_dimension.group_id
order by dimension_name;

区间

相比于数列较多用于示意离散数据，区间往往用于形容间断的数据，尽管两者具备不同的数据特色，不过在理论利用中，数列与区间的解决具备较多相通性。本节将介绍一些常见的区间场景，并形象出通用的解决方案。

二鬼拍门

区间宰割

已知一个数值区间 \([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 - 6
select
    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’]

1. 如何合并两个日期区间，并返回合并后的新区间？
2. 如何晓得哪些日期是穿插日期，并返回该日期穿插次数？

解决上述问题的办法有多种，此处介绍其中一种简略且通用的计划。
外围思路是联合数列生成、区间宰割办法，先将日期区间合成为最小处理单元，即多个日期组成的数列，而后再基于日期粒度做统计。具体步骤如下：
① 获取每个日期区间蕴含的天数；
② 按日期区间蕴含的天数，将日期区间拆分为相应数量的递增日期序列；
③ 通过日期序列统计合并后的区间，穿插次数；

SQL 实现：

-- SQL - 7
with 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_date
from (
    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;

🤔 减少点儿难度！
如果有多个日期区间，且区间之间穿插状态未知，上述问题又该如何求解。即：

1. 如何合并多个日期区间，并返回合并后的多个新区间？
2. 如何晓得哪些日期是穿插日期，并返回该日期穿插次数？

SQL 实现：

-- SQL - 8
with 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_date
from (
    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
) t
group by cont;

利用场景举例

如何按任意时段统计工夫区间数据？

场景形容

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

-- SQL - 9
with 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 - 10
select 
    date_item as day, 
    sum(repayment) as total_repayment
from (
    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
) t
where date_item >= '2022-01-15' and date_item <= '2022-01-16'
group by date_item
order by date_item;

排列组合

排列组合是针对离散数据罕用的数据组织办法，理论利用场景中又以组合更为常见，本节将别离介绍排列、组合的实现办法，并联合实例着重介绍通过组合对数据的解决。

双马饮泉

排列

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

-- SQL - 11
select 
    concat(val1, val2) as perm
from (select split('A,B,C', ',') as characters) dummy
lateral view explode(characters) t1 as val1
lateral view explode(characters) t2 as val2;

整体实现比较简单。

组合

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

-- SQL - 12
select 
    concat(least(val1, val2), greatest(val1, val2)) as comb
from (select split('A,B,C', ',') as characters) dummy
lateral view explode(characters) t1 as val1
lateral view explode(characters) t2 as val2
group by least(val1, val2), greatest(val1, val2);

整体实现比较简单。

利用场景举例

如何比照统计所有组合？

场景形容

现有用户拜访日志表 visit_log，该表定义见 疾速制作测试数据 。
如何按省份两两建设比照组，按比照组展现省份的用户访问量？

解决思路

外围思路是从所有省份列表中不反复地取出 2 个省份，生成所有的组合后果，而后关联 visit_log 表分组统计后果。

SQL 实现

-- SQL - 13
select
    combs.province_comb,
    log.province,
    count(1) as visit_count
from visit_log log
join ( -- 所有比照组
    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 = 1
where log.province in (combs.province_1, combs.province_2)
group by combs.province_comb, log.province
order by combs.province_comb, log.province;

间断

本节次要介绍连续性问题，重点形容了间断沉闷场景。对于动态类型的间断沉闷、动静类型的间断沉闷，别离论述了不同的实现计划。
本节内容间接贴近具体的利用，大部分篇幅以 SQL 内容为主。

动态间断沉闷场景统计

场景形容

现有用户拜访日志表 visit_log，该表定义见 疾速制作测试数据 。
如何获取间断登录大于或等于 2 天的用户？

上述问题在剖析连续性时，获取连续性的后果以超过固定阈值为准，可归类为 间断沉闷大于 N 天的动态间断沉闷场景统计。

SQL 实现

基于相邻日期差实现（lag / lead 版）

-- SQL - 14
select user_id
from (
    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
) t1
where datediff(dt, lag_dt) + 1 = 2
group by user_id;

整体实现比较简单。

基于相邻日期差实现（排序版）

-- SQL - 15
select user_id
from (
    select *, 
        dense_rank() over(partition by user_id order by dt) as dr
    from visit_log
) t1
where datediff(dt, date_add(dt, 1 - dr)) + 1 = 2
group by user_id;

整体实现比较简单。

基于间断沉闷天数实现

-- SQL - 16
select user_id
from (
    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
) t2
where cont_days >= 2
group by user_id;

能够视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如间断沉闷天数。

基于间断沉闷区间实现

-- SQL - 17
select user_id
from (
    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
) t2
where datediff(cont_date_end, cont_date_start) + 1 >= 2
group by user_id;

能够视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如间断沉闷区间。

动静间断沉闷场景统计

场景形容

现有用户拜访日志表 visit_log，该表定义见 疾速制作测试数据 。
如何获取最长的 2 个间断沉闷，输入用户、最长间断沉闷天数、最长间断沉闷日期区间？

上述问题在剖析连续性时，获取连续性的后果不是且无奈与固定的阈值作比拟，而是各自以最长间断沉闷作为动静阈值，可归类为 动静间断沉闷场景统计。

SQL 实现

基于 动态间断沉闷场景统计 的思路进行扩大即可，此处间接给出最终 SQL：

-- SQL - 18
select
    user_id, 
    -- 最长间断沉闷天数
    datediff(max(dt), min(dt)) + 1 as cont_days,
    -- 最长间断沉闷日期区间
    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
order by cont_days desc
limit 2;

结语

通过灵便的、散发性的数据处理思维，就能够用最简略的语法，解决简单的数据场景 是本篇文章贯通全文的思维。文中针对数列生成、区间变换、排列组合、间断判断等常见的场景，给出了绝对通用的解决方案，并联合实例进行了理论使用的解说。

本篇文章尝试独辟蹊径，强调灵便的数据处理思维，心愿能让读者感觉眼前一亮，更心愿真的能给读者产生帮忙。同时毕竟集体能力无限，思路不肯定是最优的，甚至可能呈现谬误，欢送提出意见或倡议。为了便于交换探讨，文中的每个 SQL 都标记了编号，能够间接在评论区 @SQL 编号 沟通。