2 of 52

Реляционная модель

До этого на вход поступала только одна таблица.

Теперь будем рассматривать несколько таблиц.

Таблицы будут логически связаны.

В реляционной модели связь между таблицами удобнее всего использовать для формализации функций. Это отношение “многие к одному”.

Для частично определенных функций (многие к одному или нулю) есть оговорки.

Отношение “многие ко многим” затронем позднее.

3 of 52

CALENDAR

Список дат и их характеристики. Первичный ключ: calendar_dt

Фиксирует технические свойства даты.
Фиксирует бизнес-свойства даты.

Например, когда начинается и заканчивается первая неделя года.

Интерпретация: функции на датах, аргумент calendar_dt.

4 of 52

CORE_ACCOUNT

Депозиты клиентов. Первичный ключ: account_rk + valid_from_dttm

Это события обновления информации по депозитам.

Депозиты бывают закрытыми (объект прекратил существование). Наоборот, клиенты из CD_CUSTOMERS все бессмертные.

5 of 52

CORE_ACCOUNT

account_rk определяет конкретный депозит
customer_rk определяет клиента, которому принадлежит данный депозит (у одного клиента может быть много депозитов, а может и не быть вовсе)
apr_rt: процентная ставка по депозиту
term: срок депозита в днях
currency_cd: валюта депозита (RUB рубли, USD доллары)
account_opening_amt: размер депозита при открытии

6 of 52

CORE_ACCOUNT

account_renewal_cnt: номер открытия / пролонгации депозита�(1 - открытие, 2 и более - факт пролонгации одного и того же депозита)
renewed_dt: дата открытия / пролонгации депозита
expiration_dt: фактическая (в прошлом) или планируемая (в будущем) дата истечения срока депозита
valid_from_dttm / valid_to_dttm: границы существования версии депозита

Если депозит закрыт, то его последняя версия будет “закрыта” в прошлом!

7 of 52

CORE_ACCOUNT

8 of 52

Модель данных логическая для аналитики

9 of 52

Декартово произведение множеств

Декартово произведение двух множеств — это (неупорядоченное) множество, элементами которого являются всевозможные упорядоченные пары элементов исходных множеств.

Мощность результата равна произведению мощностей! �Иногда это очень много!

10 of 52

Декартово произведение множеств

???

11 of 52

Декартово произведение множеств

{2,4,7}x{2,4,7} = {(2,2),(2,4),(2,7),(4,2),(4,4),(4,7),(7,2),(7,4),(7,7)}

12 of 52

Декартово произведение таблиц

Таблица - множество строк.

???

13 of 52

Декартово произведение таблиц

Таблица - множество строк.

14 of 52

Декартово произведение таблиц

В (разумной) таблице не может быть двух разных столбцов об одном и том же

Проблема одноименности столбцов: с самого начала старайтесь не использовать слишком общие имена, например ID

15 of 52

Хотел сделать фото в ИТАЛИИ

Ожидание

16 of 52

Хотел сделать фото в ИТАЛИИ

Реальность

17 of 52

Реляционная алгебра. Ожидание и реальность.

В реляционной алгебре:

Таблицы не могут содержать одинаковых кортежей (строк)
Таблицы не могут содержать одинаковых имен атрибутов (колонок)

В базах данных:

Таблицы могут содержать одинаковые строки (есть скрытые поля)
Таблицы могут содержать столбцы с одинаковым названием

�Последнее нужно всячески избегать на этапе проектирования таблиц и работы с данными. В правильной базе данных ожидание = реальность!

18 of 52

Свойства декартова произведения таблиц

В отношении (таблице) колонки не упорядочены.�Это отличает декартово произведение таблиц от декартова произведения множеств.

Коммутативность (да для таблиц, нет для множеств)

TABLE1 x TABLE2 = TABLE2 x TABLE1�

Ассоциативность (да для таблиц, да для множеств)

TABLE1 x (TABLE2 x TABLE3) = (TABLE1 x TABLE2) x TABLE3

19 of 52

Соединение

Декартово

произведение

Фильтр�(WHERE)

Еще множество

Увеличивает результат

Уменьшает результат

20 of 52

Соединение (JOIN)

Соединение двух таблиц

Декартово произведение - основа любого соединения!�Остальное определяется типом соединения и может отсутствовать.

Соединения двух таблиц бывают разного типа

Соединение

Декартово

произведение

Фильтр�(WHERE)

Еще множество

Увеличивает результат

Уменьшает результат

21 of 52

Соединения

Соединения 3-х и более таблиц представляется последовательной цепочкой соединений двух таблиц.

Соединения таблиц ассоциативны.

�Некоторые соединения не коммутативны.

22 of 52

SELECT

В SELECT после ключевого слова FROM может быть более одной таблицы. Например, вот так:

SELECT ...�FROM TABLE1, TABLE2, ... , TABLEN;

Это и есть соединение таблиц.

В соединении возможно повторение одной и той же таблицы.

23 of 52

SELECT

В SELECT можно указывать поля из всех участвующих в соединении таблиц и выражения с ними.��select TEST_TABLE02.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02, asql.TEST_TABLE03;

Для колонок, которые есть в обеих таблицах, нужно явно указать откуда она.�

Слишком громоздко?

24 of 52

SELECT

Используем алиасы.��select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a, asql.TEST_TABLE03 b;

25 of 52

SELECT

Как ни странно, но так работать будет.��

select * �from asql.TEST_TABLE02, asql.TEST_TABLE03;

В чем проблема?

26 of 52

CROSS JOIN соединение

Декартово произведение в чистом виде (без дополнительных условий и множеств) называется CROSS JOIN. Можно записать двумя способами.�

select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a, asql.TEST_TABLE03 b;

select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a CROSS JOIN asql.TEST_TABLE03 b;

27 of 52

Вопрос

Таблица�N строк

Таблица�??? строк

CROSS JOIN

Таблица�M строк

28 of 52

INNER JOIN соединение

Декартово произведение с фильтром называется INNER JOIN. Можно записать двумя способами.

select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a, asql.TEST_TABLE03 b�where a.customer_rk = b.customer_rk;

select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a INNER JOIN asql.TEST_TABLE03 b� on a.customer_rk = b.customer_rk;

29 of 52

INNER JOIN соединение

Фильтр (условие) может быть любым.

select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a, asql.TEST_TABLE03 b�where a.monthly_income_amt >= b.balance_amt;

30 of 52

INNER JOIN

select * �from asql.TEST_TABLE02 a, asql.TEST_TABLE03 b�where a.customer_rk = b.customer_rk;

31 of 52

INNER JOIN

Логику можно представить по другому.

В результат попадут

все строки из A, для которых WHERE=TRUE хоть раз (пересекаются с B).
все строки из B, для которых WHERE=TRUE хоть раз (пересекаются с A).�

32 of 52

Вопрос

Таблица�N строк

Таблица�??? строк

INNER JOIN

Таблица�M строк

33 of 52

LEFT JOIN соединение

Декартово произведение с фильтром и дополнительным множеством из левой таблицы называется LEFT OUTER JOIN, или сокращенно LEFT JOIN.

�select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a LEFT JOIN asql.TEST_TABLE03 b� on a.customer_rk = b.customer_rk;

�Дополнительно к INNER JOIN из левой таблицы берутся строки, для которых не нашлось ни одного совпадения по условию ON. Для таких записей поля, соответствующие правой таблице, заполняются значениями NULL.

34 of 52

LEFT JOIN

select * �from asql.TEST_TABLE02 a LEFT JOIN asql.TEST_TABLE03 b � on a.customer_rk = b.customer_rk;

35 of 52

LEFT JOIN

В результат попадет декартово произведение где останутся

все строки из A, для которых ON=TRUE хоть раз (пересекаются с B).
все строки из B, для которых ON=TRUE хоть раз (пересекаются с B).

Плюс

все строки из A, для которых ON!=TRUE для всех B (не пересекается с B).

36 of 52

LEFT JOIN

В результирующей таблице встретятся ровно один раз, причём остальные поля будут содержать NULL

В результат LEFT JOIN всегда попадут все строки из левой таблицы вне зависимости от условия ON.

Те строки, для которых условие ON выполняется хотя бы раз

В результирующей таблице встретятся ровно столько раз, сколько сработало условие ON

Те строки, для которых условие ON ни разу не выполнилось

37 of 52

Задача

Вывести клиентов (customer_rk) с количеством их действующих рублевых депозитов, причём только тех клиентов, у которых есть хотя бы один такой депозит.

Подсказка: соединения таблиц вам пока не понадобятся.

38 of 52

Задача

39 of 52

Задача

Вывести всех клиентов (customer_rk) с количеством их действующих рублевых депозитов.

40 of 52

Задача

41 of 52

Задача

Вывести всех клиентов (customer_rk) с количеством их действующих рублевых депозитов и количеством их действующих долларовых депозитов.

42 of 52

Задача

43 of 52

RIGHT JOIN соединение

Декартово произведение с фильтром и дополнительным множеством из правой таблицы называется RIGHT OUTER JOIN. Или RIGHT JOIN.

�select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a RIGHT JOIN asql.TEST_TABLE03 b� on a.customer_rk = b.customer_rk;

�RIGHT JOIN - это тоже самое что и LEFT JOIN, но для правой таблицы.

44 of 52

RIGHT JOIN

RIGHT JOIN - зеркальное отражение LEFT

A RIGHT JOIN B = B LEFT JOIN A

45 of 52

RIGHT JOIN

RIGHT JOIN - зеркальное отражение LEFT

Почему в зеркале лево/право меняется местами, а верх и низ - нет?

A RIGHT JOIN B = B LEFT JOIN A

46 of 52

RIGHT JOIN vs LEFT JOIN

RIGHT JOIN всегда можно заменить на LEFT JOIN.

Старайтесь избегать RIGHT JOIN там, где есть возможность использовать LEFT JOIN. “Левое” соединение интуитивно более понятно и гораздо легче читается в коде, чем “правое”. Ведь читаем мы слева направо.

47 of 52

OUTER JOIN соединение

Декартово произведение с фильтром и дополнительным множеством из обеих таблиц называется FULL OUTER JOIN. Или OUTER JOIN. Или FULL JOIN.

select a.customer_rk, first_nm, balance_amt �from asql.TEST_TABLE02 a OUTER JOIN asql.TEST_TABLE03 b� on a.customer_rk = b.customer_rk;��Из левой таблицы берутся строки для которых не нашлось ни одного совпадения по условию ON. Для таких записей поля, соответствующие правой таблице, заполняются значениями NULL. ��Из правой таблицы берутся строки для которых не нашлось ни одного совпадения по условию ON. �Для таких записей поля, соответствующие левой таблице, заполняются значениями NULL.

48 of 52

OUTER JOIN

В результат попадет декартово произведение, где останутся

все строки из A, для которых ON = TRUE (пересекаются с B).
все строки из B, для которых ON = TRUE (пересекаются с B).

Плюс

все строки из A, для которых ON!=TRUE для всех B (не пересекается с B).
все строки из B, для которых ON!=TRUE для всех A (не пересекается с A).

49 of 52

Вопрос

Таблица�N строк

Таблица�??? строк

FULL JOIN

Таблица�M строк

50 of 52

Задача 3.1

Вывести текущее состояние клиентов (customer_rk + ФИО) с указанием “степени популярности” их ФИО.

Степень популярности - это количество таких ФИО среди всех клиентов на текущий момент времени.

51 of 52

Задача 3.2

На каждую дату вывести количество открытых, но еще не закрытых депозитов к этой дате включительно. Даты: включительно с даты открытия первого счета по сегодня. Пролонгированный депозит считается открытым.

Примечание. Не в каждую дату происходит открытие или закрытие депозита. Даже если этого не произошло, дата все равно должна попасть в итоговый список.

52 of 52

Задача 3.3

Для каждого года вывести количество

открытий новых депозитов,
пролонгаций,
пролонгаций, когда сумма депозита вырастала более чем на 50% по сравнению с прошлым периодом действия депозита.

Пролонгация - новый период действия старого депозита.