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

原创

已于 2024-10-16 09:44:55 修改 · 1.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #database #大数据 #big data

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

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

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

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
un

最低0.47元/天解锁文章