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>
102 lines
5.6 KiB
SQL
102 lines
5.6 KiB
SQL
-- ===========================================================================
|
||
-- 98-bulk-seed.sql : 대량·다양 히스토리 시드 (다년치)
|
||
-- 기존 90-seed(2026-04-20~07-19, 3개월)의 앞쪽에 2021-01-01~2026-04-19 히스토리를
|
||
-- 붙여 볼륨을 수백만 건으로 확대한다. 성장추세·계절성·가맹점 생애주기 반영.
|
||
-- ID 는 1천만 오프셋 대역 사용(기존 1000~9999 및 라이브 채번과 비충돌).
|
||
-- idempotent: 이미 대량시드가 있으면(=오프셋 대역 존재) 건너뛴다.
|
||
-- ===========================================================================
|
||
SELECT setseed(0.73);
|
||
|
||
DO $$
|
||
DECLARE v_exists bigint;
|
||
BEGIN
|
||
SELECT count(*) INTO v_exists FROM purchase WHERE purchase_id >= 10000000;
|
||
IF v_exists > 0 THEN
|
||
RAISE NOTICE '대량시드 이미 적재됨(% 건) — 건너뜀', v_exists;
|
||
RETURN;
|
||
END IF;
|
||
|
||
-- -------------------------------------------------------------------------
|
||
-- 1. 가맹점 대량 확대 (M0041~M0200, 160개). MCC·업종·요율·개점일 다양.
|
||
-- -------------------------------------------------------------------------
|
||
INSERT INTO merchant (merchant_id, name, mdr_bps, daily_limit, status, created_at)
|
||
SELECT 'M' || lpad(g::text, 4, '0'),
|
||
(ARRAY['라온','블루','그린','썬','스카이','한빛','미소','골든','포레','오션',
|
||
'스타','로얄','네오','에버','휘게','코지','퓨어','모던','정성','신선'])[1 + (g % 20)]
|
||
|| ' ' ||
|
||
(ARRAY['마트','식당','카페','약국','의원','주유소','서점','전자','헤어살롱','헬스장',
|
||
'여행사','호텔','베이커리','문구','꽃집','세탁','안경','노래방','PC방','편의점'])[1 + ((g*7) % 20)]
|
||
|| ' ' || (g - 40) || '호점',
|
||
(ARRAY[15,18,20,22,24,25,26,27,28,29,30])[1 + (g % 11)],
|
||
(ARRAY[10000000,30000000,50000000,100000000,200000000,300000000,500000000])[1 + (g % 7)],
|
||
CASE WHEN g % 40 = 0 THEN 'CLOSED' WHEN g % 55 = 0 THEN 'HOLD' ELSE 'ACTIVE' END,
|
||
timestamptz '2020-06-01' + ((g - 40) * interval '11 days')
|
||
FROM generate_series(41, 200) g
|
||
ON CONFLICT (merchant_id) DO NOTHING;
|
||
|
||
-- 신규 가맹점 MCC 매핑 (기존 mm_merch_mcc 확장)
|
||
INSERT INTO mm_merch_mcc (merchant_id, mcc)
|
||
SELECT 'M' || lpad(g::text, 4, '0'),
|
||
(ARRAY['5411','5812','5814','5912','8062','5541','5942','5732','7230','7997',
|
||
'4722','7011','5462','5943','5992','7216','8043','7929','7994','5411'])[1 + ((g*7) % 20)]
|
||
FROM generate_series(41, 200) g
|
||
ON CONFLICT (merchant_id) DO NOTHING;
|
||
END $$;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2. 대량 히스토리 매입 (2021-01-01 ~ 2026-04-19).
|
||
-- 일 거래량 = 성장추세(연도↑) × 계절성(12월 피크) × 주중/주말.
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO purchase (purchase_id, merchant_id, amount, fee, net, status, biz_date,
|
||
channel, issuer, category, created_at, updated_at)
|
||
WITH days AS (
|
||
SELECT d::date AS biz_date,
|
||
extract(year FROM d)::int AS yy,
|
||
extract(month FROM d)::int AS mm,
|
||
extract(isodow FROM d)::int AS dow
|
||
FROM generate_series('2021-01-01'::date, '2026-04-19'::date, '1 day') d
|
||
),
|
||
vol AS (
|
||
SELECT biz_date, yy, mm, dow,
|
||
-- 연 성장(2021=0.5 → 2026=1.4) × 계절(12월 1.6, 1·2월 0.8) × 주말감소
|
||
GREATEST(50, (
|
||
(0.5 + (yy - 2021) * 0.18)
|
||
* CASE WHEN mm = 12 THEN 1.6 WHEN mm IN (1,2) THEN 0.8 WHEN mm IN (7,8) THEN 1.15 ELSE 1.0 END
|
||
* CASE WHEN dow >= 6 THEN 0.45 ELSE 1.0 END
|
||
* 900
|
||
)::int) AS ntx
|
||
FROM days
|
||
),
|
||
raw AS (
|
||
SELECT 10000000 + row_number() OVER (ORDER BY v.biz_date, gs) AS purchase_id,
|
||
v.biz_date,
|
||
'M' || lpad((1 + floor(random()*199))::int::text, 4, '0') AS merchant_id,
|
||
random() AS r_amt, random() AS r_ch, random() AS r_is, random() AS r_ct
|
||
FROM vol v CROSS JOIN LATERAL generate_series(1, v.ntx) gs
|
||
)
|
||
SELECT r.purchase_id, r.merchant_id,
|
||
(CASE WHEN r.r_amt < 0.55 THEN 3000 + floor(random()*47000)
|
||
WHEN r.r_amt < 0.87 THEN 50000 + floor(random()*250000)
|
||
WHEN r.r_amt < 0.97 THEN 300000+ floor(random()*1500000)
|
||
ELSE 2000000 + floor(random()*5000000) END)::bigint / 100 * 100 AS amount,
|
||
0, 0,
|
||
-- 과거일수록 정산완료(S) 비중↑, 소량 취소(C)/무효(V)
|
||
CASE WHEN r.r_ct < 0.955 THEN 'S' WHEN r.r_ct < 0.985 THEN 'C' ELSE 'V' END AS status,
|
||
r.biz_date,
|
||
CASE WHEN r.r_ch < 0.55 THEN 'POS' WHEN r.r_ch < 0.80 THEN 'EDC'
|
||
WHEN r.r_ch < 0.93 THEN 'EDI' ELSE 'FOREIGN' END AS channel,
|
||
CASE WHEN r.r_is < 0.26 THEN 'BC' WHEN r.r_is < 0.44 THEN 'KB'
|
||
WHEN r.r_is < 0.60 THEN 'SHINHAN' WHEN r.r_is < 0.73 THEN 'HYUNDAI'
|
||
WHEN r.r_is < 0.84 THEN 'SAMSUNG' WHEN r.r_is < 0.92 THEN 'LOTTE'
|
||
WHEN r.r_is < 0.97 THEN 'NH' ELSE 'HANA' END AS issuer,
|
||
CASE WHEN r.r_ch >= 0.93 THEN 'FX' ELSE 'GEN' END AS category,
|
||
r.biz_date::timestamptz + interval '9 hours' + (random() * interval '12 hours'),
|
||
r.biz_date::timestamptz + interval '9 hours' + (random() * interval '13 hours')
|
||
FROM raw r
|
||
WHERE NOT EXISTS (SELECT 1 FROM purchase p WHERE p.purchase_id >= 10000000 LIMIT 1);
|
||
|
||
-- 수수료/정산액 채우기 (mdr_bps 가맹점별)
|
||
UPDATE purchase p SET fee = p.amount * m.mdr_bps / 10000,
|
||
net = p.amount - p.amount * m.mdr_bps / 10000
|
||
FROM merchant m WHERE m.merchant_id = p.merchant_id
|
||
AND p.purchase_id >= 10000000 AND p.fee = 0;
|