Дополнительные возможности SQL

1.

Занятие 6. Дополнительные
возможности SQL
Преподаватель: Владимир Кривец
1

2.

Вспоминаем пройденное
Занятие 5
• Зачем соединения?
• Что такое предикат соединения?
• Для каких строк и из каких таблиц проверяется предикат?
• Какие виды соединений бывают?
• Select * from t1 left join t2 on t1.id = t2.id
• Из какой таблицы СУБД выведет все строки?
• Могут ли вывестись все строки из 2х таблиц?
• Почему не всегда можно использовать «=» при фильтрации с помощью подзапроса?
• Что такое коррелирующий подзапрос?

3.

Занятие 6.
Дополнительные возможности SQL
План
• Объединение результатов нескольких запросов
• Вынесение подзапроса в оператор WITH
• Аналитические функции
• Оконные функции
• Иерархические запросы
• Практика

4.

Объединение результатов нескольких
запросов
Клиенты
Client_Id
FIO
Mail
1000
Иванов И.И.
mail1@mail
1001
…
…
1002
…
…
Client_Id
FIO
Mail
1
Петров П.П.
mail2@mail
2
…
…
3
…
…
Клиенты Партнеров

5.

Объединение результатов нескольких
запросов
Объединение результатов запросов
позволяет сформировать единый набор
данных
• Какие запросы можно объединять
• Одинаковое кол-во столбцов
• Столбцы должны совпадать типами данных или быть приводимыми

6.

Объединение результатов нескольких
запросов
SELECT -- Q1
column1
,column2
,column3
FROM [TABLE_1] as t1 -- таблица или несколько
UNION / UNION ALL -- оператор объединения
SELECT
column1 -- порядок столбцов должен быть таким же, как в Q1
,column2
,column3
FROM { что угодно }
ORDER BY … -- сортировать можно только 1 раз

7.

Объединение результатов нескольких
запросов
UNION vs UNION ALL
• UNION ALL объединяет 2 набора данных и выводит все строки
• UNION объединяет 2 набора данных и убирает дубли строк
• Те - выводит только уникальные строки

8.

Обобщенное табличное выражение
Common Table Expression
CTE – способ оформления кода
• Не влияет на выполнение SQL запросов
• Во время выполнения запроса с CTE СУБД «переписывает» этот запрос и исключает CTE

9.

Common Table Expression
WITH [CTE_NAME] as (
SELECT *
FROM [TABLE_1] as t1 -- таблица или несколько
UNION ALL
SELECT *
FROM { что угодно }
)
SELECT *
FROM [CTE_NAME]

10.

Аналитические, Ранжирующие
Оконные функции
• Аналитические функции вычисляют статистическое значение на основе группы строк.
• Аналитические функции можно использовать для вычисления:
• скользящих средних, промежуточных итогов, процентных долей или первых N результатов в
группе.
• Ранжирующие функции возвращают ранжирующее значение для каждой строки в секции(группе).
• Аналитические и Ранжирующие функции – оконные функции, те функции которые рассчитываются в рамках
«окна» \ группы \ секции

11.

Оконные функции
• Задают правила разбиения строк на группы (секции, окна)
• Задают правила упорядочивания строк в группе
• Вычисляет значение для каждой строчке в окне
• Для вычислений могут использоваться:
• Ранжирующие функции
• Аналитические
• Агрегатные – Агрегатные функции могут «превращаться» в оконные

12.

Оконные функции
SELECT
FUNC( ? ) OVER( PARTITION BY Column1 ORDER BY Column 2, Column 3)
FROM [TABLE_1]
• FUNC( ? ) -- Аналитическая, Ранжирующая или Агрегатная функция
• OVER(PARTITION BY ? ORDER BY ? ) -- оператор окна, указывает СУБД, что нужно
применять «оконные механизмы», задает параметры разбиения на группы(окна) и
параметры сортировки
• PARTITION BY -- параметр разбиения на группы(окна), аналог GOUP BY
• ORDER BY – параметр сортировки внутри группы(окна)

13.

Оконные функции
Типовые задачи для оконных функций
• Нарастающий итог
• Ранжирование
• Добавление к строке «пред.\след.» значений какой-то величины
• Сумма продаж месяцем ранее для каждого месяца

14.

Ранжирующие функции
• ROW_NUMBER() – нумерует строки по порядку
• ROW_NUMBER() OVER(PARTITION BY ? ORDER BY ? )
• RANK () – Возвращает ранг строки в группе
• RANK () OVER(PARTITION BY ? ORDER BY ? )
• DENSE_RANK () – Возвращает ранг строки в группе без «разрывов»
• DENSE _RANK () OVER(PARTITION BY ? ORDER BY ? )
• NTILE (N) – Разбивает секцию на подгруппы на основании ORDER BY из OVER
• NTILE (N) OVER(PARTITION BY ? ORDER BY ? )

15.

Аналитические функции
• LAG(scalar_expression, offset, default ) – возвращает «предыдущее» значение
• LAG(?, ?, ? )OVER(PARTITION BY ? ORDER BY ? )
• scalar_expression – колонка, предыдущее значение которой ищем
• offset – смещение, сколько строк «назад» нужно отчитать.
• 1 – предыдущее, вчера
• 2 – позавчера, «позапредыдущее»
• 3 – 3 строки назад
• default – у последней строки не может быть предыдущего значения, default – значение для
последней строки, обычно 0.
• LEAD(scalar_expression, offset, default ) – возвращает «следующее» значение
• FIRST_VALUE(scalar_expression) – возвращает первое значение в группе
• FIRST_VALUE(?, ?, ? )OVER(PARTITION BY ? ORDER BY ? )
• LAST_VALUE(scalar_expression) – возвращает поседнее значение в группе

16.

Иерархическая структура
Потомок – предок, родитель - ребенок
• Примеры иерархической организации:
• Главная компания
• Дочерняя компания
• Маленькая дочерняя компания
• Маленькая дочерняя компания
• Страна
• Область
• Город
• Улица

17.

Рекурсивные запросы
А точнее - рекурсивные CTE
• Рекурсивные запросы применяются:
• Когда нужно нагенерить строки (календарь, последовательность чисел и тд)
• Когда нужно работать с иерархичными таблицами