64 подписчика
--OLAP
-- scenario1.sql
-- 5.2
CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$
DECLARE
test_rec record ;
BEGIN
WITH
branch_summary AS (
SELECT
b.bid,
COUNT(a.aid) as account_count,
SUM(a.abalance) as total_balance,
AVG(a.abalance) as avg_balance
FROM pgbench_branches b
LEFT JOIN pgbench_accounts a ON b.bid = a.bid
GROUP BY b.bid
),
transaction_summary AS (
SELECT
h.bid,
COUNT(*) as transaction_count,
SUM(h.delta) as net_flow,
COUNT(DISTINCT h.aid) as active_accounts,
MIN(h.mtime) as first_transaction,
MAX(h.mtime) as last_transaction
FROM pgbench_history h
WHERE h.mtime > CURRENT_TIMESTAMP - INTERVAL '7 days'
GROUP BY h.bid
)
SELECT
bs.bid,
bs.account_count,
bs.total_balance,
bs.avg_balance,
COALESCE(ts.transaction_count, 0) as transaction_count,
COALESCE(ts.net_flow, 0) as net_flow,
COALESCE(ts.active_accounts, 0) as active_accounts,
EXTRACT(DAY FROM ts.last_transaction - ts.first_transaction) as activity_days,
CASE
WHEN ts.transaction_count > 0
THEN bs.total_balance / ts.transaction_count
ELSE 0
END as balance_per_transaction
INTO test_rec
FROM branch_summary bs
LEFT JOIN transaction_summary ts ON bs.bid = ts.bid
WHERE bs.total_balance > 0
ORDER BY bs.total_balance DESC
LIMIT 100;
return 0 ;
END
$$ LANGUAGE plpgsql;
1 минута
Вчера