postgresql - 如何在 Postgres 中找到一个月的最大选项数？

Question

我想找出我的每个用户在给定月份的最大费率选项数。这是我的费率表的样子：

Member  | Month     |   Rate
Joe     | Jan       |   1
Joe     | Jan       |   2
Joe     | Jan       |   3
Joe     | Feb       |   1
Joe     | Feb       |   2
Joe     | Feb       |   2
Joe     | Mar       |   1
Joe     | Mar       |   2
Joe     | Mar       |   2
Max     | Jan       |   1
Max     | Jan       |   1
Max     | Jan       |   1
Max     | Feb       |   2
Max     | Feb       |   2
Max     | Feb       |   2
Max     | Mar       |   3
Max     | Mar       |   3
Max     | Mar       |   3
Ben     | Jan       |   1
Ben     | Jan       |   2
Ben     | Jan       |   2
Ben     | Feb       |   1
Ben     | Feb       |   1
Ben     | Feb       |   1
Ben     | Mar       |   1
Ben     | Mar       |   1
Ben     | Mar       |   1

乔在一月份有可供他使用的费率选项 [1,2,3]。乔在二月和三月只有两个 [1,2]。对于每个用户，我想显示一个月内可用的最大费率数（与所有月份相比）。结果表应如下所示：

Member | Max rates in one month
Joe    | 3
Max    | 1
Ben    | 2

我将如何编写此查询？

标签： postgresql

Answer 1

您可以通过一些聚合来做到这一点：

SELECT Member, MAX(rate_count) as "Max rates in one month"
FROM 
    (SELECT Member, Month, COUNT(DISTINCT RATE) rate_count FROM yourtable GROUP BY Member, Month) dt
GROUP BY Member;