Similar presentations:
Дополнительные возможности 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
1000
Иванов И.И.
mail1@mail
1001
…
…
1002
…
…
Client_Id
FIO
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 ExpressionWITH [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
• Рекурсивные запросы применяются:
• Когда нужно нагенерить строки (календарь, последовательность чисел и тд)
• Когда нужно работать с иерархичными таблицами