hql面试题

我们有如下的用户访问数据

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 t2.user_id `用户id`,
       t2.visitmonth `月份`,
       subtotal_visit_cnt `小计`,
       sum(subtotal_visit_cnt) over (partition BY user_id ORDER BY visitmonth) `累积`
FROM
  (SELECT lower(user_id) user_id,
          visitmonth,
          sum(visitcount) AS subtotal_visit_cnt
   FROM
     (SELECT user_id,
             date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,
             visitcount
      FROM bifang.user_visit_info) t1
   GROUP BY lower(user_id),
            visitmonth)t2
ORDER BY t2.user_id,
         t2.visitmonth