Hive Sql题

第一题：

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

 ​

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

 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,count(*) count,sum(min) total 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 userid,month,
 visits,
 max(visits) over(distribute by userid sort by month) max_visits,
 sum(visits) over(distribute by userid sort by month) total_visits
 from (
 select userid,month,
 sum(visits) 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; 

 select count(*)
 from
 (
 select distinct uid
 from(
 select uid,dt,lag(dt,6) over(partition by uid order by dt) pre_dt,
 sum(login_status) over(partition by uid order by dt rows between 6 preceding and current row) total
 from login
 ) t
 where date_sub(dt,6)=pre_dt and t.total=7) t1;

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

 ​

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      |
 +--------+---------+--------+-----+----------+--+

 select class,stu_no,score,rn,rn_diff
 from(
 select class,stu_no,score,
 row_number() over(partition by class order by score desc) rn,
 score-nvl(lag(score,1) over(partition by class order by score desc),0) rn_diff
 from stu
 ) t
 where t.rn<4;

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

 ​

8、编写sql实现行列互换。数据如下：

 id sid subject int
 1,001,语文,90
 2,001,数学,92
 3,001,英语,80
 4,002,语文,88
 5,002,数学,90
 6,002,英语,75.5
 7,003,语文,70
 8,003,数学,85
 9,003,英语,90
 10,003,政治,82

编写sql实现，得到结果如下：

 +---------+--------+--------+--------+--------+-----------+--+
 | sid     | 语文   | u2.数学  | u2.英语  | u2.政治  | u2.total  |
 +---------+--------+--------+--------+--------+-----------+--+
 | 001     | 90.0   | 92.0   | 80.0   | 0.0    | 262.0     |
 | 002     | 88.0   | 90.0   | 75.5   | 0.0    | 253.5     |
 | 003     | 70.0   | 85.0   | 90.0   | 82.0   | 327.0     |
 | total   | 248.0  | 267.0  | 245.5  | 82.0   | 842.5     |
 +---------+--------+--------+--------+--------+-----------+--+

 drop table score;
 create table score( 
 id int, 
 sid string, 
 subject string, 
 score double
 )
 row format delimited 
 fields terminated by ',' 
 ;
 ​
 load data local inpath './hivedata/score.txt' into table score; 

 select 
 sid, 
 sum(if(subject="语文",score,0)) as `语文`, 
 sum(if(subject="数学",score,0)) as `数学`, 
 sum(case when subject="英语" then score else 0 end) as `英语`, 
 sum(case when subject=