Snowflake SQL关联查询如何获取最近一次转化会话用于二次关联

问题根因 #

• 首次关联prev_lc时返回的是当前lead下所有早于当前session的转化会话，并非仅最近1条，导致后续过滤逻辑失效
• SQL语法不允许在JOIN的ON子句中使用聚合函数，关联条件的执行优先级高于聚合计算，所以会触发编译报错

可行方案1：提前用窗口函数计算每个lead的最近转化会话（推荐） #

提前通过CTE预处理得到每个lead_id对应的最近一次转化会话，每个lead仅保留1条记录，后续直接关联即可。

-- 预处理：拿到每个lead的最近一次转化会话
WITH last_converted_session AS (
    SELECT 
        ss.lead_id,
        ss.session_start AS last_lc_session_start,
        ss.id AS last_lc_session_id
    FROM (
        SELECT 
            ss.lead_id,
            ss.session_start,
            ss.id,
            -- 按lead分区，会话时间倒序排列，最近的转化会话排第一
            ROW_NUMBER() OVER (PARTITION BY ss.lead_id ORDER BY ss.session_start DESC) AS rn
        FROM sessions ss
        INNER JOIN lead_conversions inner_lc 
            ON inner_lc.session_id = ss.id
    ) t
    WHERE rn = 1 -- 仅保留每个lead最近的1次转化会话
)
-- 主查询关联逻辑
SELECT 
    -- 此处填写你需要查询的字段
FROM 你的主表 s -- 替换为你实际的主表，原逻辑里主表别名为s
LEFT JOIN last_converted_session prev_lc
    ON prev_lc.lead_id = lc.lead_id 
    AND prev_lc.last_lc_session_start::TIMESTAMP < s.session_start::TIMESTAMP
LEFT JOIN cte_sessions reset_prev_sess 
    ON reset_prev_sess.lead_id = lc.lead_id 
    AND reset_prev_sess.session_start::TIMESTAMP <= s.session_start::TIMESTAMP 
    AND (
        prev_lc.last_lc_session_start::TIMESTAMP IS NULL 
        OR 
        reset_prev_sess.session_start::TIMESTAMP > prev_lc.last_lc_session_start::TIMESTAMP
    )

可行方案2：用标量子查询直接获取最近转化时间 #

如果不需要转化会话的其他字段，仅需要时间边界，可以直接在关联条件里用标量子查询取最大值，不需要提前做关联：

LEFT JOIN cte_sessions reset_prev_sess 
    ON reset_prev_sess.lead_id = lc.lead_id 
    AND reset_prev_sess.session_start::TIMESTAMP <= s.session_start::TIMESTAMP 
    AND (
        -- 标量子查询直接取当前lead早于当前session的最近转化时间
        (SELECT MAX(ss.session_start) FROM sessions ss INNER JOIN lead_conversions inner_lc ON inner_lc.session_id = ss.id WHERE ss.lead_id = lc.lead_id AND ss.session_start < s.session_start) IS NULL 
        OR 
        reset_prev_sess.session_start::TIMESTAMP > (SELECT MAX(ss.session_start) FROM sessions ss INNER JOIN lead_conversions inner_lc ON inner_lc.session_id = ss.id WHERE ss.lead_id = lc.lead_id AND ss.session_start < s.session_start)
    )

原有写法问题说明 #

• 首次分组子查询按inner_lc.id分组，是按单条转化记录聚合，不是按lead_id聚合，所以每个lead仍然会返回多条转化会话记录，无法拿到全局最大值
• ON子句中使用聚合函数不符合SQL执行顺序要求，关联过滤执行时还未做聚合计算，必然会触发报错

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