hive不常但用且会考的用法_行列转换、排序、聚合_hive 行转列几种方法

一、行转列和列转行

Hive行转列用到的函数：

• concat(str1,str2,…) --字段或字符串拼接
• concat_ws(sep, str1,str2) --以分隔符拼接每个字符串
• collect_set(col) --将某字段的值进行去重汇总，产生数组array类型字段
• collect_list(col) --将某字段的值汇总（不去重），产生数组array类型字段

数据1_table1： 学生考试成绩表

数据2_table2： 学生考试成绩表

1、行转列

列转行 ( concat_ws + collect_list/set )
实现数据1–>数据2：

select name
     , concat_ws(",",collect_list(subject)) as subject
     , concat_ws(",",collect_list(score)) as score
  from table1
 group by name;
1
2
3
4
5

2、列转行

行转列（split + explode + lateral view）
实现数据2–>数据1：
一列中的数据由一行变成多行。这里需要使用分割和爆炸，并结合侧面视图实现
（1）、单列

select name,subject
  from table2
lateral view
explode(split(subject,',')) t as subject; 

--若给每个电影一个编号，假设编号就按名字的顺序，此时我们要用到另一个hive函数，称为posexplode，代码如下：
select seq+1,name,subject
  from movie2
lateral view
posexplode(split(subject,',')) t as seq,subject; 
1
2
3
4
5
6
7
8
9
10

（2）、多列

select name,subject,score
  from movie2
lateral view posexplode(split(subject,',')) sn as seq1,subject
lateral view posexplode(split(score,',')) sc as seq2,score
where seq1= seq2
'''
为什么不直接用explode函数？直接用每个学生会生成subject*score个行，和想要不符
lateral view explode(split(subject,',')) sn as seq1,subject
lateral view explode(split(score,',')) sc as seq2,score
'''
1
2
3
4
5
6
7
8
9
10

二、排序

• row_number() --根据顺序排序
• rank() --排序相同时会重复，总数不会变
• dense_rank() 排序相同时会重复，总数会减少
• percent_rank() 排序后占总体的百分比

1、row_number()

--ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)
select name
     , subject
     , score
     , row_number() over(partition by name order by score desc) as rn 
 from table1;
1
2
3
4
5
6

运行如下：

2、rank()

select name
     , subject
     , score
     , rank() over(partition by name order by score desc) as rn 
 from table1;
1
2
3
4
5

运行如下：

3、dense_rank()

select name
     , subject
     , score
     , dense_rank() over(partition by name order by score desc) as rn 
 from table1;
1
2
3
4
5

运行如下：

4、percent_rank()

select name
     , subject
     , score
     , percent_rank() over(partition by name order by score desc) as rn 
 from table1;
1
2
3
4
5

三、累积聚合函数

• sum()/avg()
• max()/min()
• cume_dist()

1、sum()/max()/min()

**数据3_table3：**用户登陆APP次数和充值金额表

统计每个用户截止到当前为止的当天最大登录次数和累计到当前的总访问次数

select name
     , dt
     , record
     , sum(record) over(partition by userid order by dt) as sum_record
     , max(record) over(partition by userid order by dt) as max_record
from table_3;
'''
还有滑动求和方法，滑动求和就需要用到over中的另一用法（窗口函数相关知识）：
sum(COLUMN1) over(partition by COLUMN2 order by COLUMN3 range between ... and ...)
使用range between and指定窗口的大小，向前使用preceding，向后使用following。如2 preceding and 0 following指定的窗口包括当前行、当前行前面两行，总共3行。
sum(count) over(partition by userid order by dt range between 2 preceding and 0 following)

窗口函数：
 - CURRENT ROW：当前行；
 - n PRECEDING：往前n行数据；
 - n FOLLOWING：往后n行数据；
 - UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点；
 - LAG(col,n,default_val)：往前第n行数据；例，同组前一行的值：lag(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式)
 - LEAD(col,n, default_val)：往后第n行数据；lead(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式)
 - NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。这个函数需要注意：n必须为int类型。

'''
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

运行如下：

2、cume_dist()

CUME_DIST 小于等于当前值的行数/分组内总行数

数据4_table4： 部门员工薪水表

-–比如，统计小于等于当前薪水的人数，所占总人数的比例
SELECT dept
     , userid
     , sal
     , CUME_DIST() OVER(ORDER BY sal) AS rn1 --不分组，直接按薪水计算占比
     , CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 --按部门分组，计算小于等于当前薪水员工占比
     --, cume_dist() over(order by sal desc)  as rn3 --按照sal降序排序后，结果就是统计大于等于当前薪水的人数的比例
FROM table4;

1
2
3
4
5
6
7
8
9

运行如下：

四、连续登陆和互粉问题

4.1、连续登录

例如table3，找到连续登陆3天的客户

--1、先把数据按照用户分组，根据登录日期排序
--2、用登录日期减去排序数字rn，得到的差值日期如果是相等的，则说明这两天肯定是连续的
--3、根据name和日期差date_diff 分组，最小登录日期即为此次连续登录的开始日期start_date，最大登录日期即为结束日期end_date，登录次数即为分组后的count(1)
select t2.name        
     , t2.date_diff
     , count(1) as times  --连续登陆天数
     , min(t2.dt) as start_date
     , max(t2.dt) as end_date
  from (select t1.name
             , t1.dt
             , date_sub(t1.dt,rn) as date_diff
          from (select name
                     , dt
                     , row_number() over(partition by name order by dt asc) as rn 
                  from table3 
                 ) t1
      ) t2
group by t2.name
    , t2.date_diff
having times >= 3    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.2、互粉

微博体系中互粉的有多少组
在微博粉丝表中，互相关注的人有多少组，例如：A–>B;B–>A；A和B互粉，称为一组。
表结构：id,keep_id,time… (id,keep_id可作为联合主键)
借助Hive进行实现

select count(*)/2 as weibo_relation_number
  from ((select concat(id,keep_id) as flag from weibo_relation)
         union all  --全部合并到一起，不能提前去重
        (select concat(keep_id,id) as flag from weibo_relation)
       ) as tmp
having count(flag) =2
group by flag;
1
2
3
4
5
6
7

五、其他

5.1、union和union all的区别**

**union：**对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
**union all ：**两个结果集进行并集操作，包括重复行，不进行排序；

5.2、drop、delete、truncate区别**

**drop：**删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除；（drop table 表名）
**truncate：**删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已；（truncate table 表名）
**delete：**与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete即可以对行数据进行删除，也可以对整表数据进行删除。（delete from 表名 （where 列名 = 值））

5.3、get_json_object(string json_string, string path)**

第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。
每次只能返回一个数据项。
举例：
data 为 test表中的字段，数据结构如下：

data =
{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "email":"amy@only_for_json_udf_test.net", 
 "owner":"amy" 
}
1
2
3
4
5
6
7
8
9
10

1）get单层值

hive> select  get_json_object(data, '$.owner') from test;
结果：amy
1
2

2）get多层值

hive> select  get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95
1
2

3）get数组值[]

hive> select  get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}
1
2

5.4、获取字段中的数字

regexp_replace(col_name,'([^0-9]+)','') as col_value   -- 获取字段中的数字
1