从组中获取最大计数
我无法在sql.Below中获取组函数的输出，这是表的细节
我有1个表格名称“检查”有2列pid，cid
Name Null? Type
----------------------------------------- -------- ----------------------------
PID VARCHAR2(20)
CID VARCHAR2(20)
以下是可用的行
select * from checks;
PID CID
-------------------- --------------------
p1 c1
p1 c1
p1 c2
p1 c2
p1 c2
p2 c1
p2 c1
p2 c1
p2 c1
p2 c1
p2 c1
p2 c2
p2 c2
p2 c2
p2 c2
p2 c2
P代表参与者，c代表类别
题
我需要知道哪个参与者参加了多于一个类别，哪类参与者参与最多（对于每个参与者）
预期结果：
pid cid count(cid)
--- --- -----------
p1 c2 3
p2 c1 6
假设一个支持窗口函数和CTE的数据库系统（你没有指定一个，但我怀疑Oracle），我会写：
;With Groups as (
select pid,cid,COUNT(*) as cnt from checks group by pid,cid
), Ordered as (
select pid,cid,cnt,
ROW_NUMBER() OVER (PARTITION BY pid ORDER BY cnt desc) as rn,
COUNT(*) OVER (PARTITION BY pid) as multi
from Groups
)
select pid,cid,cnt
from Ordered
where rn = 1 and multi > 1
第一个CTE（ Groups ）只是找到每个唯一的cid,pid组合的计数。 第二个CTE（已Ordered ）根据计数为这些结果分配行编号 - 最高计数分配的行编号为1.我们还计算了为每个pid生成的总行数。
最后，我们选择行号为1（最高计数）的那些行，并且为同一个pid获得多个结果。
这是一个可供玩的Oracle小提琴。 这里有一个SQL Server版本（并感谢Andriy M生产Oracle版本）
这会给你一些基本的想法：
结果如下所示。 此外，由于p1参与了多个类别，所以当我们使用以下参数时，p1将与每个新类别一起出现：“按PID组，CID”
一步步：
首先，获取每个行(PID, CID)的计数。 这很简单：
SELECT
PID,
CID,
COUNT(*) AS cnt
FROM checks
GROUP BY
PID,
CID
你得到这个结果集为你的例子：
PID CID cnt
--- --- ---
p1 c1 2
p1 c2 3
p2 c1 6
p2 c2 5
现在，输入COUNT(*) OVER (PARTITION BY PID)返回每个人的类别数量：
SELECT
PID,
CID,
COUNT(*) AS cnt,
COUNT(*) OVER (PARTITION BY PID) AS cat_cnt
FROM checks
GROUP BY
PID,
CID
OVER子句将“正常”聚合函数COUNT()转换为窗口聚合函数。 这使得COUNT(*)在分组行集合上运行，而不是源集合。 因此， COUNT(*) OVER ...在这种情况下按每个PID计数行，这对于我们而言具有每人类别计数的含义。 这是更新的结果集：
PID CID cnt cnt_cat
--- --- --- -------
p1 c1 2 2
p1 c2 3 2
p2 c1 6 2
p2 c2 5 2
还有一件事是对每个PID的cnt值进行排序。 这可能是棘手的，因为可能会有关系在最高计数。 如果您总是希望每个PID只有一行，并且对于哪个CID, cnt完全无差别CID, cnt将会出现CID, cnt ，您可以像这样修改查询：
SELECT
PID,
CID,
COUNT(*) AS cnt,
COUNT(*) OVER (PARTITION BY PID) AS cat_cnt,
ROW_NUMBER() OVER (PARTITION BY PID ORDER BY COUNT(*) DESC) AS rn
FROM checks
GROUP BY
PID,
CID
这就是结果集的样子：
PID CID cnt cnt_cat rn
--- --- --- ------- --
p1 c1 2 2 2
p1 c2 3 2 1
p2 c1 6 2 1
p2 c2 5 2 2
此时，结果包含产生最终输出所需的所有数据，您只需在cnt_cat和rn上进行过滤。 但是，你不能直接这样做。 相反，将最后一个查询用作派生表，无论是WITH表表达式还是“常规”子查询。 下面是一个使用WITH的例子：
WITH grouped AS (
SELECT
PID,
CID,
COUNT(*) AS cnt,
COUNT(*) OVER (PARTITION BY PID) AS cat_cnt,
ROW_NUMBER() OVER (PARTITION BY PID ORDER BY COUNT(*) DESC) AS rn
FROM checks
GROUP BY
PID,
CID
)
SELECT PID, CID, cnt
FROM grouped
WHERE cat_cnt > 1
AND rn = 1
;这是一个SQL Fiddle演示（使用Oracle）：http://sqlfiddle.com/#!4/cd62d/8
为了扩大更多的排名部分，如果你仍然想要返回一个CID, cnt每个PID CID, cnt ，但宁愿有更多的控制什么行应该被确定为“赢家”，你需要添加一个领带-breaker到排名函数的ORDER BY子句。 作为一个例子，你可以修改原始表达式，
ROW_NUMBER() OVER (PARTITION BY PID ORDER BY COUNT(*) DESC) AS rn与这一个：
ROW_NUMBER() OVER (PARTITION BY PID ORDER BY COUNT(*) DESC, CID) AS rn 也就是说，决胜局是CID ，并且两个或两个以上的CID都是顶级的，这是在其他人获胜之前排序的。
不过，您可能想要决定返回每个PID所有最高计数。 在这种情况下，使用RANK()或DENSE_RANK()而不是ROW_NUMBER() （并且不使用平局），即像这样：
RANK() OVER (PARTITION BY PID ORDER BY COUNT(*) DESC) AS rn上一篇: Get Max Count from Group by
下一篇: Writing a game controller driver for some hardware connected via USB