Hive常见的计算(连续纸，topN，行专列，列转行)

目录

1. 连续值

第1步：先按照条件开窗分组排序，得到分组排序的结果。排序字段值 减去 排序序列号

第2步：按照用户id 和 差值gid 分组。聚合得到一个总行数count，再按照连续值去过滤总行数count。

2. topN

第1步：开窗分组排序

第2步：求相邻排名间的分差，

3. 行转列

 翻转转换

step a. 按照用户分组

step b. 使用case when 新增字段，并赋值。

分组聚拢

step a. 按照用户分组

step b. 使用concat_ws等函数进行聚拢操作。

4. 列转行

5.  json字符串，字符串组的解析

---

1. 连续值

例如连续7天登录的用户，连续3天消费的用户等。

这主要是要有转换的思想。

将连续性转换为 ==》 差值相同

将连续值转换为 ==》 按照差值分组聚合的行数

step A- 需要开窗，在窗口内进行分组排序，上排序序列号gid

step B- 使用当前的排序字段值a 减去 排序序列号gid ( a - gid) ，得到新的一列。

step C- 再将连续值转换为判断差值大小，按照用户和差值分组。差值相同说明日期是连续的。根据求的连续值，去过滤步骤2得到的值，得到最终的结果集。

例如：求连续7天登录的用户。

数据： 第1列为用户ID， 第2列为登录日期，第3列为登录结果(1为成功，0为失败，即没有登录)

-- user_id dt status(1 正常登录，0 异常)
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 0
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1

按照步骤，

第1步：先按照条件开窗分组排序，得到分组排序的结果。排序字段值 减去 排序序列号

这里求得是用户，即按照用户分组，连续7天，按照登录日期排序。日期字段值 - 排序序列号。这个差值如果相同，说明日期连续

-- 开窗排序， 开窗函数 over()
over(partiton by user_id order by dt)

-- 排名函数，给局部排序的上序列号，
-- rank()，排名可以重复，相同排名的后续会有空位。即 1,2,3,3,5
-- DENSE_RANK(), 排名可以重复，相同排名的后续不会有空位，1,2,3,3,4,5
-- 这里排名 顺序增加 且 排名不会重复。使用 row_number()

row_number() over(partiton by user_id order by dt)

-- 求排序字段的当前值 和 排序序列号 的差值。 这里是日期的求值，直接使用date_sub
date_sub(dt,row_number() over(partition by user_id order by dt))

-- 最终第一步的sql是

select user_id,
       dt,   
       date_sub(dt,row_number() over(partition by user_id order by dt)) gid
from user_login
where status = 1

第2步：按照用户id 和 差值gid 分组。聚合得到一个总行数count，再按照连续值去过滤总行数count。

-- 根据 当前字段值 和 排序序列号的差值，和用户分组。 最后过滤

with tmp as (
  select user_id,
         dt,
         date_sub(dt,row_number() over(partition by user_id order by dt)) gid
  from user_login
  where status =1
)

select user_id,
       count(*) as login_count -- 连续登录的天数
from tmp 
group by user_id,gid
having login_count >= 7 -- having是在聚合函数count()后面执行，where则是之前执行。所以这里用

2. topN

求前几名排名。

这个排名不是全局，全局只要直接order by 字段就好了。指的是条件范围内的topN，即局部的topN，可能是局部的时间，局部的部门等。

比如求每个班级的前面三名，分数一样的并列，同时求出名次间的分差(即相邻排名的分差)

前面3名 ==》 排名函数( row_number【值相同时排名也不会相同】，rank【值相同时，排名也相同，且后续的排名值会断开】， dense_rank【值相同时，排名也相同，且后续的排名值不断开，会连续】)

相邻名词的分差 ==》 上下行的差值  ==》 将一列整体向上或者向下移动一行

--student_id,class,score
1 1901 90
2 1901 90
3 1901 83
4 1901 60
5 1902 66
6 1902 23
7 1902 99
8 1902 67
9 1902 87

 期待的结果

class score rnk lagscore
1901 90 1 0
1901 90 1 0
1901 83 2 -7
1901 60 3 -23
1902 99 1 0
1902 87 2 -12
1902 67 3 -20

实现思路：

step a. 求得是每个班级的，分数排名 ==》 按照班级分组，分数排序，再上排名函数。

step b. 两个相邻排名间的分差 ==》 M(score) 减去 M-1(score) ==》 需要分数，需要排名号

step c. 分数相同，并列排名, 后续的排名不会空 ==》 排名函数用 dense_rank()

sql实现：

第1步：开窗分组排序

-- 第一步，得到分组排序的排名

-- over() 开窗函数，按照班级分组，分数排序
over(partition by class order by score desc)

-- 前面3名，降序排名，排名重复不为空
dense_rank() over(partition by class order by score desc)

-- 分组排序的排名

select student_id,
       class,
       score,
       dense_rank() over(partition by class order by score desc) as rnk  -- 降序排名
from student_table

第2步：求相邻排名间的分差，

相邻排名 ==》 相当于求连续值 ==》 对排名值字段进行开窗, 得到新的一列排名分数值

排名也是按照班级分组，分数排名 ==》 over(partition by class order by score desc)

相邻排名的分差 ==》 理解为把 一列的值整体移动，可以将减数那列上移一格，或者被减数那一列往下移动一格。 这里 采用 被减数下移一格

-- 第一步得到的分班排名
with tmp as (
    select sno, 
          class, 
          score,
          dense_rank() over (partition by class order by score desc) as rnk
    from student_table
)

-- 再次开窗，对班级分组，分数排名。 ==》 分组排名的分数

over(partition by class order by score desc)

-- 新增一行它的后续排名分数，他的值是 原来的排名分数列往下移动一行 ==》 lag() 函数往下移动一行

lag(score) over(partition by class order by score desc)

-- 求分差，空补0

nvl(score - lag(score) over(partition by class order by score desc),0)

-- 最终

select class,
       score,
       nvl(score - lag(score) over (partition by class order by score desc), 0) lagscore
from tmp
where rank<=3;  -- 前面3名

3. 行转列

 翻转转换

 原表名为 user_skill

-- 数据：user_id skill
1 java
1 hadoop
1 hive
1 hbase
2 java
2 hive
2 spark
2 flink
3 java
3 hadoop
3 hive
3 kafka

求每个用户的组件技能情况，即期待输出

id java hadoop hive hbase spark flink kafka
1   1     1      1    1     0     0     0
2   1     0      1    0     1     1     0
3   1     1      1    0     0     0     1

step a. 按照用户分组

step b. 使用case when 新增字段，并赋值。

select id,
  sum(case when course="java" then 1 else 0 end) as java,
  sum(case when course="hadoop" then 1 else 0 end) as hadoop,
  sum(case when course="hive" then 1 else 0 end) as hive,
  sum(case when course="hbase" then 1 else 0 end) as hbase,
  sum(case when course="spark" then 1 else 0 end) as spark,
  sum(case when course="flink" then 1 else 0 end) as flink,
  sum(case when course="kafka" then 1 else 0 end) as kafka
from user_skill
group by user_id;

分组聚拢

相同的列数，将多个行值聚拢成一个行值

原表名为 row2line

-- 数据。id1 id2 flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8

期待输出

-- 编写sql实现如下结果
id1 id2  flag
 a   b   2|1|3
 c   d   6|8

思路： id1，id2的值没变 ==> 按照id1，id2分组

           flag的值是组合值  ==》 将原来分组好的flag值，聚拢起来，在拼接到一起

step a. 按照用户分组

step b. 使用concat_ws等函数进行聚拢操作。

-- 分组聚拢
-- 分组group by
-- 聚拢可以使用 collect_set，collect_list。set会去重，这里的输出没有两个8，所以使用set

select id1,
  id2,
  collect_set(flag) flag
from row2line


-- 将 集合中的值，拼接起来 ==》 concat_ws("拼接符", string or array)   

select id1,id2,
       concat_ws("|",collect_set(cast (flag as string))) flag
from row2line

4. 列转行

将一行的值，拆分成多个行值  ==》  炸裂行数 explode() + lateral view

这个配合json的解析，会是实际工作中最常遇到的情况。

本例是上述的反转。即将

-- 编写sql实现如下结果
id1 id2  flag
 a   b   2|1|3
 c   d   6|8

转变为

-- 数据。id1 id2 flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8

explode()， udtf函数，它炸开字段时，不能与其他列在同一级

select id1,id2,explode(flag), 是不允许的。

lateral view： lateral view udtf[expression]  tableAlias as columnAlias

-- 主要是关注explode的用法，搭配 udtf函数
-- udtf函数 lateral view的用法

select id1,id2,newflag
from line2row
lateral view explode(split(flag,"\\|")) t1 as newflag

5.  json字符串，字符串组的解析