如何通过关系计数将结果分组 [英] How to group results by count of relationships

问题描述

给定表，配置文件和会员身份，其中配置文件具有许多成员身份，如何根据以下内容查询配置文件

Given tables, Profiles, and Memberships where a profile has many memberships, how do I query profiles based on the number of memberships?

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

For example I want to get the number of profiles with 2 memberships. I can get the number of profiles for each membership with:

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
...

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

But how do I group and sum the counts to get the query to return results like:

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

或者甚至只是一个查询，返回单个值 n

Or even just a query for a single value of n that would return

profiles_with_2_memberships
___________________________
28

推荐答案

我没有您的示例数据，但是我只是在这里用一个表重新创建了该方案： 演示

I don't have your sample data, but I just recreated the scenario here with a single table : Demo

您可以左键使用 generate_series（）进行计数，对于 n 成员资格缺失计数得到零。如果您不希望为零，请使用第二个查询。

You could LEFT JOIN the counts with generate_series() and get zeroes for missing count of n memberships. If you don't want zeros, just use the second query.

Query1

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;

Query2

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;

这篇关于如何通过关系计数将结果分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！