计算会员每次购买的季度间隔及全体会员总平均间隔

步骤1：数据整理与排序 #

首先需要将每个会员的购买记录按时间先后排序，把Year和Quarter转换成统一的季度序号（公式：Year*4 + Quarter），方便后续计算间隔。例如：

• 2020年第1季度 → 2020*4+1=8081
• 2021年第1季度 → 2021*4+1=8085

步骤2：计算单个会员的购买间隔 #

对每个会员排序后的购买记录，计算相邻两次购买的季度序号差值，得到单次购买间隔：

• 示例中会员10521321仅有两次购买，序号差为4，对应间隔4个季度
• 会员113213213有4次购买，得到3个间隔：4、5、3个季度

步骤3：计算单个会员的平均购买间隔 #

将该会员的所有间隔求和，除以间隔的数量：

• 会员113213213：(4+5+3)/3=4个季度

步骤4：计算全体会员的总平均购买间隔 #

有两种统计逻辑，可根据业务需求选择：

• 逻辑一：将所有会员的平均购买间隔求和，再除以会员总数
• 逻辑二：直接将所有会员的所有间隔求和，再除以间隔的总数量

SQL实现示例 #

假设你的表名为member_purchases，字段为MemberNo、Year、Quarter、Purchase，代码如下：

-- 1. 生成会员购买季度序号并排序
WITH ranked_purchases AS (
    SELECT 
        MemberNo,
        Year*4 + Quarter AS quarter_seq,
        ROW_NUMBER() OVER(PARTITION BY MemberNo ORDER BY Year, Quarter) AS purchase_rank
    FROM member_purchases
    WHERE Purchase > 0 -- 过滤有效购买记录
),
-- 2. 计算相邻购买的间隔
purchase_intervals AS (
    SELECT 
        curr.MemberNo,
        curr.quarter_seq - prev.quarter_seq AS interval_quarters
    FROM ranked_purchases curr
    JOIN ranked_purchases prev 
        ON curr.MemberNo = prev.MemberNo 
        AND curr.purchase_rank = prev.purchase_rank + 1
),
-- 3. 计算单个会员的平均间隔
member_avg_intervals AS (
    SELECT 
        MemberNo,
        AVG(interval_quarters) AS avg_interval
    FROM purchase_intervals
    GROUP BY MemberNo
)
-- 4. 计算全体总平均（二选一）
-- 逻辑一：会员平均间隔的平均值
SELECT AVG(avg_interval) AS overall_avg_interval FROM member_avg_intervals;

-- 逻辑二：所有间隔的总平均值
-- SELECT AVG(interval_quarters) AS overall_avg_interval FROM purchase_intervals;

Python实现示例（基于Pandas） #

import pandas as pd

# 读取数据（替换为你的数据源路径）
df = pd.read_csv('member_purchases.csv')

# 生成统一季度序号
df['quarter_seq'] = df['Year'] * 4 + df['Quarter']

# 过滤有效购买记录
df = df[df['Purchase'] > 0]

# 按会员分组并按时间排序
df_sorted = df.sort_values(by=['MemberNo', 'Year', 'Quarter'])

# 计算每个会员的相邻购买间隔
df_sorted['interval_quarters'] = df_sorted.groupby('MemberNo')['quarter_seq'].diff()

# 剔除无前置记录的首条购买数据
valid_intervals = df_sorted.dropna(subset=['interval_quarters'])

# 计算单个会员的平均间隔
member_avg = valid_intervals.groupby('MemberNo')['interval_quarters'].mean()

# 计算全体总平均（二选一）
overall_avg_member = member_avg.mean()
overall_avg_all = valid_intervals['interval_quarters'].mean()

print(f"全体会员总平均（会员均值的平均）：{overall_avg_member:.2f} 季度")
print(f"全体会员总平均（所有间隔的平均）：{overall_avg_all:.2f} 季度")

内容的提问来源于stack exchange，提问作者Yen