SQL-开窗函数计算复购指标统计案例

村口蹲点的阿三

已于 2024-10-16 09:44:55 修改

阅读量1.4k

点赞数

分类专栏： sql数据处理文章标签： sql 数据库 database 大数据 big data

于 2022-06-20 17:59:02 首次发布

本文链接：https://blog.youkuaiyun.com/liuya19921123/article/details/125377717

版权

sql数据处理专栏收录该内容

4 篇文章

订阅专栏

本文探讨了在SQL中计算复购指标的重要性，包括复购金额、不同周期的复购统计，并强调了动态复购的挑战。通过使用lead()和sum() over(partition by order by range)等开窗函数，可以实现复购详情的精确计算。文中以用户消费记录为例，展示了如何统计3天、7天和30天内的会员复购金额，详细说明了计算过程和所需的数据序列化步骤。

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

无论是分析还是数仓，我们经常会碰到计算复购场景，复购计算过程中涉及到以下几点：

1、除用户是否归属复购外，复购指标统计，如金额、营收等

2、不同复购周期的计算，7天，30天，60天，90天等等

3、动态复购，用户每天起始往后的复购详情，又同归因

这里复购的统计变化比较多，主要涉及到两类的计算去重用户统计，复购指标统计。

如果在计算过程中不涉及到复购指标的统计，可以直接使用lead()开窗函数，在之前说的开窗函数里面讲过，具体的方法可以查下。

针对计算复购指标同样开窗，利用sum()over(partition by order by range )的函数，之前也介绍过，但是计算过程中需要构建一个日期序列。

举个基础的复购指标案例：

数据：用户消费记录：用户标识mid、消费日期cid、消费金额pnum

需求：统计用户3天、7天、30天会员复购金额

思路：将时间进行序列化后使用sum开窗的range进行求和处理

开窗函数具体的使用语法可以之前面的内容查找

数据表：

mid       cid     pnum
10086	2022-01-07	2.0
10086	2022-02-05	2.0
10086	2022-01-15	2.0
10086	2022-01-05	2.0
10086	2022-01-18	2.0
10086	2022-01-11	2.0
10086	2022-02-01	2.0
10086	2022-03-01	2.0
10086	2022-01-01	2.0
10086	2022-01-02	2.0
10086	2022-01-03	2.0

sql语句：

with test_table as(
    -- 创建一个用户消费记录临时表
select '10086' as mid,'2022-01-01' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-02' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-03' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-05' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-07' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-11' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-15' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-01-18' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-02-01' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-02-05' as cid,2.0 as pnum
union all 
select '10086' as mid,'2022-03-01' as cid,2.0 as pnum
)
select mid,cid,cid_row,pnum
-- 开窗求和 根据会员开窗，按照时间序列求和当前时间往后+2的序列内的值
,sum(pnum)over(partition by mid order by cid_row RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) as fg_pnum_3
,sum(pnum)over(partition by mid order by cid_row RANGE BETWEEN CURRENT ROW AND 6 FOLLOWING) as fg_pnum_7
,sum(pnum)over(partition by mid order by cid_row RANGE BETWEEN CURRENT ROW AND 29 FOLLOWING) as fg_pnum_30
FROM 
(select mid
    ,cid
    ,datediff(cid,'2022-01-01') as cid_row  -- 日期减去固定年月日创建一个序列
    ,pnum 
    FROM test_table
) as aa

数据结果：

mid   cid   cid_row   pnum   fg_pnum_3 fg_pnum_7 fg_pnum_30
10086	2022-01-01	0	2.0	6.0	10.0	16.0
10086	2022-01-02	1	2.0	4.0	8.0	14.0
10086	2022-01-03	2	2.0	4.0	6.0	14.0
10086	2022-01-05	4	2.0	4.0	6.0	12.0
10086	2022-01-07	6	2.0	2.0	4.0	12.0
10086	2022-01-11	10	2.0	2.0	4.0	10.0
10086	2022-01-15	14	2.0	2.0	4.0	8.0
10086	2022-01-18	17	2.0	2.0	2.0	6.0
10086	2022-02-01	31	2.0	2.0	4.0	6.0
10086	2022-02-05	35	2.0	2.0	2.0	4.0
10086	2022-03-01	59	2.0	2.0	2.0	2.0