如何在MySQL中按SEGMENTED_DATE计算value列的周期间差值？

输入表说明 #

你当前通过以下SQL生成了包含分群、用户数、统计日期的输入表：

select segment ,count(distinct user_id)as value,SEGMENTED_DATE  
from weekly_customer_RFM_TABLE
where segment in('About to sleep','Promising','champion','Loyal_customer',
           'Potential_Loyalist','At_Risk','Need_Attention','New_customer',
           'Hibernating','Cant_loose')
  and SEGMENTED_DATE between '2022-10-07' and '2022-10-28'
Group by segment,SEGMENTED_DATE

核心实现方案 #

要计算同一分群下后一周用户数与前一周的差值，直接用SQL窗口函数LAG()就能完成，它可以精准获取同分组内的前一行数据。完整SQL如下：

WITH weekly_segment_data AS (
    -- 复用原输入表生成逻辑
    select segment, count(distinct user_id) as value, SEGMENTED_DATE
    from weekly_customer_RFM_TABLE
    where segment in ('About to sleep','Promising','champion','Loyal_customer',
                     'Potential_Loyalist','At_Risk','Need_Attention','New_customer',
                     'Hibernating','Cant_loose')
      and SEGMENTED_DATE between '2022-10-07' and '2022-10-28'
    group by segment, SEGMENTED_DATE
)
SELECT
    segment,
    SEGMENTED_DATE AS current_week_date,
    value - LAG(value, 1) OVER (PARTITION BY segment ORDER BY SEGMENTED_DATE) AS value_diff
FROM weekly_segment_data
-- 可选：过滤掉无前置数据的首周记录（比如2022-10-07）
WHERE LAG(value, 1) OVER (PARTITION BY segment ORDER BY SEGMENTED_DATE) IS NOT NULL;

关键逻辑解释 #

• PARTITION BY segment：确保只在同一个用户分群内进行前后周的对比计算
• ORDER BY SEGMENTED_DATE：保证按统计日期的先后顺序取数，避免时间混乱
• LAG(value, 1)：获取当前分群下前一周的用户数，1表示取上一行数据（对应前一周）
• value_diff：直接用当前周用户数减去前一周数值，得到差值结果

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