PostgreSQL 如何合并多统计逻辑到单条FILTER聚合查询中

现有表结构 #

现有property_table数据表结构及测试数据如下：

PROPERTY_ID OWNER_NAME  ISFORSALE   ISFORECLOSURE   ISFORRENT   CITY
    100         AA          1           0               1       abc
    101         AA          1           0               0       xyz
    102         BB          0           1               1       abc
    103         BB          1           0               0       abc
    104         BB          0           0               1       abc
    105         BB          1           0               1       abc
    106         CC          1           0               0       xyz     

已有查询逻辑 #

已实现基础指标统计，使用COUNT(*) FILTER语法分别统计待售、止赎、可租房产数量：

SELECT 
    COUNT(*) FILTER (WHERE ISFORSALE = 1) AS SALE_CNT,
    COUNT(*) FILTER (WHERE ISFORECLOSURE = 1) AS FORECLOSURE_CNT,
    COUNT(*) FILTER (WHERE ISFORRENT = 1) AS RENT_CNT
FROM property_table

新增需求 #

需要新增MUTI_PROP_OWNERS字段，统计持有超过1套待售房产的业主总数量，对应独立查询逻辑如下：

SELECT count(*) from (
SELECT OWNER_NAME, COUNT(*) AS cnt1
FROM property_table 
WHERE ISFORSALE = 1
GROUP BY OWNER_NAME
HAVING COUNT(*) > 1
) t;

合并查询实现 #

可以直接将两个查询逻辑合并，无需分开执行。最简单的实现方式是将多套待售业主统计作为标量子查询嵌入原有SELECT语句中，完整查询代码如下：

SELECT 
    COUNT(*) FILTER (WHERE ISFORSALE = 1) AS SALE_CNT,
    COUNT(*) FILTER (WHERE ISFORECLOSURE = 1) AS FORECLOSURE_CNT,
    COUNT(*) FILTER (WHERE ISFORRENT = 1) AS RENT_CNT,
    -- 嵌入多套待售业主统计子查询
    (
        SELECT count(*) 
        FROM (
            SELECT OWNER_NAME
            FROM property_table 
            WHERE ISFORSALE = 1
            GROUP BY OWNER_NAME
            HAVING COUNT(*) > 1
        ) t
    ) AS MUTI_PROP_OWNERS
FROM property_table;

执行后返回结果完全匹配要求的格式：

SALE_CNT | FORECLOSURE_CNT | RENT_CNT | MUTI_PROP_OWNERS
________________________________________________________    
    5    |       1         |    4     |      2  

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