TURNER ADAMS

JAMES

FORD MILLER

1500 1100

950 3000

1300

22675 23775 24725 27725 29025

Обсуждение

Получение текущих сумм - одна из задач, решение которых упростили новые оконные функции ANSI. Для СУБД, не поддерживающих пока что эти функции, необходимо использовать скалярный подзапрос (объединение по полю с уникальными значениями).

DB2 и Oracle

Оконная функция SUM OVER упрощает задачу по вычислению текущей суммы. В операторе ORDER BY решения указан не только столбец SAL, но и столбец EMPNO (первичный ключ), чтобы исключить дублирование значений при вычислении текущей суммы. В противном случае возникает проблема с дубликатами, что иллюстрирует в следующем примере столбец RUNNING TOTAL2:

select empno, sal,

sum(sal)over(order by sal,empno) as running total1,

sum(sal)over(order by sal) as running total2 from emp

Значения столбца RUNNING TOTAL2 для служащих WARD, MARTIN, SCOTT и FORD неверны. Их заработные платы встречаются несколько раз, и дубликаты также вошли в текущую сумму. Вот почему для формирования (правильных) результатов, которые показаны в столбце RUNNING TOTAL1, необходимо включать в оператор ORDER BY и столбец EMPNO (значения которого уникальны). Рассмотрим следующее: для служащего ADAMS в столбцах RUNNING TOTAL1

и RUNNING TOTAL2 указано значение 2850, добавляем к нему заработную плату служащего WARD, равную 1250, и получаем 4100, а в столбце RUNNING TOTAL2 возвращено 5350. Почему? Поскольку WARD и MARTIN имеют одинаковые значения в столбце SAL, их две зарплаты размером по 1250 складываются, образуя в сумме 2500, а затем это значение добавляется к 2850, что в итоге и дает 5350 как для WARD, так и для MARTIN. Задавая сочетание столбцов, которое не может иметь дублирующиеся значения (например, любое сочетание SAL и EMPNO уникально), мы гарантируем правильное вычисление текущей суммы.

MySQL, PostgreSQL и SQL Server

Пока данные СУБД не обеспечивают полной поддержки оконных функций, для вычисления текущей суммы используется скалярный подзапрос. Необходимо провести объединение по столбцу с уникальными значениями, в противном случае, если в столбце имеются дублирующиеся значения, текущие суммы будут неверны. Ключ к решению данного рецепта - объединение по D.EMPNO с E.EMPNO, в результате чего возвращаются (суммируются) все значения D.SAL, для которых D.EMPNO меньше или равно E.EMPNO. Разобраться в этом можно, переписав скалярный подзапрос как объединение для небольшого числа служащих:

select e.ename as ename1, e.empno as empno1, e.sal as sal1, d.ename as ename2, d.empno as empno2, d.sal as sal2 from emp e, emp d where d.empno <= e.empno and e.empno = 7566

ENAME

EMPNO1

SAL1 ENAME

EMPNO2

SAL2

JONES JONES JONES JONES

7566 7566 7566 7566

2975 SMITH

2975 ALLEN

2975 WARD

2975 JONES

7369 7499 7521

7566

800 1600 1250

2975

Каждое значение EMPNO2 сравнивается с каждым значением EMPNO1. В сумму включается значение столбца SAL2 каждой строки, для которой значение столбца EMPNO2 меньше или равно значению столбца EMPNO1. В этом фрагменте значения EMPNO для служащих SMITH, ALLEN, WARD и JONES сравниваются со значением EMPNO для JONES. Поскольку значения EMPNO для всех четырех служащих удовлетворяют условию (меньше или равны EMPNO для JONES), их заработные платы суммируются. Заработная плата любого служащего, значение EMPNO которого больше, чем значение для JONES, не будет включена в SUM (в данном фрагменте). Полный запрос суммирует заработные платы всех служащих, для которых соответствующее значение EMPNO меньше или равно 7934 (EMPNO служащего MILLER), которое является наибольшим значением EMPNO в таблице.

Вычисление текущего произведения Задача

Требуется найти текущее произведение для числового столбца. Эта операция аналогична Вычислению текущей суммы , но значения не складываются, а перемножаются.

Решение

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

DB2 и Oracle

Примените оконную функцию SUM OVER и воспользуйтесь возможностью производить умножение путем суммирования логарифмов:

1 select empno,ename,sal,

2 exp(sum(ln(sal))over(order by sal,empno)) as running prod

3 from emp

4 where deptno = 10

EMPNO ENAME SAL RUNNING PROD

7934 MILLER 1300 1300

7782 CLARK 2450 3185000

7839 KING 5000 15925000000

В SQL вычисление логарифмов отрицательных чисел и нуля является недопустимой операцией. Если в таблицах содержатся такие значения, необходимо предупредить их передачу в SQL-функцию LN. В данном решении такие меры предосторожности не предусмотрены в целях удобства чтения кода, но они должны быть предприняты в реальных запросах. Если в таблицах представлены исключительно отрицательные значения, ноль либо NULL, приведенное выше решение не подходит.

В качестве альтернативы в Oracle можно использовать оператор MODEL, который был введен в Oracle Database 10. В следующем примере все значения SAL возвращены как отрицательные числа, чтобы показать, что отрицательные значения не представляют проблемы для вычисления текущих произведений:

1 select empno, ename, sal, tmp as running prod

2 from (

3 select empno,ename,-sal as sal

4 from emp

5 where deptno=10