Фильтрация строк из таблицы Oracle SQL, используя максимальное значение столбца

Question

Я изучаю SQL оракула, и у меня есть таблица, подобная следующей:

+--------+--------+------------------------+
| name   | nation | count                  |
+--------+--------+------------------------+
| Ruben  | UK     | 2                      |
| Ruben  | EEUU   | 16                     |
| Cesar  | UK     | 21                     |
| Cesar  | EEUU   | 12                     |
| Cesar  | FRANCE | 4                      |
| John   | FRANCE | 3                      |
| John   | UK     | 7                      |
| ....   | ..     | ..                     |
+--------+--------+------------------------+

Таблица выше представляет собой встроенное представление, которое я создал с помощью запроса. Как видите, таблицы группируются по имени и по нации и делают некоторые подсчеты. Я хочу отфильтровать таблицу, используя столбец count, чтобы получить что-то вроде этого:

+--------+--------+
| name   | nation |
+--------+--------+
| Ruben  | EEUU   |
| Cesar  | UK     |
| John   | UK     |
| ....   | ..     |
+--------+--------+  

Как видите, для каждого имени я хочу выбрать нацию на основе подсчета.

Answer 1

Использование keep аналитическое ключевое слово:

select name, min(nation) keep (dense_rank last order by cnt)
from (select name, nation, count(*) as cnt
      from /* your data source */
      group by name, nation)
group by name

1. min(nation) - мин в этом случае не имеет смысла, но вы должны сохранить его (не работает без)
2. keep - сохраняет только один результат nation
3. dense_rank last говорит, чтобы забрать последний элемент
4. order by cnt говорит, как определить порядок элементов

В конце концов, это сделает для каждого имени нацию с наибольшим количеством. Тот же результат может быть достигнут с

select name, min(nation) keep (dense_rank first order by cnt desc)

Answer 2

Вы можете сделать это с другим уровнем внутреннего запроса. Что-то вроде:

select name, nation
from (
  select name, nation, cnt,
    row_number() over (partition by name, nation order by cnt desc) as rn
  from (
    select name, nation, count(*) as cnt
    from <your table>
  )
)
where rn = 1;

row_number() analytic добавляет псевдостолбец к самому внутреннему запросу, который ранжирует строку с наибольшим количеством как 1 для каждого имени / нации; самый внешний запрос затем смотрит только на тех, кто занял первое место.

Вам нужно решить, что делать со связями - если две строки нации / имени имеют одинаковое количество, вы можете показать обе, используя rank() вместо row_number(); или вы можете решить, какой из них выбрать, добавив еще один столбец к order by пункт. На данный момент, если есть два одинаковых, он покажет вам только один из них - и то, что он показывает, довольно произвольно.