1 select a.empno, a.ename, a.sal,

2 (select case when a.empno = min(b.empno) then sum(b.sal)

3 else sum(-b.sal)

4 end

5 from emp b

6 where b.empno <= a.empno

7 and b.deptno = a.deptno ) as rnk

8 from emp a

9 where a.deptno = 10

Обсуждение

Решения аналогичны рассматриваемым в рецепте Вычисление текущей суммы . Единственное отличие в том, что все, кроме первого (точкой отсчета должно быть первое значение SAL в DEPTNO 10), значения столбца SAL возвращаются как отрицательные значения.

Вычисление моды Задача

Требуется найти моду (для тех, кто забыл, мода (mode) в математике -это наиболее часто встречающийся элемент рассматриваемого множества данных) столбца значений. Например, поставлена задача найти моду заработных плат 20-го отдела (DEPTNO 20). Для следующего набора заработных плат:

select sal

from emp

where deptno = 20

order by sal

1100

2975 3000 3000

мода равна 3000.

Решение

DB2 и SQL Server

С помощью ранжирующей функции DENSE RANK ранжируйте счетчики заработных плат, что поможет найти моду:

1 select sal

2 from (

3 select sal,

4 dense rank()over(order by cnt desc) as rnk

5 from (

6 select sal, count(*) as cnt

8 from emp

9 where deptno = 20

10 group by sal

11 ) x

12 ) y

13 where rnk = 1

Oracle

Пользователям Oracle 8i Database подойдет решение для DB2. В Oracle 9i Database и более поздних версиях для поиска моды столбца SAL можно применять расширение KEEP к агрегатной функции MAX. Одно важное замечание: если есть одинаковые значения счетчиков, т. е. модой являются несколько строк, решение с использованием KEEP возвратит только одну из них, и это будет строка с наибольшей заработной платой. Если необходимо увидеть все моды (если их несколько), придется изменить это решение или просто использовать представленное выше решение для DB2. В данном случае, поскольку 3000 - мода столбца SAL для DEPTNO 20, а также наибольшее значение SAL, это решение подходит:

1 select max(sal)

2 keep(dense rank first order by cnt desc) sal

3 from (

4 select sal, count(*) cnt

5 from emp

6 where deptno=20

7 group by sal

MySQL и PostgreSQL

Для поиска моды используйте подзапрос:

1 select sal

2 from emp

3 where deptno = 20

4 group by sal

5 having count(*) >= all ( select count(*)

6 from emp

7 where deptno = 20

8 group by sal )

Обсуждение

DB2 и SQL Server

Вложенное представление Х возвращает каждое значение столбца SAL и число раз, сколько это значение встречается в столбце. Вложенное представление Y использует ранжирующую функцию DENSE RANK

(которая допускает одинаковые значения счетчиков) для сортировки результатов. Результаты ранжируются на основании того, сколько раз встречается каждое из значений SAL, как показано ниже:

1 select sal,

2 dense rank()over(order by cnt desc) as rnk

3 from (

4 select sal,count(*) as cnt

5 from emp

6 where deptno = 20

7 group by sal

8 ) x

SAL RNK

3000 1

800 2

1100 2

2975 2

Самая внешняя часть запроса просто возвращает строку(и), для которых значение RNK равно 1.

Oracle

Вложенное представление возвращает каждое значение столбца SAL и число раз, сколько это значение встречается в столбце, как показано ниже:

select sal, count(*) cnt

from emp where deptno=20 group by sal

SAL CNT

800 1

1100 1

2975 1

3000 2

Следующий шаг - использовать расширение KEEP агрегатной функции MAX для поиска моды. Если проанализировать приведенный ниже оператор KEEP, можно заметить три подоператора, DENSE RANK, FIRST и ORDER BY CNT DESC:

keep(dense rank first order by cnt desc)

С их помощью очень удобно искать моду. Оператор KEEP определяет, какое значение SAL будет возвращено функцией MAX, по значению CNT, возвращенному вложенным представлением. Выражение выполняется справа налево. Сначала значения для CNT выстраиваются по убыванию, затем выбирается первое из них и возвращается в порядке, установленном функцией DENSE RANK. Посмотрев на результирую-