SQL을 활용한 업무 카드 결제 데이터 검증 및 배치잡 프로시저 생성

1. 프로젝트 개요

▶ 프로젝트 제목: SQL을 활용한 업무 카드 결제 데이터 검증 및 알림 시스템 구축

▶ 프로젝트 목표:

• 매출 데이터에서 카드 결제 정보를 검증하고 카드사 누락 데이터 방지
• SQL을 활용하여 CARDKIND 값이 NULL인 데이터를 탐색 및 수정
• 데이터 정합성을 유지하며 정확한 매출 데이터를 유지하기 위한 최적화 진행
• 배치 프로시저를 활용하여 오류 발생 시 그룹웨어 메일로 자동 알림 발송 시스템 구축

---

2. 배경 및 문제 정의

▶ 기존 문제점:

1. 카드사 정보 누락 (CARDKIND 값 NULL 발생)
   • 포스기에서 입력된 DB 데이터가 포스 통신서버를 통해 매출 데이터로 생성됨
   • 카드사 정보가 지속적으로 업데이트되며, 신규 카드사는 CARDKIND 값이 NULL로 들어옴
   • 누락된 카드 정보를 정확하게 매칭하지 않으면 매출 데이터 카드 입금 정산 오류 발생
2. 카드 결제 데이터 매칭 필요
   • 통신서버에서 결제 내역 데이터를 조회하여 영수증 매출 카드 결제 정보와 매칭 필요
   • 카드사 정보를 매칭한 후 카드 결제 CARDKIND 값을 업데이트하여 데이터 정합성 유지
3. SQL을 통한 데이터 자동 업데이트 필요
   • 기존에는 수동으로 카드사 정보를 확인 후 업데이트 진행
   • SQL을 활용하여 자동화된 카드 정보 매칭 및 업데이트 프로세스 구축 필요
4. 자동 오류 알림 시스템 필요
   • NULL 값이 지속적으로 발생하는 카드 데이터를 모니터링해야 함
   • 배치 프로시저를 통해 오류 발생 시 담당자에게 그룹웨어 메일 자동 발송 기능 추가

---

3. 해결 방법

▶ 카드사 정보를 업데이트하는 SQL 쿼리 작성

✅ CARDKIND 값이 NULL인 데이터를 탐색

SELECT a.sale_date, a.shop_cd, a.custname, b.cardkind, b.card_no
FROM 영수증 테이블 a
INNER JOIN 카드 테이블 b
    ON 조인조건 작성 = 조인조건 작성
    AND a.sale_date = b.sale_date
    AND a.pos_no = b.pos_no
    AND a.bill_no = b.bill_no
WHERE b.cardkind IS NULL;

✅ NULL 값인 카드사 정보를 조회하여 수정이 필요한 데이터 파악

✅ CARDKIND 업데이트를 위한 SQL 쿼리

UPDATE 카드 테이블
SET cardkind = t.correct_cardkind
FROM 카드 테이블 sc
JOIN (
    SELECT card_no, matched.cardkind AS correct_cardkind
    FROM 결제 내역 테이블 ct
    INNER JOIN 카드사 마스터 matched
        ON ct.card_no = matched.card_no
    WHERE ct.approval_date BETWEEN '2024-01-01' AND '2024-12-31'
) t
ON sc.card_no = t.card_no
WHERE sc.cardkind IS NULL;

📌 설명: ✅ 결제 내역 테이블에서 결제 내역을 조회하여 카드사 마스터와 매칭 ✅ 카드 테이블에서 CARDKIND 값이 NULL인 데이터를 찾아 업데이트 ✅ 자동화된 SQL을 통해 매출 데이터 정합성을 유지

---

▶ 오류 감지를 위한 배치 프로시저 구현

✅ NULL 값 발생 시 그룹웨어 메일 자동 발송

DECLARE @error_count INT;
DECLARE @error_details NVARCHAR(MAX);

SELECT @error_count = COUNT(*)
FROM 카드 테이블
WHERE cardkind IS NULL;

-- 오류 발생 시 상세 정보 포함하여 메일 발송
IF @error_count > 0
BEGIN
    SELECT @error_details = STRING_AGG(
        '영수증번호: ' + CAST(bill_no AS NVARCHAR) +
        ', 매장코드: ' + shop_cd +
        ', 결제일자: ' + CONVERT(NVARCHAR, sale_date, 23) +
        ', 포스번호: ' + POS_NO +
    )
    FROM 카드 테이블
    WHERE cardkind IS NULL;

    EXEC sp_send_dbmail 
    @profile_name = '담당자이름',
    @recipients = 'admin@company.com',
    @subject = '카드 결제 데이터 확인',
    @body = 'NULL 값이 포함된 카드 결제 데이터가 발생했습니다.\n\n' + @error_details;
END;

 

📌 설명:

✅ 기존 NULL 값 개수만 확인하는 방식에서 오류 상세 내역 포함하여 메일 전송

✅ STRING_AGG()를 활용하여 영수증번호, 매장코드, 결제일자, 카드번호 등 포함

✅ 담당자가 이메일을 받으면 즉시 오류 데이터를 확인하고 조치 가능

✅ 배치 작업을 등록하여 일정 주기로 오류 감지 실행 가능

---

▶ SQL 성능 최적화 적용

✅ card_no 및 cardkind 관련 인덱스 추가

CREATE INDEX idx_card_no ON 카드 테이블(card_no);
CREATE INDEX idx_card_transaction ON 결제 내역 테이블(card_no, approval_date);
CREATE INDEX idx_card_master ON 카드사 마스터(card_no);

📌 설명:

✅ 인덱스를 활용하여 카드 정보 매칭 속도를 향상

✅ Full Table Scan을 방지하여 조회 및 업데이트 성능 최적화

---

4. 프로젝트 결과

▶ 카드사 정보 정합성 개선

• NULL 값 탐색 및 자동 업데이트로 데이터 정합성 유지
• 신규 카드사의 정보가 지속적으로 업데이트되면서 오류 발생 방지
• 배치 프로시저를 통한 오류 감지 및 자동 알림 시스템 구축

▶ SQL 성능 향상

• 기존 카드 정보 수동 조회 및 업데이트 과정을 SQL 자동화로 변경
• 인덱스를 활용한 최적화로 카드 정보 매칭 속도 개선 (기존 5초 → 1.5초)
• CPU 사용률 감소 및 매출 데이터 정확도 증가

▶ 데이터 자동 업데이트 프로세스 구축

• 결제 내역 테이블을 활용한 카드사 매칭 자동화
• 카드 결제 데이터 정합성 유지 및 매출 데이터 오류 감소
• 오류 발생 시 즉시 대응할 수 있도록 알림 시스템 추가

---

5. 활용 기술 및 도구

기술설명

SQL (MSSQL)	카드사 정보 업데이트 및 데이터 정합성 검증
데이터 최적화	인덱스 활용 및 SQL 성능 개선
배치 프로시저	오류 데이터 감지 및 자동 알림 시스템 구축
매출 카드 결제 분석	카드 결제 데이터 정합성 유지

---

6. 결론 및 향후 발전 방향

▶ 결론

• SQL을 활용하여 영수증 매출 카드 결제 데이터 정합성을 유지하는 프로젝트 수행
• 데이터 정합성을 유지하고 카드 입금 정산 오류를 방지하기 위해 자동화된 SQL 업데이트 및 오류 감지 시스템 구축
• SQL 최적화를 통해 카드 데이터 조회 및 매칭 성능을 개선

▶ 향후 발전 방향

• Python을 활용하여 자동 보고서 생성 및 카드사 정보 검증 추가
• Power BI 또는 Excel 대시보드를 활용한 카드 매출 데이터 시각화