求助：将PostgreSQL查询转换为可在SQL Server运行的查询

原PostgreSQL查询的核心逻辑是按月统计活跃用户，计算每个月的流失用户数（上月活跃但本月未活跃的用户）及流失率，由于SQL Server不支持数组类型和PostgreSQL特有的数组操作，我们需要调整实现方式，以下是转换后的查询：

WITH monthly_users AS (
    -- 提取每个月的独立活跃用户（去重）
    SELECT
        DATEADD(month, DATEDIFF(month, 0, CAST(occurred_at AS DATE)), 0) AS month_start,
        user_id
    FROM yammer_events
    GROUP BY DATEADD(month, DATEDIFF(month, 0, CAST(occurred_at AS DATE)), 0), user_id
),
monthly_agg AS (
    -- 按月统计总活跃用户数，同时关联上月的统计数据
    SELECT
        CAST(month_start AS DATE) AS month,
        COUNT(user_id) AS total_users,
        LAG(month_start) OVER (ORDER BY month_start) AS prev_month,
        LAG(COUNT(user_id)) OVER (ORDER BY month_start) AS prev_total_users
    FROM monthly_users
    GROUP BY month_start
)
SELECT
    month,
    -- 计算流失用户数：上月活跃但本月未活跃的用户数量
    (
        SELECT COUNT(mu_prev.user_id)
        FROM monthly_users mu_prev
        WHERE mu_prev.month_start = ma.prev_month
        AND NOT EXISTS (
            SELECT 1 FROM monthly_users mu_curr
            WHERE mu_curr.month_start = ma.month_start
            AND mu_curr.user_id = mu_prev.user_id
        )
    ) AS churn_count,
    -- 计算流失率：流失用户数除以上月总用户数，转换为百分比
    CASE
        WHEN ma.prev_total_users = 0 THEN 0.0
        ELSE CAST(
            (
                SELECT COUNT(mu_prev.user_id)
                FROM monthly_users mu_prev
                WHERE mu_prev.month_start = ma.prev_month
                AND NOT EXISTS (
                    SELECT 1 FROM monthly_users mu_curr
                    WHERE mu_curr.month_start = ma.month_start
                    AND mu_curr.user_id = mu_prev.user_id
                )
            ) AS NUMERIC
        ) / ma.prev_total_users * 100
    END AS churn_rate_percentage
FROM monthly_agg ma
ORDER BY ma.month;

关键转换点说明 #

• 日期截断：PostgreSQL的date_trunc('month', occurred_at::date)替换为SQL Server的DATEADD(month, DATEDIFF(month, 0, CAST(occurred_at AS DATE)), 0)，用于获取当月第一天的起始时间。
• 数组操作替代：SQL Server无原生数组类型，原查询中array_agg+数组差集的逻辑，改用NOT EXISTS子查询统计上月活跃但本月未活跃的用户数。
• 类型转换：PostgreSQL的::numeric替换为SQL Server的CAST(... AS NUMERIC)，确保除法运算为浮点精度。
• 窗口函数兼容：保留原查询的窗口函数逻辑，调整为SQL Server支持的语法形式。

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