hive场景题

1、了解哪些窗口函数，都是什么功能？找一个在某个业务中的应用？ 手写窗口函数及功能意义，同时随便写一个带窗口函数的sql，并说明其sql的含义。

窗口函数 over() 又名开窗函数，属于分析函数的一种。

sum(col) over() : 分组对col累计求和
count(col) over() : 分组对col累计
min(col) over() : 分组对col求最小
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值
first_value(col) over() : 某分区排序后的第一个col值
last_value(col) over() : 某分区排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为 NULL 时候， 取默认值，如不指定，则为 NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为 NULL 时候， 取默认值，如不指定，则为 NULL
ntile(n) : 用于将分组数据按照顺序切分成n片，返回当前切片值。注意：n必须为int类型。

排名函数：
row_number() over() : 排名函数，不会重复，适合于生成主键或者不并列排名
rank() over() : 排名函数，有并列名次，名次不连续。如:1,1,3 
dense_rank() over() : 排名函数，有并列名次，名次连续。如：1，1，2

over() 函数的用法
distribute by + sort by 组合
位置：在over函数的小阔号
写法：可以单独使用，也可以一起组合使用
    如：
    over(distribute by colName)
    over(sort by colName)
    over(distribute by colName sort by colName [asc|desc])
作用：
   distribute by colName：用于指定分组字段，表示按照指定字段分组，那么每一组对应一个窗口,如果没有，则表示整张表为一组
   sort by colName: 用于排序，如果没有distribute by组合，表示整张表为一组，进行排序，如果有则组内进行排序

partition by +order by 组合
位置：还是在over小括号里
写法：可以单独使用，也可以一起组合使用
	如：
    over(partition by colName)
    over(order by colName)
    over(partition by colName order by colName [asc|desc])
作用：与 distribute by + sort by 组合效果一模一样。  

over(分组 排序 窗口)中的 order by 后使用 window 子句
作用：window子句用来更细粒度的管理窗口大小的

current row： 当前行
preceding:  向前
following:  向后
unbounded preceding: 从起点
unbounded following: 到终点

例如:
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,-- 按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,-- 与sample3一样，由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, -- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING  ) as sample6,-- 当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行     
from t_order;

2、求出每个栏目的被观看次数及累计观看时长？

数据: video表

uid channel min 
1 1 23
2 1 12
3 1 12
4 1 32
5 1 342
6 2 13
7 2 34
8 2 13
9 2 134

drop table video;
create table video( 
uid int, 
channel string, 
min int 
)
row format delimited 
fields terminated by ' ' 
;
load data local inpath './hivedata/video.txt' into table video;

答案：

select channel,sum(min) from video group by channel;

3、编写sql实现

数据：

userid,month,visits 
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1

drop table visits;
create table visits(
userid string, 
month string, 
visits int 
)
row format delimited 
fields terminated by ',' 
;
load data local inpath './hivedata/visits.txt' overwrite into table visits; 

完成需求：每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，结果数据格式如下:

+---------+----------+---------+-------------+---------------+--+
| userid  |  month   | visits  | max_visits  | total_visits  |
+---------+----------+---------+-------------+---------------+--+
| A       | 2015-01  | 33      | 33          | 33            |
| A       | 2015-02  | 10      | 33          | 43            |
| A       | 2015-03  | 38      | 38          | 81            |
| B       | 2015-01  | 30      | 30          | 30            |
| B       | 2015-02  | 15      | 30          | 45            |
| B       | 2015-03  | 34      | 34          | 79            |
+---------+----------+---------+-------------+---------------+--+

select t.userid,t.month,t.visits,
max(t.visits) over(distribute by t.userid sort by t.month asc) as max_visits,
sum(t.visits) over(distribute by t.userid sort by t.month asc) as total_visits 
from 
(select userid,month,sum(visits) as visits from visits group by userid,month) t;

4、编写连续7天登录的总人数：

数据: t1表

Uid dt login_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 
2 2019-07-19 1 
2 2019-07-20 0 
2 2019-07-21 1 
2 2019-07-22 0 
2 2019-07-23 1 
2 2019-07-24 0 
3 2019-07-11 1 
3 2019-07-12 1 
3 2019-07-13 1 
3 2019-07-14 1 
3 2019-07-15 1 
3 2019-07-16 1 
3 2019-07-17 1 
3 2019-07-18 1 

drop table login;
create table login( 
Uid int, 
dt string, 
login_status int 
)
row format delimited 
fields terminated by ' ' 
;

load data local inpath './hivedata/login.txt' into table login; 

-- 1) 用窗口函数，根据uid分组，日期排序
select uid,dt,row_number() over(distribute by uid sort by dt) from login where login_status=1;

-- 2) 在第一步基础上使用 date_sub日期函数计算出每个日期排名前的日期
select t1.uid,date_sub(t1.dt,t1.num) dt from
(select uid,dt,row_number() over(distribute by uid sort by dt) num
from login where login_status=1) t1;

-- 3) 在第二步的基础上，根据用户id和日期分组，计算日期数目大于7的用户
select uid,dt from
(select t1.uid,date_sub(t1.dt,t1.num) dt from
(select uid,dt,row_number() over(distribute by uid sort by dt) num
from login where login_status=1) t1) t2
group by uid,dt
having count(uid)>7;

5、你知道的排名函数有哪些？说一说它们之间的区别？ 文字说明即可

1. row_number()  分数有相同，名次是连续不重复的
    分数   名次
    100     1
    99      2
    99      3
    98      4
    
2. rank()         分数相同，名次有重复，是间断的
    分数   名次
    100     1
    99      2
    99      2
    98      4
    
3. dense_rank()    分数有相同，名次重复不间断
    分数   名次
    100     1
    99      2
    99      2
    98      3
    
这三个排名函数，不能单独使用，也必须配合over函数一起使用。

6、编写sql语句实现每班前三名，分数一样不并列，同时求出前三名按名次排序的一次的分差：

数据： stu表

Stu_no 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

drop table stu;
create table stu( 
Stu_no int, 
class string, 
score int 
)
row format delimited 
fields terminated by '\t' 
;

load data local inpath './hivedata/stu.txt' into table stu; 

编写sql实现，结果如下：

+--------+---------+--------+-----+----------+--+
| class  | stu_no  | score  | rn  | rn_diff  |
+--------+---------+--------+-----+----------+--+
| 1901   | 2       | 90     | 1   | 90       |
| 1901   | 1       | 90     | 2   | 0        |
| 1901   | 3       | 83     | 3   | -7       |
| 1902   | 7       | 99     | 1   | 99       |
| 1902   | 9       | 87     | 2   | -12      |
| 1902   | 8       | 67     | 3   | -20      |
+--------+---------+--------+-----+----------+--+

-- 1) 先查score排序排名后的表
select class,stu_no,score,row_number() over(distribute by class sort by score desc) rn from stu；

-- 2) 基于 1 查出的虚拟表用 where 条件查出前3名 
select * from
(select class,stu_no,score,row_number() over(distribute by class sort by score desc) rn from stu) t where t.rn<=3；

-- 3）使用lag查找前一行记录
-- lag(列名,n,m):  当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null
select t.*,t.score-nvl(lag(score) over(distribute by class sort by rn),0) rn_diff from
(select class,stu_no,score,row_number() over(distribute by class sort by score desc) rn 
from stu) t where t.rn<=3;

7、对于行列互换，你有哪些解决方式，详细说明每一种方式？ 使用语言描述即可

行转列： 
1、使用 case when 查询出多列即可，即可增加列。 


列转行: 
1、lateral view explode()，使用展开函数可以将1列转成多行，被转换列适用于array、map等类型。 

posexplode 相比在 explode 之上，将一列数据转为多行之后，还会输出数据的下标。

lateral view posexplode(数组)，如有排序需求，则需要索引。将数组展开成两行(索引