【Hive】笔试题 01（自连接与窗口函数分别实现）

现有这么一批数据，现要求出：  
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数  
  
三个字段的意思：  
用户名，月份，访问次数  
  
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,11  
  
最后结果展示:  
  
用户  月份      最大访问次数  总访问次数       当月访问次数  
A   2015-01     33          33        33  
A   2015-02     33          43        10  
A   2015-03     38          81        38  
B   2015-01     30          30        30  
B   2015-02     30          45        15  
B   2015-03     44          89        44  

准备数据：
 
data1.txt
 
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,11
 
建表：
create database if not exists mydemo;
use mydemo;
drop table if exists infos;
create table if not exists infos(name string, date string, ftime int) 
row format delimited fields terminated by ",";
load data local inpath "/opt/soft/data1.txt" into table infos;
select * from infos;

第二步：
 
为了统计出以下这样的数据：
用户  月份      最大访问次数  总访问次数       当月访问次数  
A   2015-01     33          33        33  
A   2015-02     33          43        10  
A   2015-03     38          81        38  
B   2015-01     30          30        30  
B   2015-02     30          45        15  
B   2015-03     44          89        44  
 
第三步：
第一次遇到这样的查询难免有些手忙脚乱，我们可以将这道题拆分为三张表，进行分析 🤳

先开始第一张简单的表，一起来看看吧 😁

编写SQL ：
 
create table infos_step1 as select name,date,sum(ftime) as visit from infos group by name,date;

select * from infos_step1;

第四步：
create table infos_step2 as 
select 
a.name as namea,
a.date as datea,
a.visit as visita,
b.name as nameb,
b.date as dateb,
b.visit as visitb
from infos_step1 a join infos_step1 b
on a.name = b.name;

create table infos_step3 as 
select nameb, dateb, visitb, 
max(visita) as max_visit, 
sum(visita) as sum_visit
from infos_step2 
where datea <= dateb 
group by nameb, dateb, visitb;

select * from infos_step3

另外几种写法：

select
name,
date,
sum(ftime) as month_count,
max(sum(ftime)) over (partition by name order by date) as max_month,
sum(sum(ftime)) over (partition by name order by date) as total
from infos
group by name, date
order by name, date;

+-------+----------+--------------+------------+--------+--+
| name  |   date   | month_count  | max_month  | total  |
+-------+----------+--------------+------------+--------+--+
| 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  | 44           | 44         | 89     |
+-------+----------+--------------+------------+--------+--+