将PostgreSQL CTE查询转换为适用于生产库的普通查询

改写后SQL（完全兼容原逻辑，无CTE） #

SELECT *
FROM (
    SELECT
        activity_id,
        ts,
        customer,
        activity,
        CASE
            WHEN activity = 'completed_order' AND LAG(activity) OVER (PARTITION BY customer ORDER BY ts) != 'email'
            THEN NULL
            WHEN activity = 'email' AND LAG(activity) OVER (PARTITION BY customer ORDER BY ts) != 'email'
            THEN 1
            ELSE 0
        END AS cndn
    FROM activity_stream
    WHERE customer IN (SELECT customer FROM activity_stream WHERE activity = 'email')
) AS s
WHERE cndn = 1 OR cndn IS NULL
ORDER BY ts;

改写说明 #

• 直接将原cohort CTE的内部查询逻辑内联为主查询的派生表，所有计算规则、筛选条件、窗口函数逻辑完全和原SQL一致，返回结果无差异。
• 移除了原CTE内部子查询多余的ORDER BY ts语句：内部排序不会影响最终结果，反而会产生不必要的排序开销，最终排序由外层查询的ORDER BY ts保证。
• 改写后避开了PostgreSQL旧版本（12及以下）的CTE优化栅栏限制，优化器可以正常执行谓词下推等优化，执行效率会高于原CTE写法。

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