实现开窗count distinct 历史累计截止日期去重统计

村口蹲点的阿三

已于 2024-10-16 09:45:48 修改

阅读量9.6k

点赞数 21

分类专栏： hive-sql语法文章标签： hive hadoop 数据仓库 sql etl

于 2022-02-24 18:32:20 首次发布

本文链接：https://blog.csdn.net/liuya19921123/article/details/123118027

版权

hive-sql语法专栏收录该内容

16 篇文章

订阅专栏

在Hive中，当面临需要计算历史累计去重计数的场景，例如会员历史累计消费人数，传统的`count(distinct)`开窗函数无法直接实现。本文介绍了两种解决方案：一是通过`sum`函数结合排序来统计每日新增并累计；二是利用`collect_set`配合`size()`函数。这两种方法可以有效应对实际统计需求，并详细解析了Hive的窗口函数`OVER(PARTITION BY)`的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

解决问题：hive中count(distinct ) over() 无法使用场景

累计去除统计，实际经常使用到的场景比如会员每日历史累计消费，项目每日累计营收等。

案例：

数据准备：

用户轨迹用户访问日志表 test_visit_tab

cookieid(用户id) uvdate(访问时间) pagename(浏览页面) pv(访问次数)
cookie1 2022-02-01  A_page  1
cookie1 2022-02-01  B_page  2
cookie1 2022-02-02  A_page  1
cookie1 2022-02-02  B_page  3
cookie2 2022-02-01  A_page  1
cookie2 2022-02-01  B_page  5
cookie2 2022-02-03  A_page  2
cookie3 2022-02-02  A_page  6
cookie3 2022-02-02  B_page  3
cookie3 2022-02-03  A_page  1
cookie3 2022-02-03  B_page  2
cookie3 2022-02-04  A_page  1
cookie3 2022-02-04  B_page  5
cookie4 2022-02-05  A_page  1
cookie4 2022-02-05  B_page  4

需要统计的效果按照uvdate时间统计截止当前访问人数，统计效果

uvdate uv(当天uv) add_uv(截止当天uv)
2022-02-01  2   2
2022-02-02  2   3
2022-02-03  2   3
2022-02-04  1   3
2022-02-05  1   4

开窗count(distinct )over(order by)尝试

select uvdate
,count(distinct cookieid) as uv
-- order by 开窗截止数据
,count(distinct cookieid)over(order by uvdate) as add_uv
from test_visit_tab
group by uvdate

最终出来的结果

uvdate uv add_uv
2022-02-01  2   2
2022-02-02  2   2
2022-02-03  2   2
2022-02-04  1   1
2022-02-05  1   1

方法一：

发现虽然语法可以通过查询，但是结果其实和coount(distinct cookieid)不开窗的效果一致，换个思路使用sum开窗处理

思路：

1、将用户按照日期排序

2、统计每日新增用户（排序为1的值求和）

3、计算每日累计新增用户求和

实现：

select uvdate
,uv
,sum(add_uv_num)over(order by uvdate) as add_uv
from (
    select uvdate
    ,count(distinct cookieid) as uv
    -- 每日只统计新增用户（）
    ,sum(case when rankid = 1 then 1 else 0 end) as add_uv_num
    from (
        select uvdate,cookieid
        -- 将用户访问按时间排序
        ,row_number()over(partition by cookieid order by uvdate asc) as rankid
        from test_visit_tab
        group by uvdate,cookieid
    ) as tab_1
    group by uvdate
) as tab_2

最终结果：

uvdate uv add_uv
2022-02-01  2   2
2022-02-02  2   3
2022-02-03  2   3
2022-02-04  1   3
2022-02-05  1   4

方法二：

使用collect_set聚合函数后再size()求长度，实现去重统计的效果，弊端collect_set开窗后无法进行去重操作（后面可以再查看group内容）

实现：

select distinct uvdate,add_uv
from (
    select uvdate
    ,size(collect_set(cookieid)over(order by uvdate)) as add_uv
    from test_visit_tab
) as tt

最终结果：

uvdate add_uv
2022-02-01  2
2022-02-02  3
2022-02-03  3
2022-02-04  3
2022-02-05  4

阐述上述两种方法解决count distinct无法统计问题和解决部分实际场景统计方式，开窗具体语法可以查看。

hive 开窗函数OVER(PARTITION)详解（一）_liuya19921123的博客-CSDN博客_hive的over partition什么是窗口函数？窗口函数（Window Function）是 SQL2003 标准中定义的一项新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数，它为每行数据进行一次计算：输入多行（一个窗口）、返回一个值。在报表等分析型查询中，窗口函数能优雅地表达某些需求，发挥不可替代的作用。窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是OVER关键字。语法定义如下：window_function (expres...https://blog.csdn.net/liuya19921123/article/details/120430584 hive 开窗函数OVER(PARTITION)详解（二）_liuya19921123的博客-CSDN博客_hive over 开窗函数窗口函数有哪些？窗口函数可以分为以下 3 类：聚合（Aggregate）：AVG(),COUNT(),MIN(),MAX(),SUM()...sum(col) over() : 分组对col累计求和 count(col) over() : 分组对col累计计数 min(col) over() : 分组对col求最小 max(col) over() : 分组求col的最大值 avg(col) over() : 分组求col列的平均值取值（Value）：FIRST_VALUE(),LAS...https://blog.csdn.net/liuya19921123/article/details/120511937