Hive练习题之用户访问次数

有如下的用户访问数据 tb_visit

userId	visitDate	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

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

用户id	   月份	   小计	累计
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

第一步：先处理日期，将其转换成月份，以便后面的聚合

SELECT
 userId,
 visitCount,
 DATE_FORMAT(visitDate,"yyyy-MM") visitMth
FROM
 tb_visit

+---------+-------------+-----------+
| userId  | visitCount  | visitMth  |
+---------+-------------+-----------+
| u01     | 5           | 2017-01   |
| u02     | 6           | 2017-01   |
| u03     | 8           | 2017-01   |
| u04     | 3           | 2017-01   |
| u01     | 6           | 2017-01   |
| u01     | 8           | 2017-02   |
| u02     | 6           | 2017-01   |
| u01     | 4           | 2017-02   |
+---------+-------------+-----------+

第二步：按userID和visitMth分组，求总金额

SELECT
 userId,
 visitMth,
 SUM(visitCount) visitCount
FROM
 (SELECT
  userId,
  visitCount,
  DATE_FORMAT(visitDate,"yyyy-MM") visitMth
 FROM
  tb_visit) t1
GROUP BY userId,visitMth

+---------+-----------+-------------+
| userId  | visitMth  | visitCount  |
+---------+-----------+-------------+
| u04     | 2017-01   | 3           |
| u01     | 2017-01   | 11          |
| u03     | 2017-01   | 8           |
| u01     | 2017-02   | 12          |
| u02     | 2017-01   | 12          |
+---------+-----------+-------------+

第三步： 使用窗口函数，sum() over()

SELECT
 userId,
 visitMth,
 visitCount,--小计
 SUM(visitCount) OVER(PARTITION BY userId ORDER BY visitMth) totalCount --总计 默认的是起始行到当前行，分区范围是按userId分区，并按照日期进行排序
FROM
 (SELECT
  userId,
  visitMth,
  SUM(visitCount) visitCount
 FROM
  (SELECT
   userId,
   visitCount,
   DATE_FORMAT(visitDate,"yyyy-MM") visitMth
  FROM
   tb_visit) t1
 GROUP BY userId,visitMth) t2
ORDER BY userId;--最后按userId排序

+---------+-----------+-------------+-------------+
| userId  | visitMth  | visitCount  | totalCount  |
+---------+-----------+-------------+-------------+
| 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           |
+---------+-----------+-------------+-------------+