hive练习6

create database ketanlianxi;

use ketanlianxi;

创建表

create table action(
    userId string,
    visitDate string,
    visitCount int
)row format delimited fields terminated by '\t';

数据

u01	2017/1/21	5
u02	2017/1/23	6
u03	2017/1/22	8
u04	2017/1/20	3
u01	2017/1/23	6
u01	2017/2/21	8
u02	2017/1/23	6
u01	2017/2/22	4

select * from action;

+----------------+-------------------+--------------------+--+
| action.userid  | action.visitdate  | action.visitcount  |
+----------------+-------------------+--------------------+--+
| u01            | 2017/1/21         | 5                  |
| u02            | 2017/1/23         | 6                  |
| u03            | 2017/1/22         | 8                  |
| u04            | 2017/1/20         | 3                  |
| u01            | 2017/1/23         | 6                  |
| u01            | 2017/2/21         | 8                  |
| u02            | 2017/1/23         | 6                  |
| u01            | 2017/2/22         | 4                  |
+----------------+-------------------+--------------------+--+

select
    userId,
       date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mm,
       visitCount
from action;  t1

+---------+----------+-------------+--+
| userid  |    mm    | visitcount  |
+---------+----------+-------------+--+
| u01     | 2017-01  | 5           |
| u02     | 2017-01  | 6           |
| u03     | 2017-01  | 8           |
| u04     | 2017-01  | 3           |
| u01     | 2017-01  | 6           |
| u01     | 2017-02  | 8           |
| u02     | 2017-01  | 6           |
| u01     | 2017-02  | 4           |
+---------+----------+-------------+--+

select
       userid,
       mn,
       sum(visitCount) sum_visitCount
       from
(select
    userId,
       date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
       visitCount
from
           action) t1
group by userid, mn;  t2

+---------+----------+-----------------+--+
| userid  |    mn    | sum_visitcount  |
+---------+----------+-----------------+--+
| u01     | 2017-01  | 11              |
| u01     | 2017-02  | 12              |
| u02     | 2017-01  | 12              |
| u03     | 2017-01  | 8               |
| u04     | 2017-01  | 3               |
+---------+----------+-----------------+--+

select
       userId,
       mn,
       sum_visitCount,
       sum(sum_visitCount) over(partition by userId order by mn) as sum_visitCount
from
     (select
        userid,
        mn,
        sum(visitCount) sum_visitCount
        from
            (select
                userId,
                date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
                visitCount
    from
           action) t1
            group by userid, mn)t2; t3

+---------+----------+-----------------+-----------------+--+
| userid  |    mn    | sum_visitcount  | sum_visitcount  |
+---------+----------+-----------------+-----------------+--+
| u01     | 2017-01  | 11              | 11              |
| u01     | 2017-02  | 12              | 23              |
| u02     | 2017-01  | 12              | 12              |
| u03     | 2017-01  | 8               | 8               |
| u04     | 2017-01  | 3               | 3               |
+---------+----------+-----------------+-----------------+--+