Пример 1: Вывести список учеников и максимальный балл в каждой строке: SELECT
FROM
Фамилия
,Предмет
,Школа
,Баллы
, MAX(Баллы) OVER() AS Макс_Балл Ученики
Пример 2: Вывести список учеников и разницу между баллами ученика и минималь- ным баллом в каждой строке:
SELECT
FROM
Фамилия
,Предмет
,Школа
,Баллы
, Баллы - MIN(Баллы) OVER() AS Разница
Ученики
Пример 3: Вывести список учеников и процентное соотношение к суммарному баллу в каждой строке:
SELECT
FROM
Фамилия
,Предмет
,Школа
,Баллы
,Баллы * 100 / SUM(Баллы) OVER() AS Процент Ученики
Пример 4: Вывести список учеников и средний балл в школе в каждой строке: SELECT
FROM
Фамилия
,Предмет
,Школа
,Баллы
,AVG(Баллы) OVER(PARTITION BY Школа) AS Сред_Шк Ученики
Пример 5: Вывести список учеников и количество учеников в школе в каждой строке, отсортировать по школам в оконной функции:
SELECT
Кол_Шк
FROM
Фамилия
,Предмет
,Школа
,Баллы
,COUNT(*) OVER(PARTITION BY Школа ORDER BY Школа) AS
Ученики
Пример 6: Вывести список учеников и номер строки при сортировке по баллам по убы- ванию:
SELECT
ROW_NUMBER() OVER(ORDER BY Баллы DESC) AS Номер_строки
,Фамилия
,Предмет
FROM
,Школа
,Баллы Ученики
Пример 7: Вывести список учеников и номер строки внутри школы при сортировке по баллам по убыванию:
SELECT
ROW_NUMBER() OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS
Номер_строки
,Фамилия
,Предмет
,Школа
,Баллы
FROM
Ученики
Пример 8: Вывести список учеников и ранг по баллам в каждой школе: SELECT
RANK() OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS Ранг_Шк
,Фамилия
,Предмет
,Школа
,Баллы
FROM
Ученики
Пример 9: Вывести список учеников и сжатый ранг по баллам в каждой школе. Резуль- тат отсортировать по фамилии в алфавитном порядке:
SELECT
DENSE_RANK() OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS
Сж_Ранг_Шк
FROM
,Фамилия
,Предмет
,Школа
,Баллы
Ученики
ORDER BY
Фамилия
Пример 10: Вывести список учеников, распределенных по трем группам по фамилии: SELECT
NTILE(3) OVER(ORDER BY Фамилия) AS Гр_Фам
,Фамилия
,Предмет
,Школа
,Баллы
FROM
Ученики
Пример 11: Вывести список учеников, распределенных по двум группам по баллам внутри школы:
SELECT
NTILE(2) OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS Гр_Балл
,Фамилия
,Предмет
,Школа
,Баллы
FROM
Ученики
Пример 12: Вывести список учеников и разницу с баллами предыдущего ученика, при сортировке по возрастанию баллов:
SELECT
Фамилия
,Предмет
,Школа
,Баллы
,Баллы - LAG(Баллы) OVER(ORDER BY Баллы) AS Разница
FROM
Ученики
Пример 13: Вывести список учеников и разницу с баллами ученика через две позиции при сортировке по убыванию баллов, значение по умолчанию использовать 0:
SELECT
Фамилия
,Предмет
,Школа
,Баллы
FROM
,Баллы - LEAD(Баллы, 2, 0) OVER(ORDER BY Баллы DESC) AS Разница Ученики
Пример 14: Вывести список учеников и разницу с баллами первого ученика при сорти- ровке по убыванию баллов:
SELECT
Фамилия
,Предмет
,Школа
,Баллы
,FIRST_VALUE(Баллы) OVER(ORDER BY Баллы DESC) - Баллы AS Разница
FROM
Ученики
Пример 15: Вывести список учеников и разницу с баллами последнего ученика в школе при сортировке по убыванию баллов:
SELECT
Фамилия
,Предмет
,Школа
,Баллы
,LAST_VALUE(Баллы) OVER(ORDER BY Баллы RANGE BETWEEN UN- BOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - Баллы AS Разница
FROM
Ученики
Задание
Вывести список учеников и разницу между баллами ученика и максимальным бал- лом в каждой строке.
Вывести список учеников и процентное соотношение к среднему баллу в каждой строке.
Вывести список учеников и минимальный балл в школе в каждой строке.
Вывести список учеников и суммарный балл в школе в каждой строке, отсортиро- вать по школам в оконной функции.
Вывести список учеников и номер строки при сортировке по фамилиям в обратном алфавитном порядке.
Вывести список учеников, номер строки внутри школы и количество учеников в школе при сортировке по баллам по убыванию.
Вывести список учеников и ранг по баллам.
Вывести список учеников и сжатый ранг по баллам. Результат отсортировать по фамилии в алфавитном порядке.
Вывести список учеников, распределенных по пяти группам по фамилии.
Вывести список учеников, распределенных по трем группам по баллам внутри школы.
Вывести список учеников и разницу с баллами ученика, находящегося выше на три позиции при сортировке по возрастанию баллов.
Вывести список учеников и разницу с баллами следующего ученика при сорти- ровке по убыванию баллов, значение по умолчанию использовать 0.
Лабораторная работа 14
Инструкции OLAP
Цель работы
Изучить использование ROLLUP.
Изучить использование CUBE.
Изучить использование GROUPING SETS.
Изучить использование PIVOT.
Изучить использование UNPIVOT.
Теоретическая часть
Operator ROLLUP создаёт группу для каждого сочетания выражений столбцов. Кроме того, выполняет сведение результатов в промежуточные и общие итоги. Для этого запрос пе- ремещается справа налево, уменьшая количество выражений столбцов, по которым он создает группы и агрегаты. Синтаксис команды имеет следующий вид:
GROUP BY ROLLUP(<список столбцов>) или
GROUP BY <список столбцов> WITH ROLLUP
Порядок столбцов влияет на выходные данные ROLLUP и может отразиться на коли- честве строк в результирующем наборе.
GROUP BY ROLLUP(col1, col2) создает группы для каждой комбинации выражений столбцов в следующих списках:
col1, col2 col1, NULL
NULL, NULL - это общий итог.
Оператор CUBE создает группы для всех возможных сочетаний столбцов. Синтаксис команды имеет следующий вид:
GROUP BY CUBE(<список столбцов>) или
GROUP BY <список столбцов> WITH CUBE
GROUP BY CUBE(col1, col2) создает группы для каждой комбинации выражений столбцов в следующих списках:
col1, col2 col1, NULL
NULL, col2
NULL, NULL - это общий итог.
Оператор GROUPING SETS позволяет объединять несколько предложений GROUP BY в одно предложение GROUP BY. Результаты эквивалентны тем, что формируются с примене- нием конструкции UNION ALL к указанным группам. Синтаксис команды имеет следующий вид:
GROUP BY GROUPING SETS(<список столбцов>)
Если параметр GROUPING SETS имеет два или более элементов, результатом будет объединение элементов.
SQL не консолидирует повторяющиеся группы, созданные для списка GROUPING SETS.
GROUP BY GROUPING SETS(col1, col2) создает группы для каждой комбинации выражений столбцов в следующих списках: col1, NULL
NULL, col2
Для предложения GROUP BY, использующего ROLLUP, CUBE или GROUPING SETS, допускается максимум 32 выражения.
Функция GROUPING указывает, является ли указанное выражение столбца в списке GROUP BY статистическим или нет. В результирующем наборе возврат будет 1 (статистиче- ское выражение) или ноль (нестатистическое выражение). Функция GROUPING может ис- пользоваться только в предложениях SELECT, HAVING и ORDER BY, если указано предло- жение GROUP BY.
Функция GROUPING_ID вычисляет уровень группирования. Функция GROUPING_ID может использоваться только в предложениях SELECT, HAVING и ORDER BY, если указано предложение GROUP BY.
Оператор PIVOT поворачивает возвращающее табличное значение выражение, преоб- разуя уникальные значения одного столбца выражения в несколько выходных столбцов. В случае необходимости PIVOT также объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных.
Синтаксис PIVOT является более простым и понятным, чем синтаксис, который может выполнить то же действие с помощью последовательности инструкций SELECT...CASE. Син- таксис имеет следующий вид:
SELECT < столбцы для группировки>, <пивотируемые столбцы > FROM
(<запрос возвращающий данных>) AS <псевдоним>
PIVOT
(<агрегирующая функция>(<столбец>)
FOR [<столбец, значения которого будут заголовками>] IN (<список пивотируемых столбцов>)
) AS <псевдоним для пивот-таблицы>
Оператор UNPIVOT поворачивает возвращающее табличное значение выражения, пре- образуя нескольких выходных столбцов в один столбец. Синтаксис имеет следующий вид:
SELECT <список столбцов> FROM
<таблица> UNPIVOT
(<столбец значения строк> FOR [<столбец, значения заголовок>] IN (<список анпивотируемых столбцов>)
) AS <псевдоним для анпивот-таблицы>
Практическая часть
Таблица Ученики:
|
ID |
Фамилия |
Предмет |
Школа |
Баллы |
|
|
1 |
Иванова |
Математика |
Лицей |
98,5 |
|
|
2 |
Петров |
Физика |
Лицей |
99 |
|
|
3 |
Сидоров |
Математика |
Лицей |
88 |
|
|
4 |
Полухина |
Физика |
Гимназия |
78 |
|
|
5 |
Матвеева |
Химия |
Лицей |
92 |
|
|
6 |
Касимов |
Химия |
Гимназия |
68 |
|
|
7 |
Нурулин |
Математика |
Гимназия |
81 |
|
|
8 |
Авдеев |
Физика |
Лицей |
87 |
|
|
9 |
Никитина |
Химия |
Лицей |
94 |
|
|
10 |
Барышева |
Химия |
Лицей |
88 |
Пример 1: Напишите запрос, который выводит количество учеников по предметам по каждой школе, и промежуточные итоги:
SELECT
Предмет
,Школа
,COUNT(Фамилия) AS Количество
FROM
Ученики
GROUP BY
Предмет, Школа WITH ROLLUP
Пример 2: Напишите запрос, который выводит количество учеников по предметам и по школам, и промежуточные итоги:
SELECT
FROM
Предмет
,Школа
,COUNT(Фамилия) AS Количество
Ученики
GROUP BY
Предмет, Школа WITH CUBE
Пример 3: Напишите запрос, который выводит количество учеников по предметам и по школам:
SELECT
Предмет
,Школа
,COUNT(Фамилия) AS Количество
FROM
Ученики
GROUP BY
GROUPING SETS(Предмет, Школа)
Пример 4: Напишите запрос, который выводит количество учеников по предметам по каждой школе и промежуточные итоги. NULL значения заменить на соответствующий текст:
SELECT
COALESCE(Предмет, 'ИТОГО') AS Предмет
,COALESCE(Школа, 'Итого') AS Школа
,COUNT(Фамилия) AS Количество
FROM
Ученики
GROUP BY
ROLLUP(Предмет, Школа)
Пример 5: Напишите запрос, который выводит количество учеников по предметам и по школам, и промежуточные итоги. В итоговых строках NULL значения заменить на соот- ветствующий текст в зависимости от группировки:
SELECT
IIF(GROUPING(Предмет)=1, 'ИТОГО', Предмет) AS Предмет
,IIF(GROUPING(Школа)=1, 'Итого', Школа) AS Школа
,COUNT(Фамилия) AS Количество
FROM
Ученики
GROUP BY
CUBE(Предмет, Школа)
Пример 6: Напишите запрос, который выводит количество учеников по предметам и по школам. В итоговых строках NULL значения заменить на соответствующий текст в зави- симости от уровней группировки:
SELECT
CASE GROUPING_ID(Предмет, Школа) WHEN 1 THEN 'Итого по предметам' WHEN 3 THEN 'Итого'
ELSE ''
END AS Итого
,ISNULL(Предмет, '') AS Предмет
,ISNULL(Школа, '') AS Школа
,COUNT(Фамилия) AS Количество
FROM
Ученики
GROUP BY
ROLLUP(Предмет, Школа)
Пример 7: Напишите запрос, который выводит количество учеников по предметам по столбцам:
SELECT
'Количество' AS [Количество учеников по предметам]
,Математика
,Физика
,Химия
FROM
(
SELECT
Предмет
,Фамилия
FROM
Ученики
) AS SOURCE_TABLE PIVOT
(
COUNT(Фамилия)
FOR Предмет IN (Математика, Физика, Химия)
) AS PIVOT_TABLE
Пример 8: Напишите запрос для вывода количества учеников для каждой школы по каждому предмету (школы должны быть указаны в строках, предметы в столбцах):
SELECT
Школа
,Математика
,Физика
,Химия
FROM
(
SELECT
Школа
,Предмет
,Фамилия
FROM
Ученики
) AS SOURCE_TABLE PIVOT
(
COUNT(Фамилия)
FOR Предмет IN (Математика, Физика, Химия)
) AS PIVOT_TABLE
Пример 9: Напишите запрос, который выводит фамилию учеников и предметы вместе со школами в один столбец:
SELECT
Фамилия,
[Предмет или школа] FROM Ученики
UNPIVOT (
[Предмет или школа] FOR Значение IN (Предмет, Школа)
) unpvt
Задание
Напишите запрос, который выводит максимальный балл учеников по школам, по каждому предмету по каждой школе и промежуточные итоги.
Напишите запрос, который выводит минимальный балл учеников по школам и по предметам, и промежуточные итоги.
Напишите запрос, который выводит средний балл учеников по школам и по предметам.
Напишите запрос, который выводит количество учеников по каждой школе по пред- метам и промежуточные итоги. NULL значения заменить на соответствующий текст.