7 model

10 11 12 13

14 )

dimension by(row number()over(order by sal desc) rn ) measures(sal, 0 tmp, empno, ename) rules (

tmp[any] = case when sal[cv()-1] is null then sal[cv()] else tmp[cv()-1]*sal[cv()]

EMPNO ENAME

RUNNING PROD

7934 MILLER 7782 CLARK 7839 KING

-1300 -2450 -5000

-1300 3185000 -15925000000

MySQL, PostgreSQL и SQL Server

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

1 select e.empno,e.ename,e.sal,

2 (select exp(sum(ln(d.sal)))

3 from emp d

4 where d.empno <= e.empno

5 and e.deptno=d.deptno) as running prod

6 from emp e

7 where e.deptno=10

EMPNO ENAME

RUNNING PROD

7782 CLARK 7839 KING 7934 MILLER

2450 5000

1300

2450

12250000 15925000000

Для SQL Server вместо LN используется функция LOG.

Обсуждение

Кроме решения с использованием оператора MODEL, которое применимо только для Oracle 10g Database и более поздних версий, все решения основываются на том, что сумму двух чисел можно найти:

1. Вычисляя соответствующие натуральные логарифмы

2. Суммируя эти логарифмы

3. Возводя результат в степень математической константы e (используя функцию EXP)

Единственный недостаток данного подхода в его непригодности для суммирования отрицательных или нулевых значений, потому что они выходят за рамки допустимых значений для логарифмов в SQL.

DB2 и Oracle

Принцип работы оконной функции SUM OVER описан в предыдущем рецепте Вычисление текущей суммы .

В Oracle lOg Database и более поздних версиях вычисление текущего произведения можно реализовать с помощью оператора MODEL. Используя оператор MODEL и ранжирующую функцию ROW NUMBER, мы без труда организуем доступ к предыдущим строкам. С каждым элементом списка оператора MEASURES работаем, как с массивом. Обращаться к массивам можно посредством элементов списка DIMENSION (которые являются значениями, возвращенными ROW NUMBER под псевдонимом RN):

select empno, ename, sal, tmp as running prod,rn from (

select empno,ename,-sal as sal

from emp where deptno=10 )

model

dimension by(row number()over(order by sal desc) rn ) measures(sal, 0 tmp, empno, ename) rules ()

EMPNO ENAME SAL RUNNING PROD RN

7934 MILLER -1300 0 1

7782 CLARK -2450 0 2

7839 KING -5000 0 3

Обратите внимание, что SAL[1] имеет значение -1300. Поскольку строки нумеруются последовательно, без пропусков, сослаться на предыдущую строку можно, вычитая 1 из порядкового номера текущей строки. Конструкция RULES:

rules (

tmp[any] = case when sal[cv()-1] is null then sal[cv()] else tmp[cv()-1]*sal[cv()]

использует встроенный оператор ANY, чтобы можно было обрабатывать строки без точного указания их номеров в коде. В данном случае ANY принимает значения 1, 2 и 3. Исходное значение TMP[w] - нуль. Затем TMP[w] присваивается текущее значение (функция CV возвращает текущее значение), вычисляемое для соответствующей строки столбца SAL. TMP[1] изначально равно нулю, SAL[1] равно -1300. Значения для SAL[0] нет, поэтому TMP[1] присваивается значение SAL[1]. После того как TMP[1] задано, берем следующую строку, TMP[2]. Сначала вычисляется SAL[1] (SAL[CV()-1] равно SAL[1], потому что текущее значение ANY равно 2). SAL[1] не NULL, оно равно -

1300, поэтому TMP[2] присваивается результат произведения TMP[1] и SAL[2]. И так далее для всех строк.

MySQL, PostgreSQL и SQL Server

Описание подхода с использованием подзапроса, применяемого в решениях MySQL, PostgreSQL и SQL Server, можно найти в данной главе выше в разделе Вычисление текущей суммы .

Результат решения, использующего подзапрос, будет немного отличаться от результата решений для Oracle и DB2 из-за сортировки по значениям EMPNO (текущее произведение вычисляется в другой последовательности). Как и при вычислении текущей суммы, суммированием управляет предикат скалярного подзапроса. В данном решении строки упорядочиваются по столбцу EMPNO, тогда как в решении для Oracle/DB2 упорядочивание осуществляется по SAL.

Вычисление текущей разности Задача

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

ENAME SAL RUNNING DIFF

MILLER 1300 1300

CLARK 2450 -1150

KING 5000 -6150

Решение

DB2 и Oracle

Для вычисления текущей разности используйте оконную функцию

SUM OVER:

1 select ename,sal,

2 sum(case when rn = 1 then sal else -sal end)

3 over(order by sal,empno) as running diff

4 from (

5 select empno,ename,sal,

6 row number()over(order by sal,empno) as rn

7 from emp

8 where deptno = 10

9 ) x

MySQL, PostgreSQL и SQL Server

Для вычисления текущей разности используйте скалярный подзапрос: