Пока что ранги ничего не значат. Сегментирование выполнялось по EMPNO, поэтому всем трем служащим DEPTNO 10 присвоен ранг 1. Как только будет введено декартово произведение, появятся разные ранги, что можно видеть в следующих результатах:

select e.ename,e.job,e.sal,

row number()over(partition by e.empno

order by e.empno) rn

from emp e,

(select *

from emp where job=CLERK) four rows

Здесь следует остановиться и понять два ключевых момента:

RN равен 1 теперь не для всех служащих; теперь это повторяющаяся последовательность значений от 1 до 4, потому что ранжирующие функции применяются после выполнения операторов FROM и WHERE. Таким образом, сегментирование по EMPNO обусловливает сброс RN в начальное значение (1) для записи нового служащего.

Вложенный запрос FOURROWS - это просто SQL-выражение, возвращающее четыре строки. Это все, что оно делает. Должно быть получено по строке для каждого столбца (ENAME, JOB, SAL) плюс строка-пробел.

На данный момент вся тяжелая работа выполнена, осталось лишь с помощью выражения CASE разместить знеачения ENAME, JOB и SAL всех служащих в один столбец (чтобы значения SAL могли использоваться в CASE, их необходимо привести к строковому типу):

select case rn

when 1 then ename when 2 then job

when 3 then cast(sal as char(4)) end emps from (

select e.ename,e.job,e.sal,

row number()over(partition by e.empno

order by e.empno) rn

from emp e,

(select *

from emp where job=CLERK) four rows where e.deptno=10 ) x

EMPS

CLARK

MANAGER

2450

KING

PRESIDENT 5000

MILLER

CLERK

1300

Исключение повторяющихся значений из результирующего множества

Задача

При формировании отчета выдвинуто требование о том, что дублирующиеся значения в столбце должны отображаться лишь один раз. Например, из таблицы EMP требуется извлечь значения DEPTNO и ENAME, при этом необходимо сгруппировать вместе все строки для каждого значения DEPTNO и выводить каждое значение DEPTNO только один раз. Ожидается получить следующее результирующее множество:

DEPTNO ENAME

10 CLARK

KING

MILLER 20 SMITH

ADAMS

FORD

SCOTT

JONES 30 ALLEN

BLAKE

MARTIN

JAMES

TURNER

WARD

Решение

Это простая задача по форматированию, которая без труда решается применением оконной функции LAG OVER, предоставляемой Oracle. Можно прибегнуть к другим средствам, например, скалярным подзапросам и другим оконным функциям (именно они будут использоваться для остальных платформ), но LAG OVER наиболее удобна и уместна в данном случае.

DB2 и SQL Server

С помощью оконной функции MIN OVER можно найти наименьшее значение EMPNO для каждого DEPTNO, затем, используя выражение CASE, стереть значение DEPTNO из строк со всеми остальными EMPNO:

1 select case when empno=min empno

2 then deptno else null

3 end deptno,

4 ename

5 from (

6 select deptno,

7 min(empno)over(partition by deptno) min empno,

8 empno,

9 ename

10 from emp

11 ) x

Oracle

С помощью оконной функции LAG OVER организуйте доступ к предыдущим относительно текущей строкам, чтобы найти первое значение DEPTNO для каждого сегмента:

1 select to number(

2 decode(lag(deptno)over(order by deptno),

3 deptno,null,deptno)

4 ) deptno, ename

5 from emp

PostgreSQL и MySQL

Данный рецепт описывает применение оконных функций для упрощения доступа к строкам, окружающим текущую. На момент написания данной книги эти производители не поддерживают оконные функции.

Обсуждение

DB2 и SQL Server

Первый шаг - с помощью оконной функции MIN OVER найти наименьшее значение EMPNO для каждого DEPTNO:

select deptno,

min(empno)over(partition by deptno) min empno,