SQL实现静态日期至可变日期的滚动累计（Running Total）计算

滚动累计值SQL实现说明 #

原有代码问题 #

• 语法错误：customers字段重复声明AS "customers"别名，直接触发语法报错
• 窗口函数逻辑错误：
  • PARTITION BY inside.order_date会将数据按单日拆分分区，无法实现跨日期的累计计算
  • ROWS BETWEEN是行偏移量规则，不能直接传入日期字符串作为边界值

修正后可运行代码 #

SELECT 
  TO_CHAR(inside.order_date, 'YY-MM') AS "first_purchase_period",
  inside.loyalty_tier,
  -- 窗口函数调整分区、排序规则，实现从业务启动日到当前月的累计
  SUM(inside.customers) OVER (
    PARTITION BY inside.loyalty_tier 
    ORDER BY TO_CHAR(inside.order_date, 'YY-MM') 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW
  ) 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  
    customer_email,
    TRUNC(SCM.timestamp) AS "order_date",
    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}}
  WHERE 
    SCM.net_sales > 0
    -- 过滤业务启动前的无效数据
    AND TRUNC(SCM.timestamp) >= '2011-11-01'
  GROUP BY customer_email, TRUNC(SCM.timestamp)
) AS inside
GROUP BY TO_CHAR(inside.order_date, 'YY-MM'), inside.loyalty_tier
ORDER BY first_purchase_period, loyalty_tier;

核心修改说明 #

• 修复重复别名的语法错误
• 窗口函数按会员等级分区、按年月排序，使用UNBOUNDED PRECEDING指代分区内第一行数据，也就是业务启动月的对应数据，实现从起始日到当前月的滚动累计
• 新增日期过滤条件，排除2011年11月业务启动前的无效数据
• 可选增加排序规则，输出结果按年月、会员等级排序更易读

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