SQL查询实现固定起始日期按月份结束的忠诚度会员指标累计统计

核心是先生成所有历史自然月的月末节点，再将订单数据和月末节点关联，计算每个用户截止到对应月末的累计指标，最后按月份+忠诚度等级聚合即可，具体步骤如下：

• 生成统计用的月末时间序列：提取订单表中所有有数据的自然月，生成每个月的月末日期作为统计周期节点，覆盖从最早数据月份到当前月的所有周期
• 关联订单做时间截断：每个月末节点仅关联时间早于等于该月末的有效订单（保留原逻辑中net_sales > 0的过滤条件）
• 按用户+月末分组算等级：对每个用户在每个统计月末的累计消费计算对应忠诚度等级，同时计算该用户截止到当月末的订单数、总消费、首末次消费间隔
• 按月末+等级聚合得结果：汇总每个月末各等级的用户总量、总订单、总销售额等累计指标

WITH all_month_ends AS (
    -- 生成所有需要统计的月末节点
    SELECT DISTINCT LAST_DAY(SCM.timestamp) AS month_end
    FROM {{ @public_fact_shopify_criquet_master AS SCM }}
    WHERE SCM.net_sales > 0
)
SELECT
    inside.month_end,
    inside.loyalty_tier,
    SUM(inside.customers) AS customers,
    SUM(inside.orders) AS orders,
    SUM(inside.net_sales) AS net_sales,
    SUM(inside.time_between_sales) AS time_between_sales
FROM
    (
        SELECT
            ame.month_end,
            SCM.customer_email,
            CASE WHEN SUM(SCM.net_sales) BETWEEN 0 AND 124.99 THEN 'no_tier' 
                WHEN SUM(SCM.net_sales) BETWEEN 125 AND 198.99 THEN 'almost_vip' 
                WHEN SUM(SCM.net_sales) BETWEEN 199 AND 749.99 THEN 'vip' 
                WHEN SUM(SCM.net_sales) >= 750 THEN 'the_players_club' 
                ELSE NULL 
            END AS loyalty_tier,
            COUNT(DISTINCT SCM.customer_email) AS customers,
            COUNT(DISTINCT SCM.order_id) AS orders,
            SUM(SCM.net_sales) AS net_sales,
            DATEDIFF(day, MIN(SCM.timestamp), MAX(SCM.timestamp)) AS time_between_sales
        FROM  {{ @public_fact_shopify_criquet_master AS SCM }}
        JOIN all_month_ends ame ON SCM.timestamp <= ame.month_end
        WHERE SCM.net_sales > 0
        GROUP BY ame.month_end, SCM.customer_email
    ) AS inside
GROUP BY inside.month_end, inside.loyalty_tier
ORDER BY inside.month_end, inside.loyalty_tier

注：如果所用数据库没有内置LAST_DAY函数，可以替换为对应语法的月末计算逻辑，通用写法参考：DATE_ADD(DATE_TRUNC('month', SCM.timestamp), INTERVAL 1 MONTH) - INTERVAL 1 DAY，根据你使用的数据库类型调整即可。

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