Кейс по созданию витрины данных В прошлом посте я упомянул уже подготовленную витрину, которая состояла из таких полей: ▶️client_id - айди клиента ▶️salary_client - был ли клиент когда-либо зарплатным (да/нет)? ▶️any_deposit - открывал ли клиент хотя бы 1 вклад (да/нет)? ▶️during_salary - открыт ли хотя бы 1 вклад в то время, когда клиент был зарплатным (да/нет)? NULL - если клиент не был зарплатным. 🟠Как ее составить из стандартной БД? Предположим, что в БД есть таблицы с такими полями (помимо других): 1️⃣clients — справочник клиентов — client_id 2️⃣salary_periods — периоды, когда клиент был зарплатным — client_id — salary_start_date — salary_end_date (может быть NULL, если статус актуален) 3️⃣deposits — информация об открытых вкладах — deposit_id — client_id — open_date Для условности пускай витрина будет в виде мат. представления. Собирать витрину будем в помощью CTE, который будет иметь такую структуру: CREATE MATERIALIZED VIEW data_mart AS WITH salary_ever AS (Запрос №1), deposit_ever AS (Запрос №2), deposit_during_salary AS (Запрос №3), all_clients AS (Запрос №4) Запрос №5 Теперь подробнее о запросах. ▶️salary_ever (Запрос №1) SELECT DISTINCT client_id FROM salary_periods Здесь просто выбираются уникальные клиенты из зарплатной таблицы. Делается это для того, чтобы вывести тех клиентов, которые хоть когда-то были (или есть) зарплатными. ▶️deposit_ever (Запрос №2) SELECT DISTINCT client_id FROM deposits То же самое - уникальные клиенты из депозитов. Цель - вывести клиентов, которые хоть раз открывали депозит. ▶️deposit_during_salary (Запрос №3) SELECT DISTINCT d.client_id FROM deposits d JOIN salary_periods sp ON d.client_id = sp.client_id AND d.open_date >= sp.salary_start_date AND d.open_date <= COALESCE(sp.salary_end_date, CURRENT_DATE) Здесь цель - выбрать тех клиентов, которые открыли депозит именно тогда, когда были зарплатными. Для этого с помощью INNER JOIN присоединяем к первоначальной таблице deposits первоначальную таблицу salary_periods по client_id и по дате. Условие по дате - чтобы дата открытия депозита была между датой начала з/п периода и датой окончания з/п периода. Для случаев, если з/п период еще не закончился, можно прописать COALESCE(sp.salary_end_date, CURRENT_DATE) - это заменит NULL на текущую дату. ▶️all_clients (Запрос №4) SELECT client_id FROM clients UNION SELECT client_id FROM salary_periods UNION SELECT client_id FROM deposits Просто перестраховочный запрос, чтобы действительно вывести все уникальные client_id на тот случай, если таблица clients вдруг не полная. Но можно обойтись и без него, если таблица clients надежная. ▶️Запрос №5 SELECT ac.client_id, CASE WHEN se.client_id IS NOT NULL THEN 'да' ELSE 'нет' END AS salary_client, CASE WHEN de.client_id IS NOT NULL THEN 'да' ELSE 'нет' END AS any_deposit, CASE WHEN se.client_id IS NULL THEN NULL WHEN ds.client_id IS NOT NULL THEN 'да' ELSE 'нет' END AS during_salary FROM all_clients ac LEFT JOIN salary_ever se ON ac.client_id = se.client_id LEFT JOIN deposit_ever de ON ac.client_id = de.client_id LEFT JOIN deposit_during_salary ds ON ac.client_id = ds.client_id; В итоговом запросе ко всем client_id просто присоединяем все ранее полученные результаты: salary_ever, deposit_ever и deposit_during_salary. И потом просто по совпадениям можно определить: — Первый CASE: если есть пересечение с salary_ever - значит, определяем клиента как зарплатного. Если нет пересечения (NULL) - то, соответственно, наоборот. — Второй CASE: то же самое с депозитами. Если есть пересечение с any_deposit - значит, клиент хоть раз открывал депозит. Если нет, то, собственно, и нет — Третий CASE: отвечает на вопрос "открыт ли хотя бы 1 вклад в то время, когда клиент был зарплатным?". Если нет пересечения с salary_ever, то, значит, и открыть депозит клиент, будучи зарплатным, ну никак не мог. Ставим NULL в финальную витрину. Если пересечение всё же есть, то смотрим на