select deptno,

emp cnt as dept total, total,

max(case when job = CLERK

then job cnt else 0 end) as clerks,

max(case when job = MANAGER

then job cnt else 0 end) as mgrs,

max(case when job = PRESIDENT

then job cnt else 0 end) as prez,

max(case when job = ANALYST

then job cnt else 0 end) as anals,

max(case when job = SALESMAN

then job cnt else 0 end) as smen

from ( select deptno,

job,

count(*) over (partition by deptno) as emp cnt, count(job) over (partition by deptno,job) as job cnt, count(*) over () as total from emp ) x

group by deptno, emp cnt, total

DEPTNO DEPT TOTAL TOTAL CLERKS MGRS PREZ ANALS SMEN

10 3 14 1 1 1 0 0

20 5 14 2 1 0 2 0

30 6 14 1 1 0 0 4

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

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

select ename as name, sal,

max(sal)over(partition by deptno) as hiDpt, min(sal)over(partition by deptno) as loDpt, max(sal)over(partition by job) as hiJob, min(sal)over(partition by job) as loJob, max(sal)over() as hi, min(sal)over() as lo, sum(sal)over(partition by deptno

order by sal,empno) as dptRT, sum(sal)over(partition by deptno) as dptSum, sum(sal)over() as ttl from emp

order by deptno,dptRT

Этот запрос отвечает на перечисленные ниже вопросы легко, эффективно и понятно (и без дополнительных объединений с EMP!). Чтобы определить:

1. Кто из всех служащих получает наибольшую заработную плату (HI).

2. Кто из всех служащих получает наименьшую заработную плату (LO).

3. Кто получает наибольшую заработную плату в своем отделе (HIDPT).

4. Кто получает наименьшую заработную плату в своем отделе

(LODPT).

5. Кто получает наибольшую заработную плату на данной должности

(HIJOB).

6. Кто получает наименьшую заработную плату на данной должности

(LOJOB).

7. Сумму всех заработных плат (TTL).

8. Сумму заработных плат по отделам (DPTSUM).

9. Текущую сумму по всем заработным платам по отделам (DPTRT).

просто сравниваем каждого служащего и его заработную плату с другими строками результирующего множества.

Вспоминаем Розенштейна

Данное приложение - дань уважения Дэвиду Розенштейну. Как я говорил во введении, я считаю его книгу The Essence of SQL лучшей книгой (даже сегодня) по SQL всех времен и народов. В ней всего 119 страниц, но она охватывает, на мой взгляд, все ключевые для любого программиста на SQL вопросы. В частности, Дэвид показывает, как анализировать задачу и находить решение. Его решения ориентированы исключительно на множества. Даже если размер ваших таблиц не позволяет использовать эти решения, его методы изумительны, поскольку заставляют перестать искать процедурное решение задачи и начать думать категориями множеств.

Книга The Essence of SQL была опубликована задолго до появления оконных функций и операторов MODEL. В этом приложении для некоторых задач из книги Розенштейна я предлагаю альтернативные решения с использованием современных функций SQL. (Будут ли эти решения лучше, чем решения Розенштейна, зависит от обстоятельств.) В конце каждого обсуждения представлено решение, основанное на оригинальном решении из книги Дэвида. Если пример является разновидностью задачи Розенштейна, представленное для него решение тоже является вариантом решения (решение, которого может не быть в его книге, но в котором используется аналогичная техника).

Таблицы Розенштейна

Следующие таблицы основаны на таблицах из книги Розенштейна и будут использоваться в этой главе:

/* таблица студентов */ create table student ( sno integer,

sname varchar(10),

age integer