PostgreSQL中如何用循环/IF优化CASE WHEN以消除重复行

看起来你遇到的重复问题根源在于JOIN逻辑带来的笛卡尔积——你的查询里用了RIGHT OUTER JOIN vrg.rdsid86_katrisk_poly_projected AS urban，这意味着同一个fishnet.gid（也就是你的vrgid）可能会关联到urban表中的多条记录，导致同一个vrgid在不同的行里触发不同的CASE条件，哪怕你加了DISTINCT，只要Weights不同，这些行都会被保留下来。

针对你的需求，我给你两种可行的解决方案，分别对应你提到的两个思路：

方案一：在第三条CASE中排除已匹配第一条条件的vrgid #

这个思路是先提前找出所有满足第一条CASE条件的vrgid，然后在计算第三条CASE的权重时，直接跳过这些vrgid，从根源上避免它们出现在后续的结果里。

WITH first_case_vrgids AS (
    -- 先筛选出所有满足第一条CASE条件的vrgid
    SELECT DISTINCT fishnet.gid AS vrgid
    FROM vrg.urban_nonurban_count_new AS counts
    INNER JOIN vrg.gfk_2016_id_5_digit_pcd_areas2013_projected AS postcodes 
        ON postcodes.id = counts.postid
    RIGHT OUTER JOIN vrg.rdsid_86_quadgrid_centroids AS centroids 
        ON st_contains(postcodes.geom, centroids.geom)
    LEFT OUTER JOIN vrg.rdsid86_quadgrid AS fishnet 
        ON fishnet.gid = centroids.gid
    RIGHT OUTER JOIN vrg.rdsid86_katrisk_poly_projected AS urban 
        ON st_intersects(urban.geom, fishnet.geom)
    WHERE postcodes.id = '42395'
      AND st_intersects(centroids.geom, urban.geom) 
      AND counts.nonurbancells != 0
),
weight_calculations AS (
    SELECT 
        fishnet.gid AS vrgid, 
        fishnet.geom AS geom,
        CASE 
            -- 第一条CASE：保持原逻辑
            WHEN st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0 THEN 0.95 :: numeric / counts.urbancells
            -- 第二条CASE：保持原逻辑
            WHEN st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells = 0 THEN 1.00 :: numeric / counts.urbancells
            -- 第三条CASE：新增排除条件，只处理不在第一条集合里的vrgid
            WHEN NOT st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0 
                 AND fishnet.gid NOT IN (SELECT vrgid FROM first_case_vrgids) THEN 0.05 :: numeric / counts.nonurbancells
            ELSE 0 
        END AS Weights
    FROM vrg.urban_nonurban_count_new AS counts
    INNER JOIN vrg.gfk_2016_id_5_digit_pcd_areas2013_projected AS postcodes 
        ON postcodes.id = counts.postid
    RIGHT OUTER JOIN vrg.rdsid_86_quadgrid_centroids AS centroids 
        ON st_contains(postcodes.geom, centroids.geom)
    LEFT OUTER JOIN vrg.rdsid86_quadgrid AS fishnet 
        ON fishnet.gid = centroids.gid
    RIGHT OUTER JOIN vrg.rdsid86_katrisk_poly_projected AS urban 
        ON st_intersects(urban.geom, fishnet.geom)
    WHERE postcodes.id = '42395'
)
-- 最终筛选掉权重为0的行，确保结果干净
SELECT DISTINCT vrgid, Weights, geom
FROM weight_calculations
WHERE Weights != 0;

方案二：保留每个vrgid的最高优先级CASE结果 #

这个思路更通用，它会给每个vrgid的所有候选行按CASE的优先级排序（第一条CASE优先级最高，依次递减），然后只保留每个vrgid的第一行，从根本上消除重复。

WITH ranked_weights AS (
    SELECT 
        fishnet.gid AS vrgid, 
        fishnet.geom AS geom,
        -- 原CASE逻辑保持不变
        CASE 
            WHEN st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0 THEN 0.95 :: numeric / counts.urbancells
            WHEN st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells = 0 THEN 1.00 :: numeric / counts.urbancells
            WHEN NOT st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0 THEN 0.05 :: numeric / counts.nonurbancells
            ELSE 0 
        END AS Weights,
        -- 按CASE条件的优先级给每行排序：第一条=1（最高），第二条=2，第三条=3，其他=4
        ROW_NUMBER() OVER (
            PARTITION BY fishnet.gid 
            ORDER BY 
                CASE 
                    WHEN st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0 THEN 1
                    WHEN st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells = 0 THEN 2
                    WHEN NOT st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0 THEN 3
                    ELSE 4
                END
        ) AS rn
    FROM vrg.urban_nonurban_count_new AS counts
    INNER JOIN vrg.gfk_2016_id_5_digit_pcd_areas2013_projected AS postcodes 
        ON postcodes.id = counts.postid
    RIGHT OUTER JOIN vrg.rdsid_86_quadgrid_centroids AS centroids 
        ON st_contains(postcodes.geom, centroids.geom)
    LEFT OUTER JOIN vrg.rdsid86_quadgrid AS fishnet 
        ON fishnet.gid = centroids.gid
    RIGHT OUTER JOIN vrg.rdsid86_katrisk_poly_projected AS urban 
        ON st_intersects(urban.geom, fishnet.geom)
    WHERE postcodes.id = '42395'
)
-- 只保留每个vrgid的最高优先级行，同时排除权重为0的无效行
SELECT vrgid, Weights, geom
FROM ranked_weights
WHERE rn = 1 AND Weights != 0;

额外说明： #

我注意到你原查询里的第三条CASE条件写得有点问题：WHEN Not st_intersects(centroids.geom, urban.geom) = fishnet.gid :: boolean，这里把空间函数的布尔结果和fishnet.gid转布尔值比较是不合理的，应该是笔误，我在上面的方案里已经修正为WHEN NOT st_intersects(centroids.geom, urban.geom) AND counts.nonurbancells != 0，如果我的理解不对，你可以根据实际需求调整这部分逻辑。

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