Data Engineer. Лекция 3
Наливайте чай,
приветствуйте друг друга в чате и приготовьтесь хорошо провести время
Про что сегодня �расскажем
Наливайте чай,
приветствуйте друг друга в чате и приготовьтесь хорошо провести время
Представление (на сленге «вьюха») – SQL код, сохраненный в справочнике базы данных под своим собственным именем. Данный код исполняется каждый раз при обращении к представлению. Обратите внимание, что представление само по себе не хранит данные, только SQL код.
Представления используются для двух целей:
CREATE VIEW SchemaName.ViewName AS
SELECT * FROM SchemaName.TableName;
CREATE OR REPLACE VIEW SchemaName.ViewName AS
SELECT * FROM SchemaName.TableName WHERE 1=1;
DROP VIEW SchemaName.ViewName;
SQL. Представления
Создайте представление над таблицей hr.employees, скрывающее данные о зарплате сотрудников. В представлении должны быть поля first_name, last_name, email, phone_number.
SQL. Подзапросы
4-3. Руководитель хочет выплатить премию всем сотрудникам подразделений, перечисленных в таблице hr.dep_bonus. Выведите список таких сотрудников из таблицы hr.employees, воспользовавшись только подзапросами.
Пример использования подзапроса – вывести всех сотрудников, кто работает продажниками (в названии работы есть слово «Sales»):
SELECT
*
FROM hr.employees
WHERE job_id IN (
SELECT
job_id
FROM hr.jobs
WHERE job_title LIKE 'Sales%'
);
Common Table Expression
WITH cte_name (column_list) AS (
CTE_query_definition
),
cte_name2 (column_list) AS (
CTE_query_definition
)
statement;
Временная таблица, которая существует только во время выполнения запроса
SQL. Группировка и агрегатные функции
SELECT * FROM SchemaName.TableName1
UNION [ALL]
SELECT * FROM SchemaName.TableName2;
SQL. Вертикальное соединение таблиц
1. Сделайте копию таблицы hr.employees.
2. Соедините вашу копию и таблицу hr.employees с помощью запроса выше без указания ALL и с указанием ALL. В чем разница?
3. Попробуйте соединить несоединимое.
JOIN quiz
1. Есть две таблицы, в одной из них 10 записей, в другой – 100. Какое минимальное и максимальное количество записей можно получить используя разные виды соединений?
2. Соедините две таблицы всеми видами соединений (без CROSS JOIN). Соединение происходит следующим шаблоном запроса:
SELECT Table1.ID ID_1, Table2.ID ID_2
FROM Table1
<вид соединения> JOIN Table2
ON Table1.ID = Table2.ID;
JOIN quiz
Table1 | | Table2 |
ID | | ID |
1 | | 2 |
2 | | null |
3 | | 3 |
4 | | 3 |
null | | 5 |
| | null |
| | |
| | |
| | |
2. Соедините две таблицы всеми видами соединений (без CROSS JOIN). Соединение происходит следующим шаблоном запроса:
SELECT Table1.ID ID_1, Table2.ID ID_2
FROM Table1
<вид соединения> JOIN Table2
ON Table1.ID = Table2.ID;
JOIN quiz
Table1 | | Table2 | | Result (inner) | |
ID | | ID | | ID_1 | ID_2 |
1 | | 2 | | 2 | 2 |
2 | | null | | 3 | 3 |
3 | | 3 | | 3 | 3 |
4 | | 3 | | | |
null | | 5 | | | |
| | null | | | |
| | | | | |
| | | | | |
| | | | | |
2. Соедините две таблицы всеми видами соединений (без CROSS JOIN). Соединение происходит следующим шаблоном запроса:
SELECT Table1.ID ID_1, Table2.ID ID_2
FROM Table1
<вид соединения> JOIN Table2
ON Table1.ID = Table2.ID;
JOIN quiz
Table1 | | Table2 | | Result (left) | |
ID | | ID | | ID_1 | ID_2 |
1 | | 2 | | 2 | 2 |
2 | | null | | 3 | 3 |
3 | | 3 | | 3 | 3 |
4 | | 3 | | 1 | null |
null | | 5 | | 4 | null |
| | null | | null | null |
| | | | | |
| | | | | |
| | | | | |
2. Соедините две таблицы всеми видами соединений (без CROSS JOIN). Соединение происходит следующим шаблоном запроса:
SELECT Table1.ID ID_1, Table2.ID ID_2
FROM Table1
<вид соединения> JOIN Table2
ON Table1.ID = Table2.ID;
JOIN quiz
Table1 | | Table2 | | Result (right) | |
ID | | ID | | ID_1 | ID_2 |
1 | | 2 | | 2 | 2 |
2 | | null | | 3 | 3 |
3 | | 3 | | 3 | 3 |
4 | | 3 | | null | null |
null | | 5 | | null | 5 |
| | null | | null | null |
| | | | | |
| | | | | |
| | | | | |
2. Соедините две таблицы всеми видами соединений (без CROSS JOIN). Соединение происходит следующим шаблоном запроса:
SELECT Table1.ID ID_1, Table2.ID ID_2
FROM Table1
<вид соединения> JOIN Table2
ON Table1.ID = Table2.ID;
JOIN quiz
Table1 | | Table2 | | Result (full) | |
ID | | ID | | ID_1 | ID_2 |
1 | | 2 | | 2 | 2 |
2 | | null | | 3 | 3 |
3 | | 3 | | 3 | 3 |
4 | | 3 | | 1 | null |
null | | 5 | | 4 | null |
| | null | | null | null |
| | | | null | null |
| | | | null | 5 |
| | | | null | null |
Горизонтальное соединение. JOINs. CROSS JOIN.
SELECT * FROM TableName1
CROSS JOIN TableName2
SELECT * FROM TableName1, TableName2
SQL. Задачи на соединения
начальников (фамилия, имя). (hr.employees)
сотрудников, которые получают заработную плату больше
своего менеджера. (hr.employees)
совершил ни одной продажи в марте 2007 года.
(hr.employees, oe.orders)
SQL. Группировка и агрегатные функции
Пример. Сколько сотрудников в каждом отделе?
SELECT
DepartmentID,
COUNT(*) as Kolichestvo
FROM hr.employees
GROUP BY DepartmentID;
Пример. В каких отделах больше 5 работников?
SELECT
DepartmentID,
COUNT(*) as Kolichestvo
FROM hr.employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;
Аналитические (оконные) функции
Синтаксис оконной функции:
analytic_function() OVER (
[PARTITION BY expression1, expression2,...]
ORDER BY expression1 [ASC | DESC], expression2,... )
SELECT
SUM( value ) OVER( ORDER BY value ) rn
FROM
OrderDetails;
SELECT
SUM(value1) OVER(PARTITION BY id ORDER BY value1) value2
FROM
OrderDetails;
Наиболее употребляемые аналитические функции:
AVG, COUNT, DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE,
LEAD, MAX, MEDIAN, MIN, RANK, ROW_NUMBER, SUM
Аналитические функции. Окно.
Аналитические (оконные) функции
Синтаксис оконной функции:
analytic_function() OVER (
[PARTITION BY expression1, expression2,...]
ORDER BY expression1 [ASC | DESC], expression2,... )
SELECT
SUM(value1) OVER(PARTITION BY id ORDER BY value1) value2
FROM
OrderDetails;
Наиболее употребляемые аналитические функции:
AVG, COUNT, DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE,
LEAD, MAX, MEDIAN, MIN, RANK, ROW_NUMBER, SUM
SCD версионность
SCD0
Неизменяемые данные
SCD версионность
SCD1
SCD версионность
SCD2
SCD версионность
SCD2
Типовые действия с форматом SCD2
SCD версионность. Задача
*** FDTD join
Логические удаления
Актуальный (активный) срез
Домашнее задание 1
Часть 1
Создайте структуру базы данных по предложенной тематике
К базе данных предъявляются следующие требования:
Предметная область (выберите одно):
1. Продажа автомобилей.
2. Приют для животных.
3. Железнодорожные перевозки.
4. Служба доставки.
5. Организация марафона.
Требования к оформлению:
Наливайте чай,
приветствуйте друг друга в чате и приготовьтесь хорошо провести время
Часть 2
• PAYMENT_DT - дата выплаты,
• MONTH_PAID - суммарно выплачено в месяце на дату последней выплаты,
• MONTH_REST - осталось выплатить за месяц.
Проверяется в первую очередь понимание как соединять фактовую таблицу с SCD2 таблицей (нельзя все расчеты сделать над de.salary_payments, ведь работнику могут недоплатить или переплатить).
В ответе приложите SQL скрипт, таблица ****_SALARY_LOG должна быть заполнена.
Пожалуйста, будьте внимательны к ТЗ (названия таблиц, полей, формат сдачи и т.п.)!
Дедлайн: 1 неделя с момента выдачи
SQL. Строковые функции
Возьмите в работу таблицу DE.BANK_CLIENTS. Создайте представ-ление XXXX_V_BANK_CLIENTS, выводящее фамилию и имя клиента одним полем, в формате с первой заглавной буквой; номер счета; замаскированный номер карты (маскировка обычно осуществляется путем замены цифр во второй и третьей группе на знак «*»), сумму на счете в формате 000001.23 (6 знаков перед точкой).
Функция | Назначение |
lower( 'Example' ) | Переводит все символы строки в нижний регистр ('example'). |
upper( 'Example' ) | Переводит все символы строки в верхний регистр ('EXAMPLE'). |
initcap( 'example TWO' ) | Переводит первый символ каждого слова в верхний регистр, остальные – в нижний ('Example Two’). |
lpad( '1234', 8, '0' ) rpad( '1234', 8, '0' ) | Дополняет строку из первого параметра строкой из третьего параметра до общей длины строки, указанной во втором параметре. Дополнение проводится слева и справа соответственно ('00001234', '12340000'). |
ltrim( ' ABC DEF ' ) rtrim( ' ABC DEF ' ) trim( ' ABC DEF ' ) | Удаляет пробелы слева, справа, с обеих строн строки соответственно ('ABC DEF ', ' ABC DEF', 'ABC DEF'). |
SQL. Задачи на обработку строк
Приведите в порядок таблицу DE.DATASOURCE: корректно заполните поля first_name, last_name; разделите поле email на email и phone; телефон отформатируйте по маске +7 (123) 456-78-90; унифицируйте поле gender.�
Воспользуйтесь таблицами DE.BANK_CLIENTS и DE.BANK_TRANSACTIONS, на основании них создайте отчет по всем клиентам, в котором будут поля фамилия, имя и оборот за октябрь.
Очень коротко о регулярных выражениях
Найдите сотрудников, у которых в имени есть одна из букв (a,f,r,t)
Найдите сотрудников, у которых имя начинается с одной из букв (a,f,r,t)
SQL. Расширение строковых функций на регулярные выражения
Функция | Назначение |
regexp_like( 'Year of 2017', '\d+’ ) 'Year of 2017' ~ '\d+' | Поиск по регулярному выражению (возвращает «истина» или «ложь»). |
regexp_count( 'Year of 2017 52', '\d+' ) | Подсчет количества найденных вхождений регулярного выражения. |
regexp_substr( 'Year of 2017', '\d+' ) regexp_match( 'Year of 2017', '\d+' ) | Возвращает подстроку, найденную по регулярному выражению. |
regexp_instr( 'Year of 2017', '\d+' ) | Возвращает позицию найденной подстроки по регулярному выражению. |
regexp_replace( 'Year of 2017', '\d+', 'Dragon’ ) regexp_replace( 'Year of 2017', '\d+', 'Dragon' ) | Заменяет все вхождения, найденные по регулярному выражению на подстроку. |
SQL. Задачи на обработку строк регулярными выражениями
SQL. Задачи на обработку строк регулярными выражениями
Change data capture (CDC) – захват данных
Способы отследить происходящие изменения:
Пробуем собрать ETL�на SCD1
План
1. Очистка стейджинговых таблиц
2. Захват данных из источника (измененных с момента последней загрузки) в стейджинг
3. Захват в стейджинг ключей из источника полным срезом для вычисления удалений.
4. Загрузка в приемник "вставок" на источнике (формат SCD1).
5. Обновление в приемнике "обновлений" на источнике (формат SCD1).
6. Удаление в приемнике удаленных в источнике записей (формат SCD1).
7. Обновление метаданных.
8. Фиксация транзакции.
А как же SCD2?
Обновим скрипт до SCD2 на следующем занятии