PostgreSQL迁移至Microsoft SQL时如何将CTE转换为临时表或子查询

核心适配调整说明：原PostgreSQL语法里的::date类型强转，在SQL Server中替换为CAST(xxx AS DATE)即可兼容，以下分别提供两种转换方案：

方案1：临时表实现 #

逻辑拆分清晰，排查问题方便，可复用中间计算结果：

-- 1. 定义统计日期范围临时表
CREATE TABLE #date_range (
    start_date DATE,
    end_date DATE
)
INSERT INTO #date_range
SELECT CAST('2020-03-01' AS DATE) AS start_date, CAST('2020-04-01' AS DATE) AS end_date

-- 2. 统计期初有订阅的账号及对应MRR
CREATE TABLE #start_accounts (
    account_id INT,
    total_mrr DECIMAL(18,2)
)
INSERT INTO #start_accounts
SELECT s.account_id, SUM(s.mrr) AS total_mrr
FROM subscription s 
INNER JOIN #date_range d ON
s.start_date <= d.start_date
AND (s.end_date > d.start_date OR s.end_date IS NULL)
GROUP BY s.account_id

-- 3. 统计期末有订阅的账号及对应MRR
CREATE TABLE #end_accounts (
    account_id INT,
    total_mrr DECIMAL(18,2)
)
INSERT INTO #end_accounts
SELECT s.account_id, SUM(s.mrr) AS total_mrr
FROM subscription s 
INNER JOIN #date_range d ON
s.start_date <= d.end_date
AND (s.end_date > d.end_date OR s.end_date IS NULL)
GROUP BY s.account_id

-- 4. 统计留存账号及对应MRR
CREATE TABLE #retained_accounts (
    account_id INT,
    total_mrr DECIMAL(18,2)
)
INSERT INTO #retained_accounts
SELECT s.account_id, SUM(e.total_mrr) AS total_mrr
FROM #start_accounts s
INNER JOIN #end_accounts e ON s.account_id = e.account_id
GROUP BY s.account_id

-- 5. 计算最终留存/流失指标
SELECT 
    retain_mrr / start_mrr AS net_mrr_retention_rate,
    1.0 - retain_mrr / start_mrr AS net_mrr_churn_rate,
    start_mrr,
    retain_mrr
FROM 
    (SELECT SUM(total_mrr) AS start_mrr FROM #start_accounts) AS start_mrr,
    (SELECT SUM(total_mrr) AS retain_mrr FROM #retained_accounts) AS retain_mrr

-- 可选：使用完成后释放临时表
DROP TABLE #date_range
DROP TABLE #start_accounts
DROP TABLE #end_accounts
DROP TABLE #retained_accounts

方案2：嵌套子查询实现 #

无需创建临时对象，单次查询即可完成计算：

SELECT 
    retain_mrr / start_mrr AS net_mrr_retention_rate,
    1.0 - retain_mrr / start_mrr AS net_mrr_churn_rate,
    start_mrr,
    retain_mrr
FROM 
    -- 计算期初总MRR
    (SELECT SUM(total_mrr) AS start_mrr FROM (
        SELECT s.account_id, SUM(s.mrr) AS total_mrr
        FROM subscription s 
        INNER JOIN (SELECT CAST('2020-03-01' AS DATE) AS start_date, CAST('2020-04-01' AS DATE) AS end_date) d ON
        s.start_date <= d.start_date
        AND (s.end_date > d.start_date OR s.end_date IS NULL)
        GROUP BY s.account_id
    ) AS start_accounts) AS start_mrr,
    -- 计算留存总MRR
    (SELECT SUM(total_mrr) AS retain_mrr FROM (
        SELECT s.account_id, SUM(e.total_mrr) AS total_mrr
        FROM (
            SELECT s.account_id, SUM(s.mrr) AS total_mrr
            FROM subscription s 
            INNER JOIN (SELECT CAST('2020-03-01' AS DATE) AS start_date, CAST('2020-04-01' AS DATE) AS end_date) d ON
            s.start_date <= d.start_date
            AND (s.end_date > d.start_date OR s.end_date IS NULL)
            GROUP BY s.account_id
        ) AS s
        INNER JOIN (
            SELECT s.account_id, SUM(s.mrr) AS total_mrr
            FROM subscription s 
            INNER JOIN (SELECT CAST('2020-03-01' AS DATE) AS start_date, CAST('2020-04-01' AS DATE) AS end_date) d ON
            s.start_date <= d.end_date
            AND (s.end_date > d.end_date OR s.end_date IS NULL)
            GROUP BY s.account_id
        ) AS e ON s.account_id = e.account_id
        GROUP BY s.account_id
    ) AS retained_accounts) AS retain_mrr

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