如何编写无需CTE的简洁SQL生成用户每日会员状态表？

数据表说明 #

1. activity表（会员操作记录） #

2. dim_date表（日期维度表） #

需求 #

生成每日用户状态表，每行对应一个用户的单日会员状态（active或inactive），输出结构如下：

原实现代码 #

with cte as ( 
select   
a.user_id   
,a.activity   
,a.date as activity_date   
,dd.date   
,row_number() over (partition by a.user_id, dd.date order by a.date desc) as rn 
from activity a 
left join dim_date dd on a.date <= dd.date 
) 
select    
user_id   
,date   
,case when a.activity = "activate" then "active" else "inactive" end as membership_status 
from cte 
where rn = 1

更简洁高效的实现方案 #

可以通过计算每个操作的有效时间区间来避免全量关联后再过滤的低效逻辑，代码如下（仅用子查询实现更优性能，逻辑更直观）：

SELECT
    a.user_id,
    dd.date,
    CASE a.activity WHEN 'activate' THEN 'active' ELSE 'inactive' END AS membership_status
FROM (
    SELECT
        user_id,
        activity,
        date,
        -- 获取下一次操作的日期，若无则设为远未来日期确保覆盖后续所有日期
        LEAD(date, 1, '9999-12-31') OVER (PARTITION BY user_id ORDER BY date) AS next_activity_date
    FROM activity
) a
JOIN dim_date dd 
  ON dd.date >= a.date 
  AND dd.date < a.next_activity_date
ORDER BY a.user_id, dd.date;

方案优势 #

1. 性能更优：先通过LEAD()窗口函数计算每个操作的有效结束日期，仅将日期表与操作的有效区间关联，避免了原方案中每个日期与所有历史操作的全量关联，大幅减少中间数据量。
2. 逻辑清晰：直接基于操作的时间区间映射每日状态，完全贴合业务逻辑的直观理解。
3. 代码简洁：无需复杂的CTE和ROW_NUMBER()过滤，仅通过一次窗口函数计算和区间关联即可完成需求。

如果严格要求完全无CTE/子查询，可通过关联子查询实现（兼容性稍差，部分数据库支持）：

SELECT
    a.user_id,
    dd.date,
    CASE a.activity WHEN 'activate' THEN 'active' ELSE 'inactive' END AS membership_status
FROM activity a
JOIN dim_date dd 
  ON dd.date >= a.date 
  AND dd.date < COALESCE(
    (SELECT MIN(date) FROM activity WHERE user_id = a.user_id AND date > a.date),
    '9999-12-31'
  )
ORDER BY a.user_id, dd.date;

注意事项 #

• 日期格式需与数据库兼容，若原表日期为字符串格式，建议转换为日期类型后进行比较，避免逻辑错误。
• LEAD()函数的默认值（9999-12-31）需根据数据库支持的最大日期调整。

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