累计和配置单元:查找不包括重复项的运行总数

to94eoyn  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(443)

嗨,我手头有一个非常奇怪的问题,我找不到解决办法。我有一个包含以下列的表userviews:

  1. Progdate(String)
  2. UserName(String)

表中的虚拟数据:

  1. Progdate UserName
  2. 20161119 A
  3. 20161119 B
  4. 20161119 C
  5. 20161119 B
  6. 20161120 D
  7. 20161120 E
  8. 20161120 A
  9. 20161121 B
  10. 20161121 A
  11. 20161121 B
  12. 20161121 F
  13. 20161121 G

每次用户查看程序时,表中都有一个条目。例如,在11月19日,用户a观看了一次程序,因此只有一个条目。用户b看了两次这个节目,所以11月19日这个用户有两个条目,以此类推。

  1. Select Progdate, count(distinct UserName) UniqueUsersByDate
  2. from UserViews
  3. group by Progdate;

上面的查询将为我提供观看该节目的所有独特用户的日期统计

  1. Progdate UniqueUsersByDate
  2. 20161119 3
  3. 20161120 3
  4. 20161121 4

以下查询:

  1. Select Progdate, UniqueUsersByDate, Sum(UniqueUsersByDate) over(Order By Progdate) RunningTotalNewUsers
  2. from
  3. (
  4. Select Progdate, count(distinct UserName) UniqueUsersByDate
  5. from
  6. UserViews
  7. group by Progdate SORT BY Progdate
  8. ) UV;

结果如下:

  1. Progdate UniqueUsersByDate RunningTotalNewUsers
  2. 20161119 3 3
  3. 20161120 3 6
  4. 20161121 4 10

但是我想要的是所有第一次看这个节目的用户的总数。意味着如果用户a在20161119上观看了该节目,然后又在20161120上观看了该节目,则该用户的计数不应在20161120的运行总数中重复。因此,我想从上表得到的结果是:

  1. Progdate UniqueUsersByDate RunningTotalNewUsers
  2. 20161119 3 3
  3. 20161120 3 5
  4. 20161121 4 7

我只在hive hql中寻找解决方案。我们非常感谢对这个问题的任何意见。
谢谢。

bfrts1fy

bfrts1fy1#

  1. select Progdate
  2. ,UniqueUsersByDate
  3. ,sum(Users1stOcc) over
  4. (
  5. order by Progdate
  6. ) as RunningTotalNewUsers
  7. from (select Progdate
  8. ,count (distinct UserName) as UniqueUsersByDate
  9. ,count (case when rn = 1 then 1 end) as Users1stOcc
  10. from (select Progdate
  11. ,UserName
  12. ,row_number() over
  13. (
  14. partition by UserName
  15. order by Progdate
  16. ) as rn
  17. from UserViews
  18. ) uv
  19. group by Progdate
  20. ) uv
  21. ;
  1. +-------------+--------------------+-----------------------+
  2. | progdate | uniqueusersbydate | runningtotalnewusers |
  3. +-------------+--------------------+-----------------------+
  4. | 2016-11-19 | 3 | 3 |
  5. | 2016-11-20 | 3 | 5 |
  6. | 2016-11-21 | 4 | 7 |
  7. +-------------+--------------------+-----------------------+

附笔
理论上,聚合和sum分析函数的使用不需要额外的子查询,但是解析器似乎有问题(bug/feature)。
请注意,附加的子查询不一定表示附加的执行阶段,例如。 select * from (select * from (select * from (select * from (select * from t)t)t)t)t; 以及 select * from t 会有相同的执行计划。

展开查看全部

相关问题