Бывает такое, что пересоздавать все таблицы заново неэффективно, в этом нам помогает INSERT INTO
c добавлением новых данных, например, за вчера.
Давайте посмотрим, как это могло бы выглядеть на нашем примере с банком.
-- 1. Добавление новых клиентских данных
INSERT INTO client_info
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
WHERE c.created_date = CURRENT_DATE - 1 -- данные за вчера
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
-- 2. Добавление новых кредитных данных
INSERT INTO credit_info
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
WHERE cr.created_date = CURRENT_DATE - 1 -- данные за вчера
GROUP BY 1;
-- 3. Добавление новых данных по дебетовым картам
INSERT INTO debit_card_info
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
WHERE d.created_date = CURRENT_DATE - 1 -- данные за вчера
GROUP BY 1;
-- 4. Добавление новой статистики транзакций
INSERT INTO transaction_stats
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
WHERE t.transaction_date = CURRENT_DATE - 1 -- данные за вчера
GROUP BY 1, 2;
-- 5. Обновление финальной витрины
INSERT INTO bank_analytics
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
WHERE ci.client_id IN (
SELECT client_id
FROM client_info
WHERE created_date = CURRENT_DATE - 1
);
Ключевые особенности этого подхода:
Такой подход позволяет: