в поле IN PROGRESS строки с датой последнего экзамента в группе возвратить значение 1. Для этого также может использоваться оконная функция MAX OVER:

select V.*,

max(pass fail)over(partition by

student id,grade id,period id) grp p f, max(test date)over(partition by

student id,grade id,period id) last test

from V

STUDENT ID TEST ID GRADE ID PERIOD ID TEST DATE PASS FAIL GRP P F LAST TEST

1 1 2 1 01-FEB-2005 0 1 01-APR-2005

1 2 2 1 01-MAR-2005 1 1 01-APR-2005

1 3 2 1 01-APR-2005 0 1 01-APR-2005

1 4 2 2 01-MAY-2005 0 0 01-JUL-2005

1 5 2 2 01-JUN-2005 0 0 01-JUL-2005

1 6 2 2 01-JUL-2005 0 0 01-JUL-2005

Теперь, когда определено, в какой период студент сдал экзамен и какова дата последнего экзамена, осталось просто поколдовать с форматированием и приукрасить результирующее множество. В окончательном решении для создания столбцов METREQ и IN PROGRESS используется функция Oracle DECODE (сторонники CASE, кусайте локти). Функция LPAD поможет выровнять значения METREQ по правому краю:

select student id, test id, grade id, period id, test date,

decode( grp p f,1,lpad(+,6),lpad(-,6) ) metreq, decode( grp p f,1,0,

decode( test date,last test,1,0 ) ) in progress

from ( select V.*,

max(pass fail)over(partition by

student id,grade id,period id) grp p f, max(test date)over(partition by

student id,grade id,period id) last test

from V ) x

STUDENT ID TEST ID GRADE ID PERIOD ID TEST DATE METREQ IN PROGRESS 1 1 2 1 01-FEB-2005 + 0

1 2 2 1 01-MAR-2005 + 0

1 3 2 1 01-APR-2005 + 0

1 4 2 2 01-MAY-2005 - 0

1 5 2 2 01-JUN-2005 - 0

1 6 2 2 01-JUL-2005 - 1

Оконные функции, краткий обзор

В рецептах данной книги широко используются оконные функции, введенные в стандарт ISO SQL в 2003 году, и собственные оконные функции отдельных производителей. Данное приложение является кратким обзором оконных функций. Оконные функции существенно упрощают решение многих обычно вызывающих сложности (при решении с использованием стандартных механизмов SQL) задач. Полный список доступных оконных функций, их синтаксис и подробное описание их работы вы найдете в документации своей базы данных.

Группировка

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

Если давать простое определение, группировка - это способ организации подобных строк. При использовании GROUP BY в запросе каждая строка результирующего множества является группой и представляет одну или более строк с одинаковыми значениями в одном или более заданных полях. Вот суть группировки.

Если группа - это просто уникальный экземпляр строки, представляющий одну или более строк с таким же значением в определенном столбце (или столбцах), тогда примерами групп в таблице EMP являются все служащие 10-го отдела (общее значение, позволяющее объединить этих служащих в одну группу, - DEPTNO=10) или все клерки (общее значение, позволяющее объединить этих служащих в одну группу, - JOB=CLERK). Рассмотрим следующие запросы. Первый показывает всех служащих 10-го отдела; второй группирует служащих

DEPTNO CNT HI SAL LO SAL

10 3 5000 1300

Если бы не было возможности сгруппировать служащих 10-го отдела, для получения информации, возвращаемой вторым из приведенных выше запросов, пришлось бы самостоятельно проверять записи, соответствующие этому отделу (это просто, если таких строк всего три, а если этих строк три миллиона?). Итак, почему возникает необходимость в группировке? Причины весьма разнообразны. Может потребоваться представить, сколько существует разных групп или сколько членов (строк) в каждой группе. Как видно из простого примера, показанного выше, группировка позволяет получать информацию о многих строках таблицы, не проверяя их одну за другой.

Определение группы в SQL

В математике основным определением группы является тройка (G, , e), где G - множество, - бинарная операция над G, и e - член G. Мы будем использовать это определение как базовое для описания группы в SQL. SQL-группа определяется как пара (G, e), где G - результирующее множество самостоятельного или самодостаточного запроса, в котором используется оператор GROUP BY, e - член G, и выполняются следующие аксиомы:

Для каждого e в G e является уникальным и представляет один или более экземпляров e.

Для каждого e в G агрегатная функция COUNT возвращает значение > 0.

10-го отдела и возвращает о них следующую информацию: количество строк (членов) в группе, наибольшая заработная плата и наименьшая заработная плата: