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

WHERE Вид < 2
ORDER BY Основа, Блюдо;
Предложение OVER, с которым используется функция ROW_NUMBER, задает порядок нумерации строк. При этом используется дополнительное предложение ORDER BY, которое не имеет отношения к порядку вывода строк запроса. Если вы посмотрите на результат, то заметите, что порядок строк в результирующем наборе и порядок нумерации не совпадают:

Результат
Номер	Блюдо	Основа
7	Сметана	Молоко
8	Творог	Молоко
1	Мясо с гарниром	Мясо
5	Салат мясной	Мясо
3	Салат витаминный	Овощи
4	Салат летний	Овощи
2	Паштет из рыбы	Рыба
6	Салат рыбный	Рыба

А если требуется пронумеровать блюда для каждой основы отдельно? Для этого нам потребуется еще одна конструкция в предложении OVER — PARTITION BY.

Конструкция PARTITION BY задает группы строк, для которых выполняется независимая нумерация. Группа определяется равенством значений в списке столбцов, перечисленных в этой конструкции, у строк, составляющих группу.

Пример 43.

Пронумеровать блюда в рамках каждой основы отдельно.
SELECT row_number() over(partition BY Основа ORDER BY Блюдо) as Номер, Блюдо, Основа
FROM Блюда
WHERE Вид < 2
ORDER BY Основа, Блюдо;
PARTITION BY Основа означает, что блюда с одной основой образуют группу, для которой и выполняется независимая нумерация. В результате получим:

Результат
Номер	Блюдо	Основа
1	Сметана	Молоко
2	Творог	Молоко
1	Мясо с гарниром	Мясо
2	Салат мясной	Мясо
1	Салат витаминный	Овощи
2	Салат летний	Овощи
1	Паштет из рыбы	Рыба
2	Салат рыбный	Рыба

Отсутствие конструкции PARTITION BY, как это было в первом примере, означает, что все строки результирующего набора образуют одну единственную группу.

5.2. Функции RANK() и DENSE_RANK()

Эти функции, как и функция ROW_NUMBER(), тоже нумеруют строки, но делают это несколько отличным способом. Это отличие проявляется в том, что строки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги).
получат такие номера:

Пример 44.
SELECT rank() over(ORDER BY Основа ) as Номер, Блюдо, Основа
FROM Блюда

Результат
Номер	Блюдо	Основа
1	Сметана	Молоко
1	Творог	Молоко
3	Мясо с гарниром	Мясо
3	Салат мясной	Мясо
5	Салат витаминный	Овощи
5	Салат летний	Овощи
7	Паштет из рыбы	Рыба
7	Салат рыбный	Рыба

WHERE Вид < 2
ORDER BY Основа, Блюдо;

SELECT dense_rank() over(ORDER BY Основа ) as Номер, Блюдо, Основа
FROM Блюда
WHERE Вид< 2
ORDER BY Основа, Блюдо;

Результат
Номер	Блюдо	Основа
1	Сметана	Молоко
1	Творог	Молоко
2	Мясо с гарниром	Мясо
2	Салат мясной	Мясо
3	Салат витаминный	Овощи
3	Салат летний	Овощи
4	Паштет из рыбы	Рыба
4	Салат рыбный	Рыба

В первом случае мы получаем неплотную нумерацию (номер группы — порядковый номер строки, с которого начинается группа), во втором — плотную.

Как и для функции ROW_NUMBER, в предложении OVER может использоваться конструкция PARTITION BY, разбивающая весь набор строк, возвращаемых запросом, на группы, к которым затем применяется соответствующая функция.
Запрос
SELECT rank() over(partition BY Основа ORDER BY Вес) as Номер, Блюдо, Основа

Результат
Номер	Блюдо	Основа	Вес
1	Сметана	Молоко	140
1	Творог	Молоко	140
2	Мясо с гарниром	Мясо	250
1	Салат мясной	Мясо	200
1	Салат витаминный	Овощи	200
1	Салат летний	Овощи	200
1	Паштет из рыбы	Рыба	120
2	Салат рыбный	Рыба	200

FROM Блюда
WHERE Вид < 2
ORDER BY Основа, Блюдо;

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

Творог и сметана имеют одинаковый номер в группе, так как их вес совпадает.
А вот как можно выбрать самые легкие блюда в каждой категории:
SELECT *
FROM (SELECT rank() over(partition BY Основа ORDER BY Вес) as Номер,
Блюдо, Основа
FROM Блюда
WHERE Вид< 2) a
WHERE Номер = 1;

Результат
Номер	Блюдо	Основа	Вес
1	Сметана	Молоко	140
1	Творог	Молоко	140
1	Салат мясной	Мясо	200
1	Салат витаминный	Овощи	200
1	Салат летний	Овощи	200
1	Паштет из рыбы	Рыба	120

Запрос можно было бы написать короче, если бы функцию RANK можно было бы применять в предложении WHERE, т.к. само значение ранга нам не требуется. Однако это запрещено (как и для других ранжирующих функций), по крайней мере, в SQL Server.

Наконец, рассмотрим еще один пример.

Пример 45.

Найти основы блюд, которые в таблице Блюда представлены более чем тремя наименованиями.
Эта задача имеет традиционное решение через агрегатные функции:
SELECT Основа
FROM Блюда
GROUP BY Основа
HAVING count(*)>3;
Однако эту задачу можно решить и с помощью функции RANK. Идея состоит в следующем: ранжировать блюда каждой основу по уникальному ключу и выбрать только те основы, блюда которых достигают ранга 4 и более:
SELECT DISTINCT Основа
FROM (SELECT rank() over(partition BY Основа ORDER BY ID_Блюда) as
Номер, Блюдо, Основа
FROM Блюда ) a
WHERE Номер > 3;
И в одном, и в другом случае, естественно, мы получим один и тот же результат:

Результат

основа

Молоко

Мясо

Овощи

Рыба

Фрукты

Еще раз заметим, что упорядочивание в последнем случае должно быть выполнено по уникальной комбинации столбцов, т.к., в противном случае, блюд может быть больше трех, а ранг меньше (например, 1, 2, 2,...). В нашем случае данное условие выполнено, т.к. упорядочивание выполняется по столбцу ID_блюда, который является первичным ключом в таблице Блюда.

Глава 6. Использование PIVOT и UNPIVOT.

Рассмотрим такой запрос
SELECT s.Вид, Основа
FROM Блюда b JOIN Справочник_вид_блюда s ON b.Вид = s.ID_Вид;
результатом которого является

Результат вид основа Закуска Овощи Закуска Мясо Закуска Овощи Закуска Рыба

продолжение Закуска Молоко Закуска Молоко Суп Мясо Суп Рыба Суп Рыба

продолжение Горячее Мясо Горячее Рыба Горячее Яйца Горячее Овощи Горячее Молоко

продолжение
Горячее	Крупа
Горячее	Молоко
Горячее	Овощи
Горячее	Молоко
Десерт	Фрукты
…

Пусть теперь нам требуется получить таблицу со следующими заголовками:

вид	Овощи	Мясо	Рыба	Молоко	Яйца	Крупа	Фрукты	Кофе
Горячее
Десерт
Закуска
Напиток
Суп

Заголовками строк здесь являются уникальные названия видов блюд, которые берутся из столбца Вид вышеприведенного запроса, а заголовками столбцов – уникальные основы блюд (соответственно, из столбца Основа). А что должно быть в середине? Ответ очевиден – некоторый агрегат, например, функция count(Основа), которая подсчитает для каждого вида отдельно число блюд с основой овощи, мясо и т.д., которые и заполнят соответствующие ячейки этой таблицы.

Это простейший вариант сводной таблицы, который имеет всего два уровня иерархии по столбцам и строкам.
Сводные таблицы появились в версии MS Excel 5.0 и представляют собой двумерную визуализацию многомерных структур данных, применяемых в технологии OLAP для построения хранилищ данных. Следует сказать, что сводная таблица не является реляционной, поскольку имеет не только заголовки столбцов, но и заголовки строк, при этом и те и другие формируются из данных, находящихся в столбцах обычных реляционных таблиц. Последнее, кстати, означает, что число строк и столбцов заранее неизвестно, т.к. они формируются динамически при выполнении запроса к реляционным данным. Кроме того, заголовки могут иметь многоуровневые подзаголовки, что и позволяет выполнять операции свертки (переход на более высокий уровень иерархии) и детализации (переход на более низкий уровень иерархии).
Можно сказать, что pivot-таблица в SQL – это одноуровневая сводная таблица.
Оператор PIVOT не является стандартным, поэтому мы будем использовать в примерах его реализацию в языке Transact-SQL (SQL Server 2005/2008).