sql - 按分组计算具有最大值的行

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 数据库）

标签： sqloracle

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;

演示