PARTITION BY с и без KEEP в Oracle

Я столкнулся с двумя запросами, которые, похоже, имеют тот же результат: применение агрегатной функции в разделе.

Мне интересно, есть ли разница между этими двумя запросами:

SELECT empno,
   deptno,
   sal,
   MIN(sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

SELECT empno,
   deptno,
   sal,
   MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

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

Ответ 1

MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno)

Оператор может быть рассмотрен в (грубом) порядке справа налево:

OVER (PARTITION BY deptno) означает разбиение строк на отдельные группы deptno; затем
ORDER BY sal означает, что для каждого раздела упорядочивать строки на sal (неявно используя ASC окончательный порядок); затем
KEEP (DENSE_RANK FIRST означает дать "олимпийский" ранг упорядоченным строкам для каждого раздела (строки с одинаковыми значениями для столбцов упорядочения будут иметь одинаковый ранг) и отбросить все строки, которые не ранжируются первыми; и наконец
MIN(sal) для остальных строк каждого раздела, верните минимальную зарплату.

В этом случае MIN и DENSE_RANK FIRST работают в столбце sal, поэтому сделают то же самое, а KEEP (DENSE_RANK FIRST ORDER BY sal) будет избыточным.

Однако, если вы используете другой столбец для минимума, вы можете увидеть эффекты:

SQL Fiddle

Настройка схемы Oracle 11g R2:

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;

Запрос 1:

SELECT DISTINCT
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal,
  MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_sal_first_sal,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal,
  MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal,
  MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal,
  deptno
FROM test

Результаты:

| MIN_SAL_FIRST_SAL | MAX_SAL_FIRST_SAL | MIN_NAME_FIRST_SAL | MAX_NAME_FIRST_SAL | MIN_NAME_LAST_SAL | MAX_NAME_LAST_SAL | DEPTNO |
|-------------------|-------------------|--------------------|--------------------|-------------------|-------------------|--------|
|                 1 |                 1 |                  a |                  c |                 e |                 f |      1 |
|                 4 |                 4 |                  g |                  h |                 i |                 j |      2 |

Ответ 2

В вашем примере нет никакой разницы, потому что ваш агрегат находится в том же столбце, который вы сортируете. Реальная точка/мощность "KEEP" - это когда вы объединяете и сортируете по разным столбцам. Например (заимствуя таблицу "тест" из другого ответа)...

SELECT deptno,  min(name) keep ( dense_rank first order by sal desc, name  ) ,
max(sal)
FROM test
group by deptno

;

Этот запрос получает имя человека с самой высокой зарплатой в каждом отделе. Рассмотрим альтернативу без предложения "KEEP":

SELECT deptno, name, sal
FROM test t
WHERE not exists ( SELECT 'person with higher salary in same department'
                                            FROM test t2  
                                            WHERE t2.deptno = t.deptno
                                            and ((  t2.sal > t.sal )
                                            OR ( t2.sal = t.sal AND t2.name < t.name ) ) )

Предложение KEEP проще и эффективнее (только 3 согласованных получает против 34 для альтернативы, в этом простом примере).

Ответ 3

Это также может быть полезно, если вы заказываете на основе двух столбцов и выбираете один или оба столбца.

CREATE TABLE test (name, sal, deptno) AS
SELECT 'adam', 100, 1 FROM DUAL
UNION ALL SELECT 'bravo', 500, 1 FROM DUAL
UNION ALL SELECT 'coy', 456, 1 FROM DUAL
UNION ALL SELECT 'david', 50, 1 FROM DUAL
UNION ALL SELECT 'ethan', 50, 1 FROM DUAL
UNION ALL SELECT 'feral', 300, 1 FROM DUAL;

Теперь вы хотите выбрать сотрудника с самой низкой зарплатой, а также с зарплатой человека. Условие заключается в том, что если у двух сотрудников одинаковый самый низкий заработок, выберите тот, чье имя будет первым в алфавитном порядке.

  select o.deptno
,min(o.sal) keep 
  (dense_rank first order by o.sal, o.name) least_salary
,min(o.name) keep 
  (dense_rank first order by o.sal, o.name) least_salary_person
 from test o
  group by 
 o.deptno;

OUTPUT:

DEPTNO LEAST_SALARY LEAST_SALARY_PERSON

1 50 давид