ab7983318d
기존 3개월치(~20만행)를 5.5년치 대량 히스토리로 확대. 성장추세·계절성· 가맹점 생애주기 반영. ID 오프셋 대역(1천만+)으로 기존/라이브 채번과 비충돌, 전부 idempotent 가드. - 98-bulk-seed.sql: 가맹점 40→200(MCC·요율·개점일·CLOSED/HOLD 다양) + 매입 135만건(2021-01~2026-04, 성장추세 연 0.5→1.4배·12월 계절피크·주중주말· 다양 채널/발급사/카드종류/금액구간). 27초. - 99-bulk-derive.sql: 승인 135만·원장 2.6M(RECON+SETTLE)·정산 1.29M· 가맹점집계 358k. 시퀀스 정렬. 31초. - 99b-bulk-econ.sql: 정산경제 파생 — 수수료3층분해 1.29M·프레젠트먼트 1.29M· ISO8583 클리어링 1.29M·순액정산(발급사×일) 16k·펀딩 355k. - 99c-bulk-index.sql: 운영 인덱스 14개(biz_date/merchant/status 등) + ANALYZE. 검증 (실측) - 총 1,130만 행, DB 1.8GB. 연도별 성장 2021년 14.5만→2025년 35.3만건. - 3층 항등식 1,293,145/1,293,145, 펀딩 항등식 355,284/355,284 (100% 성립). - 대시보드/정산경제 화면 정상: 활성가맹점 191, 총매입 3,997억, 입금률 95.7%. - 집계쿼리 0.5초(인덱스). 최근 3개월 라이브 데이터 무손상. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
77 lines
4 KiB
SQL
77 lines
4 KiB
SQL
-- ===========================================================================
|
||
-- 99b-bulk-econ.sql : 대량 히스토리 매입의 정산 경제 파생.
|
||
-- 수수료 3층 분해(MCC interchange)·프레젠트먼트·순액정산·펀딩·ISO8583 클리어링.
|
||
-- 기존 96/97 econ 로직과 동일 산식. idempotent 가드(오프셋 대역 존재 검사).
|
||
-- ===========================================================================
|
||
|
||
-- 1. 수수료 3층 분해 (MCC interchange 우선, 채널 폴백, scheme=BC)
|
||
INSERT INTO st_fee_component (purchase_id, amount, interchange, scheme_fee, markup, mdr_total, card_type, scheme)
|
||
SELECT p.purchase_id, p.amount,
|
||
ic.amt,
|
||
p.amount * 11 / 10000,
|
||
GREATEST(p.fee - ic.amt - p.amount*11/10000, p.amount*8/10000),
|
||
ic.amt + p.amount*11/10000
|
||
+ GREATEST(p.fee - ic.amt - p.amount*11/10000, p.amount*8/10000),
|
||
t.ct, 'BC'
|
||
FROM purchase p
|
||
CROSS JOIN LATERAL (
|
||
SELECT (CASE WHEN (p.purchase_id % 10) < 3 THEN 'CHECK' ELSE 'CREDIT' END) AS ct
|
||
) t
|
||
CROSS JOIN LATERAL (
|
||
SELECT p.amount * COALESCE(
|
||
(SELECT r.rate_bps FROM mm_merch_mcc mm
|
||
JOIN st_interchange_rate r ON r.card_type = t.ct AND r.mcc = mm.mcc
|
||
AND r.channel='DEFAULT' AND r.txn_type='SALE'
|
||
WHERE mm.merchant_id = p.merchant_id LIMIT 1),
|
||
130) / 10000 AS amt
|
||
) ic
|
||
WHERE p.purchase_id >= 10000000 AND p.status = 'S'
|
||
AND NOT EXISTS (SELECT 1 FROM st_fee_component f WHERE f.purchase_id >= 10000000 LIMIT 1);
|
||
|
||
-- 2. 프레젠트먼트(클리어링) — amount − interchange
|
||
INSERT INTO cl_presentment (presentment_id, purchase_id, scheme, msg_class, clearing_amt, present_date, cycle, status)
|
||
SELECT nextval('cl_present_seq'), fc.purchase_id, 'BC', 'FIRST_PRESENT',
|
||
fc.amount - fc.interchange, p.biz_date, 'T+1', 'CLEARED'
|
||
FROM st_fee_component fc JOIN purchase p ON p.purchase_id = fc.purchase_id
|
||
WHERE fc.purchase_id >= 10000000
|
||
AND NOT EXISTS (SELECT 1 FROM cl_presentment c WHERE c.purchase_id >= 10000000 LIMIT 1);
|
||
|
||
-- 3. ISO 8583 클리어링 PRESENTMENT (이중/단일)
|
||
INSERT INTO mg_clearing (clr_id, purchase_id, scheme, msg_class, mti, msg_type, amount, biz_date, status)
|
||
SELECT nextval('mg_clearing_seq'), fc.purchase_id, 'BC', 'PRESENTMENT', '1240',
|
||
CASE WHEN fc.card_type='CHECK' THEN 'SINGLE' ELSE 'DUAL' END,
|
||
fc.amount - fc.interchange, p.biz_date, 'ACCEPTED'
|
||
FROM st_fee_component fc JOIN purchase p ON p.purchase_id = fc.purchase_id
|
||
WHERE fc.purchase_id >= 10000000
|
||
AND NOT EXISTS (SELECT 1 FROM mg_clearing c WHERE c.purchase_id >= 10000000 LIMIT 1);
|
||
|
||
-- 4. 순액정산 (발급사 BIN × 영업일 집계)
|
||
INSERT INTO st_net_settlement (net_id, scheme, member_bin, biz_date, txn_count,
|
||
gross_amt, interchange_amt, scheme_fee_amt, net_amt, direction, status)
|
||
SELECT nextval('st_netset_seq'), 'BC', mb.bin, p.biz_date, count(*),
|
||
sum(fc.amount), sum(fc.interchange), sum(fc.scheme_fee),
|
||
sum(fc.amount - fc.interchange - fc.scheme_fee), 'RECEIVE', 'SETTLED'
|
||
FROM st_fee_component fc
|
||
JOIN purchase p ON p.purchase_id = fc.purchase_id
|
||
JOIN st_member_bank mb ON mb.bank_code = p.issuer
|
||
WHERE fc.purchase_id >= 10000000
|
||
GROUP BY mb.bin, p.biz_date
|
||
ON CONFLICT (scheme, member_bin, biz_date) DO NOTHING;
|
||
|
||
-- 5. 펀딩 항등식 (가맹점 × 영업일)
|
||
INSERT INTO st_merchant_funding (funding_id, merchant_id, biz_date, gross_sales, refunds,
|
||
chargebacks, fees, reserve, deposit, funding_model, status)
|
||
SELECT nextval('st_funding_seq'), s.merchant_id, s.biz_date,
|
||
s.gross, s.refunds, 0, s.fees, 0,
|
||
s.gross - s.refunds - s.fees, 'NET_DAILY', 'FUNDED'
|
||
FROM (
|
||
SELECT p.merchant_id, p.biz_date,
|
||
sum(CASE WHEN p.status='S' THEN p.amount ELSE 0 END) gross,
|
||
sum(CASE WHEN p.status='C' THEN p.amount ELSE 0 END) refunds,
|
||
coalesce(sum(fc.mdr_total),0) fees
|
||
FROM purchase p LEFT JOIN st_fee_component fc ON fc.purchase_id=p.purchase_id
|
||
WHERE p.purchase_id >= 10000000
|
||
GROUP BY p.merchant_id, p.biz_date
|
||
) s
|
||
WHERE s.gross > 0
|
||
ON CONFLICT (merchant_id, biz_date) DO NOTHING;
|