sql - 如何按关系计数对结果进行分组

Question

给定表、Profiles 和 Memberships，其中一个配置文件有很多成员，我如何根据成员数量查询配置文件？

例如，我想获取具有 2 个成员(member)资格的个人资料的数量。我可以通过以下方式获取每个成员的个人资料数量:

SELECT "memberships"."profile_id", COUNT("profiles"."id") AS "membership_count"
FROM "profiles"
INNER JOIN "memberships" on "profiles"."id" = "memberships"."profile_id"
GROUP BY "memberships"."profile_id"

返回结果如下

profile_id | membership_count
_____________________________
1            2
2            5
3            2
...

但是我如何对计数进行分组和求和以使查询返回如下结果:

n | profiles_with_n_memberships
_____________________________
1   36
2   28
3   29
...

或者甚至只是查询将返回的 n 的单个值

profiles_with_2_memberships
___________________________
28

Answer 1

我没有你的样本数据，但我只是用一个表在这里重新创建了这个场景:Demo

您可以使用 generate_series() LEFT JOIN 计数，并为缺少的 n 成员计数取零。如果您不想要零，只需使用第二个查询。

查询 1

WITH c
AS (
    SELECT profile_id
        ,count(*) ct
    FROM Table1
    GROUP BY profile_id
    )
    ,m
AS (
    SELECT MAX(ct) AS max_ct
    FROM c
    )
SELECT n
    ,COUNT(c.profile_id)
FROM m
CROSS JOIN generate_series(1, m.max_ct) AS i(n)
LEFT JOIN c ON c.ct = i.n
GROUP BY n
ORDER BY n;

查询2

WITH c
AS (
    SELECT profile_id
        ,count(*) ct
    FROM Table1
    GROUP BY profile_id
    )
SELECT ct
      ,COUNT(*)
 FROM c 
GROUP BY ct
ORDER BY ct;