能否通过两次LEFT JOIN实现多维度会员留存率计算？

问题背景 #

需要计算2021年1月购物会员的两个复购指标：

• 2月再次购物的会员占比
• 2021年2-4月期间再次购物的会员占比
  原SQL仅保留第一个LEFT JOIN时可正常运行，添加第二个LEFT JOIN计算3个月复购率时，报错提示：FROM keyword not found where expected。单独计算各指标可行，但覆盖多时间周期时效率低下，需确认是否允许使用两次LEFT JOIN，或有无更优方案。

错误原因分析 #

你的SQL核心问题并非多次LEFT JOIN不被允许，而是标识符命名不符合SQL语法规范：

• 字段别名1month_retention_rate以数字开头，多数SQL方言（如Oracle、MySQL严格模式）不允许标识符以数字起始，这是触发语法错误的直接原因。
• 第三个子查询C中，按members和year_month分组会导致同一会员在2-4月多次购物时生成多条记录，虽count(distinct C.members)仍能得到正确结果，但会增加关联时的数据量，影响效率。

修正后的可运行SQL #

将数字开头的别名修改为合法名称（如one_month_retention_rate），同时保留两次LEFT JOIN的结构：

select 
      year_month_january21
,   count(distinct A.members) as num_of_mems_shopped_january21 
,   count(distinct B.members) as retained_february21
,  count(distinct B.members)/count(distinct A.members) *100 as one_month_retention_rate
,  count(distinct C.members)/count(distinct A.members) *100 as within_3months_retention_rate
from 
    (select 
        members
    ,   year_month as year_month_january21 
    from table.members t
    join table.date tm on t.dt_key = tm.date_key
    and year_month = 202101
    group by members, year_month) A
left join 
    (select 
        members
    ,   year_month as year_month_february21 
    from table.members t
    join table.date tm on t.dt_key = tm.date_key
    and year_month = 202102
    group by members, year_month) B on A.members = B.members
left join 
    (select 
        members
    from table.members t
    join table.date tm on t.dt_key = tm.date_key
    and year_month between 202102 and 202104
    group by members) C on A.members = C.members
group by year_month_january21;

注：子查询C中移除了year_month的分组和返回，因为我们只需要判断会员是否在该区间有购物，不需要保留具体月份，减少冗余数据。

更高效的优化方案（避免多次子查询） #

使用条件聚合替代多次LEFT JOIN，只需扫描一次原始数据，大幅提升效率：

with member_shopping_months as (
    select 
        t.members
    ,   max(case when tm.year_month = 202101 then 1 else 0 end) as shopped_jan21
    ,   max(case when tm.year_month = 202102 then 1 else 0 end) as shopped_feb21
    ,   max(case when tm.year_month between 202102 and 202104 then 1 else 0 end) as shopped_2_4_21
    from table.members t
    join table.date tm on t.dt_key = tm.date_key
    where tm.year_month between 202101 and 202104
    group by t.members
)
select 
    '202101' as year_month_january21
,   count(distinct case when shopped_jan21 = 1 then members end) as num_of_mems_shopped_january21
,   count(distinct case when shopped_jan21 = 1 and shopped_feb21 = 1 then members end) as retained_february21
,   round(
        count(distinct case when shopped_jan21 = 1 and shopped_feb21 = 1 then members end)
        / count(distinct case when shopped_jan21 = 1 then members end) *100
    , 2) as one_month_retention_rate
,   round(
        count(distinct case when shopped_jan21 = 1 and shopped_2_4_21 = 1 then members end)
        / count(distinct case when shopped_jan21 = 1 then members end) *100
    , 2) as within_3months_retention_rate
from member_shopping_months;

该方案通过CTE先统计每个会员的购物月份标记，再用条件聚合直接计算所需指标，避免了多次表关联的开销，尤其适合多时间周期的复购分析。

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