postgresql 如何在PostgreSQL中选择最大计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30842890/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select max of count in PostgreSQL
提问by thelastray
I have table in PostgreSQL with the following schema:
我在 PostgreSQL 中有具有以下架构的表：
Category | Type
------------+---------
A | 0
C | 11
B | 5
D | 1
D | 0
F | 2
E | 11
E | 9
. | .
. | .
How can I select category wise maximum occurrence of type? The following give me all:
如何选择类型的类别最大出现次数？以下给我所有：
SELECT
category,
type,
COUNT(*)
FROM
table
GROUP BY
category,
type
ORDER BY
category,
count
DESC
My expected result is something like this:
我的预期结果是这样的：
Cat |Type |Count
--------+-------+------A |0 |5
B |5 |30
C |2 |20
D |3 |10
猫 |类型 |计数
--------+-------+------一个 |0 |5
乙 |5 |30
C |2 |20
D |3 |10
That is the type with max occurrence in each category with count of that type.
这是在每个类别中出现次数最多的类型，该类型的计数。
回答by Giorgos Betsos
You can use the following query:
您可以使用以下查询：
SELECT category, type, cnt
FROM (
SELECT category, type, cnt,
RANK() OVER (PARTITION BY category
ORDER BY cnt DESC) AS rn
FROM (
SELECT category, type, COUNT(type) AS cnt
FROM mytable
GROUP BY category, type ) t
) s
WHERE s.rn = 1
The above query uses your own query as posted in the OP and applies RANK()windowed function to it. Using RANK()we can specify all records coming from the initial query having the greatest COUNT(type)value.
上面的查询使用您自己在 OP 中发布的查询，并将RANK()窗口函数应用于它。使用RANK()我们可以指定来自具有COUNT(type)最大值的初始查询的所有记录。
Note:If there are more than one types having the maximum number of occurrences for a specific category, then allof them will be returned by the above query, as a consequence of using RANK.
注意：如果有多个类型具有特定类别的最大出现次数，则上述查询将返回所有类型，这是使用RANK.
回答by Gordon Linoff
If I understand correctly, you can use window functions:
如果我理解正确，您可以使用窗口函数：
SELECT category, type, cnt
FROM (SELECT category, type, COUNT(*) as cnt,
ROW_NUMBER() OVER (PARTITION BY type ORDER BY COUNT(*) DESC) as seqnum
FROM table
GROUP BY category, type
) ct
WHERE seqnum = 1;
回答by acesargl
SELECT
category,
type,
COUNT(*)
FROM
table
GROUP BY
category,
type
HAVING
COUNT(*) = (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM A GROUP BY A) AS Q)
EDITED: I apologize to readers,
编辑：我向读者道歉，
COUNT(*) = (SELECT MAX(COUNT(*)) FROM table GROUP BY category,type)
is the ORACLE version, postgresql version is:
是ORACLE版本，postgresql版本是：
COUNT(*) = (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM A GROUP BY A) AS Q)
回答by Zineb SLAM
SELECT category , MAX (Occurence) FROM (SELECT t.category as category , Count(*) AS Occurence FROM table t);
SELECT category , MAX (Occurence) FROM (SELECT t.category as category , Count(*) AS Occurence FROM table t);
回答by Zineb SLAM
SELECT
category,
type,
COUNT(*) AS count
FROM
table
GROUP BY
category,
type
ORDER BY
category ASC
