MS SQL查询脚本转换为PostgreSQL 标注语句转换求助

转换完成的PostgreSQL脚本 #

WITH procedurerange_cte AS (
    SELECT DISTINCT
        accountsize AS HospitalSize,
        MIN(catprocsannualintegratedallpayer) OVER (PARTITION BY account size) AS MinProcsPerAcct,
        MAX(catprocsannualintegratedallpayer) OVER (PARTITION BY account size) AS MaxProcsPerAcct
    FROM sandbox.vw_hopd_universe_1_ms
    GROUP BY account size, catprocsannualintegratedallpayer
), accts_cte AS (
    SELECT 
        account size AS HospitalSize,
        COUNT(master_id) AS Count,
        SUM(catprocsannualintegratedallpayer) AS catprocsannualintegratedallpayer
    FROM sandbox.vw_hopd_universe_1_ms
    GROUP BY account size
), allcatprocs_cte AS (
    SELECT 
        SUM(catprocsannualintegratedallpayer) AS AllAnnCatProcs
    FROM sandbox.accts_universeaccts
), totals_cte AS (
    SELECT 
        CASE WHEN HospitalSize IS NULL THEN 'Total' ELSE HospitalSize END AS HospitalSize,
        SUM(Count) AS Count,
        SUM(catprocsannualintegratedallpayer) AS catprocsannualintegratedallpayer
    FROM accts_cte
    GROUP BY GROUPING SETS ((HospitalSize, Count, catprocsannualintegratedallpayer), ())
)
SELECT
    a.HospitalSize,
    a.Count,
    a.Count::float / (SELECT Count::float FROM totals_cte WHERE HospitalSize='Total') AS "%OfHospitals",
    a.catprocsannualintegratedallpayer AS HospitalAnnCatProcs,
    a.catprocsannualintegratedallpayer::float / (SELECT catprocsannualintegratedallpayer::float FROM totals_cte WHERE HospitalSize='Total') AS "%OfHospProcs",
    a.catprocsannualintegratedallpayer::float / (SELECT AllAnnCatProcs::float FROM allcatprocs_cte) AS "%OfAllProcs",
    MinProcsPerAcct,
    MaxProcsPerAcct,
    CASE
        WHEN a.HospitalSize='Large' THEN '8 to 10' 
        WHEN a.HospitalSize='Medium' THEN '5 to  7' 
        WHEN a.HospitalSize='Small' THEN '0 to  4' 
    END AS DecilesIncluded
FROM totals_cte AS a
LEFT JOIN procedurerange_cte AS b
ON a.HospitalSize = b.HospitalSize

适配修改说明 #

• 替换了MSSQL专属的CONVERT(float, 字段)类型转换语法，改用PostgreSQL标准的字段::float语法，避免整数除法导致计算结果为0的问题
• PostgreSQL中包含特殊字符%的字段别名需要用双引号包裹，因此将三个百分比列的别名都加了双引号
• 统一了CTE名称的大小写，PostgreSQL默认对未加双引号的标识符转为小写，因此将原代码中大写的allCatProcs_cte调整为和CTE定义一致的小写allcatprocs_cte
• 原CASE判断逻辑PostgreSQL完全兼容，无需修改即可直接运行

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