sql - 计算 group by 中具有最大值的行

Question

我有一张这样的表:

| ID  | CATEGORY | PRIORITY |
| --- | -------- | -------- |
| 1   | dogs     | 1        |
| 2   | dogs     | 2        |
| 3   | cats     | 1        |
| 4   | cats     | 2        |
| 5   | cats     | 2        |

我想要得到的是一个分组的结果集，如下所示:

| CATEGORY | HIGHEST_PRIORITY | NMB_ROWS_WITH_HIGHEST_PRIO |
| -------- | ---------------- | -------------------------- |
| dogs     | 2                | 1                          |
| cats     | 2                | 2                          | 

我想象以下形式的查询可以工作:

SELECT CATEGORY,
  MAX(PRIORITY),  -- Highest Priority of Category
  COUNT(MAX(PRIORITY)) -- Count how many rows within a category have the highest priority
FROM MY_TABLE
GROUP BY CATEGORY;

但不能用 COUNT(MAX(PRIORITY)) 计算行数组内这样。有没有办法在不使用子查询的情况下实现这一目标？
(我使用 Oracle 12c 数据库)

Answer 1

窗口函数在这里派上用场。我们可以先对表进行一次迭代，并为每个类别生成最高优先级值。然后，汇总并有条件地计算每个优先级等于最大优先级的次数。

WITH cte AS (
    SELECT t.*,
        MAX(PRIORITY) OVER (PARTITION BY CATEGORY) MAX_PRIORITY
    FROM MY_TABLE t
)

SELECT
    CATEGORY,
    MAX(PRIORITY) AS HIGHEST_PRIORITY,
    COUNT(CASE WHEN PRIORITY = MAX_PRIORITY THEN 1 END) AS NMB_ROWS_WITH_HIGHEST_PRIO
FROM CTE
GROUP BY
    CATEGORY;

Demo