一道Hive面试题：累积报表

来源：互联网发布：tim 知乎编辑：程序博客网时间：2024/06/06 06:42

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

userId visitDate visitCount u01 2017-01-21 5 u02 2017-01-23 6 u03 2017-01-22 8 u04 2017-01-20 3 u01 2017-01-23 6 u01 2017-02-21 8 u02 2017-01-23 6 u01 2017-02-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

分析

首先可以统计出每个用户每月访问次数的结果，如下表：

用户id 月份小计 u01 2017-01 11 u01 2017-02 12 u02 2017-01 12 u03 2017-01 8 u04 2017-01 3

然后以userId相等为条件进行自连接，可以有类似下面的结果：

u01     2017-01 11      u01     2017-01 11u01     2017-01 11      u01     2017-02 12u01     2017-02 12      u01     2017-01 11u01     2017-02 12      u01     2017-02 12

以左边的userId,vMonth,vCount进行分组，然后求最右边一列的和就可以了，当然要加一个现在条件，那就是右边的vMonth要小于等于左边的vMonth。

SQL命令

第一步，创建月份访问统计表：

create table t_vlog_month asselect userId,substr(vDate,1,7) as vMonth, sum(vCount) as mCountfrom t_vloggroup by userId,substr(vDate,1,7);

第二步，累积相加：

select t1.userId,t1.vMonth,max(t1.mCount),sum(t2.mCount) as aCountfrom t_vlog_month as t1 inner join t_vlog_month as t2 on (t1.userId=t2.userId)where t2.vMonth <= t1.vMonthgroup by t1.userId,t1.vMonth;

0 0