如何优化漏斗分析SQL查询以支持任意事件数（Java生成场景）

我正在开展漏斗分析，观察用户从起始事件到结束事件的转化情况（例如包含Visit、Sign Up、Activate的漏斗）。现有一张存储用户事件的表events_table，字段包括user_id、epoch_utc、event_date、event_name及medium。需要展示按指定顺序完成漏斗的用户，并按medium拆分各环节的流失用户及其数量。

目前参考模板写了固定3个事件的查询（如下），但现在需要支持运行时定义任意数量的漏斗事件，同时用Java应用动态生成查询，求优化方案。

with visit_users as (
    select user_id, min(epoch_utc) as min_time, min(medium) as medium, count(event_name) as total_events from events_table 
    where  date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d') and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d')
    and event_name = 'Visit'
    group by 1
),

signup_users as (
    select su.user_id, su.min_time, su.medium, su.total_events from 
    (
        select user_id, min(epoch_utc) as min_time, min(medium) as medium, count(event_name) as total_events from (
            SELECT user_id, epoch_utc, medium, event_name   FROM events_table 
                where  date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d') and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d')  AND 
                ( event_name = 'Sign Up' ) 
        ) group by 1
    ) su, visit_users acu
    where su.user_id = acu.user_id
    and su.min_time > acu.min_time
),

activate_users as (
    select icu.user_id, icu.min_time, icu.medium, icu.total_events from 
    (
        select user_id, min(epoch_utc) as min_time, min(medium) as medium, count(event_name) as total_events from (
            SELECT user_id, epoch_utc, medium, event_name   FROM events_table 
                where  date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d') and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d')  AND 
                ( event_name = 'Activate' ) 
        ) group by 1
    ) icu, signup_users su
    where icu.user_id = su.user_id
    and icu.min_time > su.min_time
)

select * from (
    select step, medium, count(user_id) as total_users, 0 as total_time, 0 as avg_time, sum(total_events) as total_events from (
    select 'Visit' as step, acu.medium, acu.user_id, 
    0, acu.total_events from visit_users acu
    ) group by 1, 2 
    UNION
    select step, medium, count(user_id) as total_users, sum(diff) as total_time, (sum(diff) / count(user_id) ) as avg_time, sum(total_events) as total_events from ( 
    select 'Sign Up' as step, su.medium, su.user_id, 
    date_diff('second', from_unixtime(acu.min_time/1000) , from_unixtime(su.min_time/1000)) as diff,
    su.total_events
    from visit_users acu, signup_users su
    where acu.user_id = su.user_id
    ) group by 1, 2 
    UNION
    select step, medium, count(user_id) as total_users, sum(diff) as total_time, (sum(diff) / count(user_id) ) as avg_time, sum(total_events) as total_events from ( 
    select 'Activate' as step, icu.medium, icu.user_id, 
    date_diff('second', from_unixtime(su.min_time/1000) , from_unixtime(icu.min_time/1000)) as diff,
    icu.total_events
    from signup_users su, activate_users icu
    where su.user_id = icu.user_id
    ) group by 1, 2
) order by step, total_users desc

优化方案：支持任意数量事件的动态查询 #

1. 重构查询逻辑，使用通用化CTE #

核心思路是避免为每个事件单独写CTE，而是先统一处理所有漏斗内的事件，再通过窗口函数和自连接实现漏斗步骤的关联：

-- 通用化基础CTE：过滤时间范围+漏斗事件，按用户和事件分组取最早时间
with funnel_events as (
    select 
        user_id,
        event_name,
        min(epoch_utc) as min_time,
        min(medium) as medium,
        count(event_name) as total_events
    from events_table
    where 
        date_parse(event_date,'%Y-%m-%d') >= date_parse('{start_date}','%Y-%m-%d') 
        and date_parse(event_date,'%Y-%m-%d') <= date_parse('{end_date}','%Y-%m-%d')
        and event_name in ({event_list}) -- 动态替换为漏斗事件列表，比如'Visit','Sign Up','Activate'
    group by user_id, event_name
),
-- 给每个用户的漏斗事件按指定顺序编号，同时验证时间递增
user_funnel_steps as (
    select
        fe.user_id,
        fe.event_name as step,
        fe.min_time,
        fe.medium,
        fe.total_events,
        -- 按漏斗定义的顺序给步骤编号，确保后续步骤时间晚于前序
        row_number() over(
            partition by fe.user_id 
            order by case fe.event_name 
                {event_order_case} -- 动态替换，比如when 'Visit' then 1 when 'Sign Up' then 2 when 'Activate' then 3 end
            ) as step_order,
        lag(fe.min_time) over(partition by fe.user_id order by step_order) as prev_step_time
    from funnel_events fe
    -- 过滤掉不符合时间顺序的异常事件（比如Sign Up时间早于Visit）
    where exists (
        select 1 from funnel_events fe_prev
        where fe_prev.user_id = fe.user_id
            and case fe_prev.event_name {event_order_case} < case fe.event_name {event_order_case}
            and fe_prev.min_time < fe.min_time
    ) or case fe.event_name {event_order_case} = 1 -- 保留第一个步骤
),
-- 统计每个步骤的用户数、时间差等指标
step_metrics as (
    select
        step,
        medium,
        count(distinct user_id) as total_users,
        sum(case when prev_step_time is not null then date_diff('second', from_unixtime(prev_step_time/1000), from_unixtime(min_time/1000)) else 0 end) as total_time,
        avg(case when prev_step_time is not null then date_diff('second', from_unixtime(prev_step_time/1000), from_unixtime(min_time/1000)) else null end) as avg_time,
        sum(total_events) as total_events
    from user_funnel_steps
    group by step, medium
),
-- 计算流失用户数（前序步骤用户数 - 当前步骤用户数）
churn_metrics as (
    select
        current.step,
        current.medium,
        prev.total_users - current.total_users as churn_users
    from step_metrics current
    left join step_metrics prev on current.medium = prev.medium
        and case current.step {event_order_case} = case prev.step {event_order_case} + 1
)

-- 合并转化和流失指标
select
    sm.step,
    sm.medium,
    sm.total_users,
    sm.total_time,
    sm.avg_time,
    sm.total_events,
    coalesce(cm.churn_users, 0) as churn_users
from step_metrics sm
left join churn_metrics cm on sm.step = cm.step and sm.medium = cm.medium
order by case sm.step {event_order_case}, sm.total_users desc

2. Java动态生成查询的关键实现 #

在Java中可以通过字符串拼接或模板引擎（如Freemarker）来动态生成查询中的变量部分：

• 动态替换时间范围：将{start_date}和{end_date}替换为用户输入的时间参数
• 生成事件列表：将用户定义的漏斗事件列表（比如List<String> funnelEvents = Arrays.asList("Visit", "Sign Up", "Activate")）拼接成'Visit','Sign Up','Activate'的格式
• 生成事件顺序CASE语句：遍历漏斗事件列表，生成排序逻辑，示例代码：

  StringBuilder eventOrderCase = new StringBuilder();
  for (int i = 0; i < funnelEvents.size(); i++) {
      eventOrderCase.append("when '").append(funnelEvents.get(i)).append("' then ").append(i+1).append(" ");
  }
  // 替换查询中的{event_order_case}占位符
  

• 处理边界情况：比如第一个步骤没有前序步骤，流失用户数设为0；自动过滤用户跳过步骤的异常数据（比如直接从Visit到Activate，视为Sign Up环节流失）

3. 性能优化点 #

• 减少重复计算：在基础CTE中一次性过滤时间范围和漏斗事件，避免多次重复解析event_date
• 用窗口函数替代多次关联：原查询中每个步骤都要关联前序CTE，改用lag()窗口函数可大幅减少关联次数，提升查询效率
• 提前过滤异常数据：在user_funnel_steps中过滤掉时间顺序不符合漏斗定义的事件，避免无效数据干扰结果
• 索引优化：给events_table添加(event_date, event_name, user_id)复合索引，加速基础数据的过滤和分组

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