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

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

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

Объединение результатов нескольких запросов
Клиенты
Клиенты Партнеров

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

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

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

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

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

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

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

Оконные функции
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 – параметр сортировки внутри группы(окна)

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

Ранжирующие функции
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 ? )

Аналитические функции
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) – возвращает поседнее значение в группе

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