Hive窗口函数

Hive行转列

一、需求分析

数据：
1,PK,RD,1
2,XIAOAI,RD,1
3,XIAOHONG,RD,2
4,XIAOZHANG,QA,1
5,XIAOLI,QA,2
6,XIAOFANG,QA,2

按照部门和性别分组：
QA,1    XIAOZHANG
QA,2    XIAOLI|XIAOFANG
RD,1    PK|XIAOAI
RD,2    XIAOHONG

二、所需函数

concat:拼接字符串
concat_ws:拼接字符串并指定分隔符
collect_list:
collect_set:

三、代码实现

hive>create table emp_info(
id string,
    name string,
    dept string,
    sex string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

hive>load data local inpath '/usr/local/src/datas/emp_info.txt' into table emp_info;

select name,concat_ws(",",dept,sex) as dept_sex from emp_info;

select 
t.dept_sex,concat_ws("|",collect_set(t.name))
from
(select name,concat_ws(",",dept,sex) as dept_sex from emp_info) t
group by
t.dept_sex;

Hive列转行

一、需求分析

Saddam  MapReduce,Hive,Spark,Fink
XIAOAI  Hadoop,Hbase,Kafka

Saddam	MapReduce
Saddam	Hive

二、所需函数

split:切割
explode:拆

三、代码实现

hive>create table emp_info2(
name string,
course string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

hive>load data local inpath '/usr/local/src/datas/emp_info2.txt' into table emp_info2;

hive>select name,split(course,',') from emp_info2;
Saddam  ["MapReduce","Hive","Spark","Fink"]
XIAOAI  ["Hadoop","Hbase","Kafka"]
-------------------------------------------------------------------------
hive>select 
name,courses
from
emp_info2
lateral view
explode(split(course,",")) courses_tmp as courses;
Saddam  MapReduce
Saddam  Hive
Saddam  Spark
Saddam  Fink
XIAOAI  Hadoop
XIAOAI  Hbase
XIAOAI  Kafka

窗口函数

一、累计问题之需求分析

域名       访问时间   访问量
imooc.com,2024-01-02,5
imooc.com,2024-01-03,15
google.com,2024-01-03,5
imooc.com,2024-01-04,8
google.com,2024-01-02,25
imooc.com,2024-01-05,5
imooc.com,2024-02-01,4
imooc.com,2024-02-02,6
google.com,2024-02-01,10
google.com,2024-02-01,10
imooc.com,2024-03-05,9
imooc.com,2024-03-06,5
google.com,2024-03-01,11
google.com,2024-03-02,10

需求：每个域名截止到每月为止pv之和  最大单月访问次数  累计到该月的总访问次数

每？？：group by ??

二、所需函数

data_format

三、代码实现

hive>create table access(
domain string,
    day string,
    pv int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

hive>load data local inpath'/usr/local/src/datas/chuangkoufunction.txt'into table access;

1.根据day字段拿出月份
hive>select domain,date_format(day,'yyyy-MM')AS month from access;

imooc.com       2024-01
imooc.com       2024-01
google.com      2024-01
imooc.com       2024-01
google.com      2024-01
imooc.com       2024-01
imooc.com       2024-02
imooc.com       2024-02
google.com      2024-02
google.com      2024-02
imooc.com       2024-03
imooc.com       2024-03
google.com      2024-03
google.com      2024-03

2.截止到每月为止pv之和
hive>select domain,date_format(day,'yyyy-MM')AS month,sum(pv)as pv
from access
group by domain, date_format(day,'yyyy-MM');

google.com      2024-01 30
google.com      2024-02 20
google.com      2024-03 21
imooc.com       2024-01 33
imooc.com       2024-02 10
imooc.com       2024-03 14

--创建一个临时表a_tmp,将以上结果存入
hive>create table a_tmp as select domain,date_format(day,'yyyy-MM')AS month,sum(pv)as pv
from access
group by domain, date_format(day,'yyyy-MM');
 
3.最大单月访问次数

方式一：自连接：自己join自己
hive>create table b_tmp as 
select
a.domain a_domain,a.month a_month,a.pv a_pv,
b.domain b_domain,b.month b_month,b.pv b_pv
from
a_tmp a join a_tmp b on a.domain=b.domain;

google.com      2024-01 30      google.com      2024-01 30
google.com      2024-02 20      google.com      2024-01 30
google.com      2024-03 21      google.com      2024-01 30
google.com      2024-01 30      google.com      2024-02 20
google.com      2024-02 20      google.com      2024-02 20
google.com      2024-03 21      google.com      2024-02 20
google.com      2024-01 30      google.com      2024-03 21
google.com      2024-02 20      google.com      2024-03 21
google.com      2024-03 21      google.com      2024-03 21
imooc.com       2024-01 33      imooc.com       2024-01 33
imooc.com       2024-02 10      imooc.com       2024-01 33
imooc.com       2024-03 14      imooc.com       2024-01 33
imooc.com       2024-01 33      imooc.com       2024-02 10
imooc.com       2024-02 10      imooc.com       2024-02 10
imooc.com       2024-03 14      imooc.com       2024-02 10
imooc.com       2024-01 33      imooc.com       2024-03 14
imooc.com       2024-02 10      imooc.com       2024-03 14
imooc.com       2024-03 14      imooc.com       2024-03 14