hsql场景题刷题

start-all.sh
hive --service metastore &
hive --service hiveserver2 &
beeline -u 'jdbc:hive2://qianfeng01:10000'

上面是开启hive，打开的是beeline模式。好久没用过，我都忘记了。

1

就是涉及到聚合函数的内容，就把聚合函数这边作为虚表的子查询，虚表千万别少表别名！

group的双维度，就是在子查询里面先group。

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,max_visits,visits,total_visits from (select userid,month,visits,max(visits) as max_visits,sum(visits) as