Представьте, что вы аналитик в банке, и вам пришла задача от руководителя собрать витрину данных для оценки кредитоспособности клиентов.
Чтобы вам это сделать, вам нужно собрать 1 таблицу с информацией:
Чтобы собрать такую витрину, вам нужно будет отдельно собирать промежуточные таблицы по каждому блоку, а затем объединить это все финальную таблицу.
Вот пример, как это может выглядеть:
-- 1. Промежуточная таблица с социальной информацией о клиентах
CREATE TABLE client_info AS
SELECT
c.client_id,
c.first_name,
c.last_name,
c.birth_date,
c.gender,
a.city,
a.region,
w.company,
w.position,
w.monthly_income,
COUNT(p.product_id) as total_products
FROM clients c
LEFT JOIN addresses a ON c.client_id = a.client_id
LEFT JOIN work_info w ON c.client_id = w.client_id
LEFT JOIN bank_products p ON c.client_id = p.client_id
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
-- 2. Промежуточная таблица с информацией о кредитах
CREATE TABLE credit_info AS
SELECT
cr.client_id,
COUNT(cr.credit_id) as total_credits,
SUM(cr.credit_amount) as total_credit_amount,
AVG(cr.interest_rate) as avg_interest_rate,
SUM(CASE WHEN cr.status = 'active' THEN cr.remaining_amount ELSE 0 END) as current_debt,
COUNT(CASE WHEN p.payment_status = 'overdue' THEN 1 END) as overdue_payments
FROM credits cr
LEFT JOIN credit_payments p ON cr.credit_id = p.credit_id
GROUP BY 1;
-- 3. Промежуточная таблица с информацией о дебетовых картах
CREATE TABLE debit_card_info AS
SELECT
d.client_id,
COUNT(d.card_id) as total_cards,
SUM(t.transaction_amount) as total_transactions_amount,
AVG(d.average_balance) as avg_balance,
COUNT(CASE WHEN t.transaction_type = 'withdrawal' THEN 1 END) as withdrawal_count,
COUNT(CASE WHEN t.transaction_type = 'purchase' THEN 1 END) as purchase_count
FROM debit_cards d
LEFT JOIN transactions t ON d.card_id = t.card_id
GROUP BY 1;
-- 4. Промежуточная таблица со статистикой транзакций
CREATE TABLE transaction_stats AS
SELECT
t.client_id,
DATE_TRUNC('month', t.transaction_date) as month,
COUNT(*) as monthly_transactions,
SUM(t.transaction_amount) as monthly_turnover,
AVG(t.transaction_amount) as avg_transaction_amount
FROM transactions t
GROUP BY 1, 2;
-- Финальная витрина данных
CREATE TABLE bank_analytics AS
SELECT
-- Информация о клиенте
ci.client_id,
ci.first_name,
ci.last_name,
ci.birth_date,
ci.city,
ci.region,
ci.monthly_income,
ci.total_products,
-- Информация о кредитах
cr.total_credits,
cr.total_credit_amount,
cr.avg_interest_rate,
cr.current_debt,
cr.overdue_payments,
-- Информация о дебетовых картах
dc.total_cards,
dc.total_transactions_amount,
dc.avg_balance,
dc.withdrawal_count,
dc.purchase_count,
-- Статистика транзакций
ts.monthly_transactions,
ts.monthly_turnover,
ts.avg_transaction_amount,
-- Расчётные метрики
(cr.current_debt / ci.monthly_income) as debt_to_income_ratio,
CASE
WHEN cr.overdue_payments = 0 THEN 'Excellent'
WHEN cr.overdue_payments <= 2 THEN 'Good'
WHEN cr.overdue_payments <= 5 THEN 'Average'
ELSE 'Poor'
END as credit_rating,
CASE
WHEN dc.avg_balance > 100000 THEN 'Premium'
WHEN dc.avg_balance > 50000 THEN 'Standard'
ELSE 'Basic'
END as client_segment
FROM client_info ci
LEFT JOIN credit_info cr ON ci.client_id = cr.client_id
LEFT JOIN debit_card_info dc ON ci.client_id = dc.client_id
LEFT JOIN transaction_stats ts ON ci.client_id = ts.client_id;
-- Удаление промежуточных таблиц
DROP TABLE IF EXISTS bank_analytics;
DROP TABLE IF EXISTS client_info;
DROP TABLE IF EXISTS credit_info;
DROP TABLE IF EXISTS debit_card_info;
DROP TABLE IF EXISTS transaction_stats;
Обратите внимание, что в конце мы удалили промежуточные таблицы, потому что они занимают место на сервере, но при это дальше мы их не планируем использовать. И еще позволяют избежать конфликта, когда мы будем запускать запрос заново.
Такой скрипт еще можно поставить на расписание, и у вашего руководителя будут каждый день актуальные данные:)
Почему такой подход используется на практике?