PostgreSQL如何横向展示按区域统计的各房价区间房产数量

使用条件聚合逻辑即可实现预期的透视表效果，全数据库兼容写法如下：

SELECT 
    ZONE AS Zone,
    COUNT(CASE WHEN PRICE < 150000 THEN 1 END) AS `0-149k`,
    COUNT(CASE WHEN PRICE >= 150000 AND PRICE <= 300000 THEN 1 END) AS `150-300k`
FROM `MY TABLE`
GROUP BY ZONE

逻辑说明 #

• 先通过GROUP BY ZONE将所有数据按区域分组，每个区域对应结果中的一行
• 在每个分组内通过CASE WHEN筛选对应价格区间的记录，COUNT会自动忽略不符合条件返回的NULL值，直接得到对应区间的房产计数
• 需要新增价格区间时，仅需追加对应的CASE WHEN统计列即可

特定数据库简化写法 #

• MySQL支持用IF函数简化判断：

SELECT 
    ZONE AS Zone,
    COUNT(IF(PRICE < 150000, 1, NULL)) AS `0-149k`,
    COUNT(IF(PRICE BETWEEN 150000 AND 300000, 1, NULL)) AS `150-300k`
FROM `MY TABLE`
GROUP BY ZONE

• PostgreSQL、SQLite支持用FILTER语法更直观：

SELECT 
    ZONE AS Zone,
    COUNT(*) FILTER (WHERE PRICE < 150000) AS "0-149k",
    COUNT(*) FILTER (WHERE PRICE BETWEEN 150000 AND 300000) AS "150-300k"
FROM "MY TABLE"
GROUP BY ZONE

内容的提问来源于stack exchange，提问作者David López