求助：SQL实现按1/2/3房间条件分列展示排序后的公寓数据

需求说明 #

需要统计2020年1月1日起，满足1、2、3房间条件的公寓占比，将三类结果分别作为列展示，每列内按占比降序排列，最终呈现横向对比格式。

原查询优化点 #

原查询存在冗余逻辑，可简化：

• (case when min_rooms is null and max_rooms is null then 0 else 1 end) = 1 可简化为 NOT (min_rooms IS NULL AND max_rooms IS NULL)
• (1 between rooms and rooms) 等价于 rooms = 1，结合原逻辑是判断房间数范围包含目标值，应保留 1 between min_rooms and max_rooms 的写法

最终SQL实现 #

WITH room_totals AS (
    -- 统计1、2、3房间条件下的总记录数，用于计算占比
    SELECT
        room_count,
        COUNT(*) AS total_records
    FROM (
        SELECT
            CASE
                WHEN 1 BETWEEN min_rooms AND max_rooms THEN 1
                WHEN 2 BETWEEN min_rooms AND max_rooms THEN 2
                WHEN 3 BETWEEN min_rooms AND max_rooms THEN 3
            END AS room_count
        FROM propdata
        WHERE
            root_tstamp >= '2020-01-01'
            AND NOT (min_rooms IS NULL AND max_rooms IS NULL)
    ) AS filtered_rooms
    WHERE room_count IN (1, 2, 3)
    GROUP BY room_count
),
apartment_rankings AS (
    -- 计算每个公寓在各房间条件下的占比，并按占比降序排名
    SELECT
        apartments,
        -- 标记公寓所属的房间条件
        CASE WHEN 1 BETWEEN min_rooms AND max_rooms THEN 1 END AS room_1,
        CASE WHEN 2 BETWEEN min_rooms AND max_rooms THEN 2 END AS room_2,
        CASE WHEN 3 BETWEEN min_rooms AND max_rooms THEN 3 END AS room_3,
        -- 计算各房间条件下的占比（转换为百分比并保留三位小数）
        ROUND(COUNT(*) * 100 / rt1.total_records, 3) AS ratio_1,
        ROUND(COUNT(*) * 100 / rt2.total_records, 3) AS ratio_2,
        ROUND(COUNT(*) * 100 / rt3.total_records, 3) AS ratio_3,
        -- 按占比降序给每个房间条件的公寓排名
        ROW_NUMBER() OVER (PARTITION BY room_1 ORDER BY ratio_1 DESC) AS rank_1,
        ROW_NUMBER() OVER (PARTITION BY room_2 ORDER BY ratio_2 DESC) AS rank_2,
        ROW_NUMBER() OVER (PARTITION BY room_3 ORDER BY ratio_3 DESC) AS rank_3
    FROM propdata p
    -- 关联各房间条件的总记录数
    CROSS JOIN room_totals rt1 WHERE rt1.room_count = 1
    CROSS JOIN room_totals rt2 WHERE rt2.room_count = 2
    CROSS JOIN room_totals rt3 WHERE rt3.room_count = 3
    WHERE
        root_tstamp >= '2020-01-01'
        AND NOT (min_rooms IS NULL AND max_rooms IS NULL)
        AND (1 BETWEEN min_rooms AND max_rooms OR 2 BETWEEN min_rooms AND max_rooms OR 3 BETWEEN min_rooms AND max_rooms)
    GROUP BY apartments, rt1.total_records, rt2.total_records, rt3.total_records
)
-- 按排名横向拼接三类房间条件的结果
SELECT
    ar1.apartments AS "APARTMENTS - 1ROOM",
    ar1.ratio_1 AS "ALL",
    ar2.apartments AS "APARTMENT - 2 ROOM",
    ar2.ratio_2 AS "ALL",
    ar3.apartments AS "APARTMENTS - 3 ROOM",
    ar3.ratio_3 AS "ALL"
FROM apartment_rankings ar1
LEFT JOIN apartment_rankings ar2 ON ar1.rank_1 = ar2.rank_2
LEFT JOIN apartment_rankings ar3 ON ar1.rank_1 = ar3.rank_3
WHERE ar1.rank_1 IS NOT NULL
ORDER BY ar1.rank_1;

逻辑说明 #

1. room_totals CTE：先筛选出符合时间和房间数范围有效条件的记录，标记出属于1、2、3房间的分组，统计每个房间条件的总记录数，为后续计算占比提供分母。
2. apartment_rankings CTE：统计每个公寓在各房间条件下的记录数，计算占比（转换为百分比），同时通过ROW_NUMBER()窗口函数给每个房间条件下的公寓按占比降序排名，确保排名相同的行对应各房间条件下的TopN公寓。
3. 最终查询：通过自连接按排名拼接三类房间条件的结果，实现横向对比展示，LEFT JOIN保证即使某类房间条件下的公寓数量更少，也能正常显示其余列的结果。

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