Snowflake SQL中同起始日期促销活动结果重叠显示问题

原始查询代码 #

with cte as (
    select
        count(distinct o.consumers),
        max(first_order_flag) as first_order_flag,
        date(order_created_date) as date,
        case
        when order_created_date between '2023-01-01' and '2023-01-14' then 'Promo1'
        when order_created_date between '2023-01-01' and '2023-01-21' then 'Promo2'
        else '0'
        end as promotion
    from orders
    group by o.consumers, order_created_date
)

select
    promotion,
    min(date(date)),
    max(date(date)),
    sum(case when first_order_flag = 1 then 1 else 0 end) as new_consumers,
    sum(case when first_order_flag = 0 then 1 else 0 end) as returning_consumers
from cte 
group by promotion;

问题分析 #

原始查询的核心问题在于CASE语句是排他性判断：

• 1月1日-14日的订单会被优先标记为Promo1，不会进入Promo2的判断分支，导致Promo2的统计仅包含1月15日-21日的数据，最小日期显示为1月14日
• 若调换CASE语句的顺序，1月1日-21日的订单会全部被标记为Promo2，Promo1将没有数据
• 需求要求两个促销活动都展示从1月1日起的完整数据，重叠时段（1.1-1.14）的订单需要同时计入两个促销的统计

解决方案 #

要实现重叠时段数据的重复统计，不能用排他性的CASE标记，而是要将每个促销活动作为独立维度，让符合条件的订单关联到对应促销。下面是基于促销维度表的实现方案：

-- 定义促销周期维度表
WITH promo_periods AS (
    SELECT 'Promo1' AS promotion, '2023-01-01'::DATE AS start_date, '2023-01-14'::DATE AS end_date
    UNION ALL
    SELECT 'Promo2' AS promotion, '2023-01-01'::DATE AS start_date, '2023-01-21'::DATE AS end_date
),
-- 先聚合用户每日的订单状态，避免重复统计
user_daily_orders AS (
    SELECT
        consumers,
        DATE(order_created_date) AS order_date,
        MAX(first_order_flag) AS first_order_flag
    FROM orders
    GROUP BY consumers, DATE(order_created_date)
)
-- 关联促销维度和订单数据，统计各促销的指标
SELECT
    pp.promotion,
    MIN(udo.order_date) AS min_date,
    MAX(udo.order_date) AS max_date,
    SUM(CASE WHEN udo.first_order_flag = 1 THEN 1 ELSE 0 END) AS new_consumers,
    SUM(CASE WHEN udo.first_order_flag = 0 THEN 1 ELSE 0 END) AS returning_consumers
FROM promo_periods pp
JOIN user_daily_orders udo 
    ON udo.order_date BETWEEN pp.start_date AND pp.end_date
GROUP BY pp.promotion;

方案说明 #

1. 促销维度表promo_periods：清晰定义每个促销的名称、起止日期，便于后续扩展更多促销活动
2. 用户日订单聚合user_daily_orders：先按用户+日期聚合，避免同一用户单日多次订单被重复统计
3. 关联统计：通过JOIN将订单与符合条件的促销关联，重叠时段的订单会同时匹配Promo1和Promo2，实现两个促销都统计到1月1日起的完整数据

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