PostgreSQL聚合统计需求：按行政区计数符合条件的多账户地址组

嘿，这个需求其实用PostgreSQL的分组聚合就能轻松实现，我帮你拆解清楚思路，再给你直接能用的代码：

首先明确核心要求：我们要先找出同一经纬度+行政区（borough）下的所有账户全是inactive状态，而且至少有一个账户还在订阅HBO的地址组，最后按行政区统计这些符合条件的组有多少个。

实现步骤拆解 #

1. 先分组标识地址组：把latitude、longitude、borough作为分组维度，因为这三个字段合起来才能代表一个“地址组”（比如同一栋公寓的不同单元，经纬度相同、行政区相同，但门牌号不同）。
2. 过滤符合条件的组：
   • 要确保组里所有账户都是inactive：用bool_and(status = 'inactive')就很合适——只有当组内每一行的状态都是inactive时，这个表达式才返回true。如果你的status是布尔类型（比如active字段，true表示活跃），那改成bool_and(NOT active)就行。
   • 要确保组里至少有一个人订了HBO：用bool_or(hbo_subscribed)，只要组里有一个订阅的，这个表达式就返回true。如果你的HBO标识是字符串（比如'yes'/'no'），就改成bool_or(hbo_subscribed = 'yes')。
3. 最后按行政区统计数量：把筛选出来的合格地址组，再按borough分组计数就行。

直接可用的SQL代码 #

假设你的表名叫accounts，字段分别是account_id（唯一ID）、latitude、longitude、borough、status（取值'active'/'inactive'）、hbo_subscribed（布尔类型或字符串）。

如果HBO标识是布尔类型（true=订阅）

SELECT 
    borough,
    COUNT(*) AS "Count"
FROM (
    SELECT 
        latitude,
        longitude,
        borough
    FROM accounts
    GROUP BY latitude, longitude, borough
    HAVING 
        bool_and(status = 'inactive') -- 组内所有账户都是inactive
        AND bool_or(hbo_subscribed)   -- 至少一个账户订阅HBO
) AS valid_groups
GROUP BY borough
ORDER BY "Count" DESC;

如果HBO标识是字符串类型（比如'yes'=订阅）

SELECT 
    borough,
    COUNT(*) AS "Count"
FROM (
    SELECT 
        latitude,
        longitude,
        borough
    FROM accounts
    GROUP BY latitude, longitude, borough
    HAVING 
        bool_and(status = 'inactive') 
        AND bool_or(hbo_subscribed = 'yes')
) AS valid_groups
GROUP BY borough
ORDER BY "Count" DESC;

代码为啥这么写？ #

• 内层的子查询valid_groups先把所有符合要求的地址组筛选出来：通过GROUP BY聚合经纬度和行政区，再用HAVING子句卡两个核心条件。
  • bool_and是PostgreSQL里的聚合函数，专门用来判断组内所有行是否都满足某个条件，完美对应“全inactive”的要求。
  • bool_or则是判断组内是否至少有一行满足条件，正好对应“至少一个订阅HBO”。
• 外层查询就简单了，把筛选后的组按行政区分组，用COUNT(*)数每个区有多少个合格的组就行。

运行后会得到类似这样的结果 #

borough | Count
---------+-------
 Bronx   |    50
 Queens  |    12

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