SQL进阶技巧：如何优雅求解指标累计去重问题？

会飞的一十六 2024-10-28

434

点击上方【蓝色】字体关注我们

01 场景描述

近期公司开发某项学习功能，改功能有很多学习内容（如java,C,python等方向），每天都会有众多学习用户学习某一项或者多项学习内容。产生数据如下表：

产生数据如下表：

日期          内容    学习用户
2022-01-01    java      u1
2022-01-02    java      u1
2022-01-02    java      u2
2022-01-01    C         u1
2022-01-01    C         u3
2022-01-01    Python    u4
2022-01-02    Python    u4
2022-01-02    Python    u5
2022-01-02    Python    u6

期望数据

现在想要计算截止每天每个学习内容的截止去重学习用户数，但是截止去重用户数小于等于1的要被过滤，期望数据如下：

日期          内容    去重截止学习用户数
2022-01-02    java    2
2022-01-01    C       2
2022-01-02    Python  3

截止到2022-01-01，学习内容java的为去重用户数为1，学习内容C的为去重用户数为2，学习内容Python的为去重用户数为1。所以2022-01-01学习内容为java和python的都要内过滤。

02 数据准备

create table study as (
-- 基础数据
    select '2022-01-01' as pdate, 'java' as icate, 'u1' as user_id
    union all
    select '2022-01-02' as pdate, 'java' as icate, 'u1' as user_id
    union all
    select '2022-01-02' as pdate, 'java' as icate, 'u2' as user_id
    union all
    select '2022-01-01' as pdate, 'C' as icate, 'u1' as user_id
    union all
    select '2022-01-01' as pdate, 'C' as icate, 'u3' as user_id
    union all
    select '2022-01-01' as pdate, 'Python' as icate, 'u4' as user_id
    union all
    select '2022-01-02' as pdate, 'Python' as icate, 'u4' as user_id
    union all
    select '2022-01-02' as pdate, 'Python' as icate, 'u5' as user_id
    union all
    select '2022-01-02' as pdate, 'Python' as icate, 'u6' as user_id
);

03 问题分析

方法1：重复值状态标记

由于要按照内容及学习用户去重，且实现的是按照时间累计去重，因此基本的思路就是按照内容及用户分组后在最早出现时间点出标记为1，其余标记为0，以此来实现累加计算。

第一步：按照用户及学习内容分组后，找出组内最早时间，并进行状态标记

select pdate
      ,icate
      ,user_id
      ,case when row_number() over(partition by icate,user_id order by pdate) = 1 then 1 else 0 end status
from study

第二步：按照日期和内容分组，对步骤1的状态进行汇总，并按照日期累计当前汇总后的状态值。具体SQL如下：

select pdate
     , icate
     , sum(sum(status)) over (partition by icate order by pdate) acc_user_cnt
from (select pdate
           , icate
           , user_id
           , case when row_number() over (partition by icate,user_id order by pdate) = 1 then 1 else 0 end status
      from study) t
group by pdate
       , icate

第三步：过滤掉累计值小于1的数据。最终SQL如下：

select pdate
     , icate
     , acc_user_cnt
from (select pdate
           , icate
           , sum(sum(status)) over (partition by icate order by pdate) acc_user_cnt
      from (select pdate
                 , icate
                 , user_id
                 , case when row_number() over (partition by icate,user_id order by pdate) = 1 then 1 else 0 end status
            from study) t
      group by pdate
             , icate) t
where acc_user_cnt > 1

方法2：利用自关联生成完成数据进行行比较求解

完整的SQL如下：

with tmp1 as (select distinct pdate, icate
              from study)
select a.pdate,
       a.icate,
       count(distinct a.user_id) as icount
from study a
         join
     tmp1 b
     on
         a.pdate <= b.pdate
             and
         a.icate = b.icate
group by a.pdate, a.icate
having icount > 1;