회사 업무화면의 비밀
SQL JOIN이 모든 걸 만든다

-- 사고번호 하나로 화면 전체 데이터를 끌어오는 쿼리
SELECT
    -- 계약 정보
    P.POL_NO,
    P.PROD_CODE,
    P.CONT_DATE,
    P.PREMIUM,
    P.POL_STATUS,
    -- 피보험자 정보
    C.CUST_NM,
    C.BIRTH_DT,
    C.PHONE,
    -- 사고 정보
    A.ACC_DATE,
    A.ACC_TYPE

FROM   ACCIDENT  A
JOIN   POLICY    P  ON A.POL_NO  = P.POL_NO
JOIN   CUSTOMER  C  ON P.CUST_ID = C.CUST_ID

WHERE  A.ACC_NO = 'ACC-2024-003847'

-- 지급 이력 목록 조회
SELECT
    CP.PAY_DT,
    CP.PAY_RSN,
    CP.PAY_AMT,
    E.EMP_NM   AS HANDLER_NM

FROM   CLAIM_PAY  CP
JOIN   EMPLOYEE   E   ON CP.HANDLER_ID = E.EMP_ID

WHERE  CP.ACC_NO = 'ACC-2024-003847'
ORDER BY CP.PAY_DT DESC

-- 2024년 지급금액이 500만원 이상인 사고 현황
SELECT
    A.ACC_NO,
    C.CUST_NM,
    P.PROD_CODE,
    SUM(CP.PAY_AMT)  AS TOTAL_AMT,
    COUNT(CP.PAY_SEQ) AS PAY_CNT

FROM       ACCIDENT   A
JOIN       POLICY     P   ON A.POL_NO   = P.POL_NO
JOIN       CUSTOMER   C   ON P.CUST_ID  = C.CUST_ID
LEFT JOIN CLAIM_PAY  CP  ON A.ACC_NO   = CP.ACC_NO

WHERE  CP.PAY_DT BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
    A.ACC_NO, C.CUST_NM, P.PROD_CODE
HAVING
    SUM(CP.PAY_AMT) >= 5000000
ORDER BY
    TOTAL_AMT DESC

-- 평균 보험료보다 높은 계약자 목록
SELECT C.CUST_NM, P.PREMIUM
FROM   POLICY P
JOIN   CUSTOMER C ON P.CUST_ID = C.CUST_ID
WHERE  P.PREMIUM > (
    SELECT AVG(PREMIUM)
    FROM   POLICY
    WHERE  POL_STATUS = '유효'
)

-- 2024년에 한 번이라도 사고가 난 계약의 계약자 조회
SELECT CUST_NM, PHONE
FROM   CUSTOMER
WHERE  CUST_ID IN (
    SELECT DISTINCT P.CUST_ID
    FROM   POLICY P
    JOIN   ACCIDENT A ON P.POL_NO = A.POL_NO
    WHERE  A.ACC_DATE BETWEEN '2024-01-01' AND '2024-12-31'
)

-- 고객별 총 지급액을 먼저 구한 뒤, 상위 10명만 뽑기
SELECT *
FROM (
    SELECT
        C.CUST_NM,
        SUM(CP.PAY_AMT) AS TOTAL_AMT
    FROM      CUSTOMER  C
    JOIN      POLICY    P  ON C.CUST_ID = P.CUST_ID
    JOIN      ACCIDENT  A  ON P.POL_NO  = A.POL_NO
    JOIN      CLAIM_PAY CP ON A.ACC_NO  = CP.ACC_NO
    GROUP BY C.CUST_NM
) SUMMARY
WHERE  TOTAL_AMT >= 10000000
ORDER BY TOTAL_AMT DESC
FETCH FIRST 10 ROWS ONLY  -- Oracle / LIMIT 10 (MySQL)

WITH CUST_SUMMARY AS (
    SELECT
        C.CUST_ID,
        C.CUST_NM,
        SUM(CP.PAY_AMT) AS TOTAL_AMT,
        COUNT(A.ACC_NO)  AS ACC_CNT
    FROM CUSTOMER C
    JOIN POLICY   P  ON C.CUST_ID = P.CUST_ID
    JOIN ACCIDENT A  ON P.POL_NO  = A.POL_NO
    JOIN CLAIM_PAYCP  ON A.ACC_NO  = CP.ACC_NO
    GROUP BY C.CUST_ID, C.CUST_NM
)
-- CTE를 두 번 참조
SELECT CUST_NM, TOTAL_AMT, ACC_CNT,
       TOTAL_AMT / ACC_CNT AS AVG_PER_ACC
FROM   CUST_SUMMARY
WHERE  ACC_CNT >= 2
ORDER BY TOTAL_AMT DESC

-- 지급금액에 따라 등급 부여
SELECT
    A.ACC_NO,
    SUM(CP.PAY_AMT) AS TOTAL_PAY,
    CASE
        WHEN SUM(CP.PAY_AMT) >= 10000000 THEN '대형사고'
        WHEN SUM(CP.PAY_AMT) >=  3000000 THEN '중형사고'
        WHEN SUM(CP.PAY_AMT) >         0 THEN '소형사고'
        ELSE '지급없음'
    END AS ACC_GRADE
FROM      ACCIDENT  A
LEFT JOIN CLAIM_PAY CP ON A.ACC_NO = CP.ACC_NO
GROUP BY A.ACC_NO

-- 월별 사고건수를 가로로 펼치기 (피벗)
SELECT
    TO_CHAR(ACC_DATE, 'YYYY') AS YEAR,
    SUM(CASE WHEN TO_CHAR(ACC_DATE,'MM')='01' THEN 1 ELSE 0 END) AS JAN,
    SUM(CASE WHEN TO_CHAR(ACC_DATE,'MM')='02' THEN 1 ELSE 0 END) AS FEB,
    SUM(CASE WHEN TO_CHAR(ACC_DATE,'MM')='03' THEN 1 ELSE 0 END) AS MAR,
    -- ... 12월까지 반복
    COUNT(*) AS TOTAL
FROM   ACCIDENT
GROUP BY TO_CHAR(ACC_DATE, 'YYYY')
ORDER BY YEAR

-- 고객별로 사고 순서 번호 붙이기 (가장 최근 사고가 1번)
SELECT
    C.CUST_NM,
    A.ACC_NO,
    A.ACC_DATE,
    ROW_NUMBER() OVER (
        PARTITION BY P.CUST_ID
        ORDER BY     A.ACC_DATE DESC
    ) AS ACC_RANK,
    COUNT(*) OVER (
        PARTITION BY P.CUST_ID
    ) AS TOTAL_ACC_CNT   -- 이 고객의 총 사고 건수
FROM  ACCIDENT A
JOIN  POLICY   P ON A.POL_NO  = P.POL_NO
JOIN  CUSTOMER C ON P.CUST_ID = C.CUST_ID

-- 월별 지급액과 전월 대비 증감
WITH MONTHLY AS (
    SELECT
        TO_CHAR(PAY_DT, 'YYYY-MM') AS MON,
        SUM(PAY_AMT)              AS MON_AMT
    FROM   CLAIM_PAY
    GROUP BY TO_CHAR(PAY_DT, 'YYYY-MM')
)
SELECT
    MON,
    MON_AMT,
    LAG(MON_AMT) OVER (ORDER BY MON) AS PREV_MON_AMT,
    MON_AMT - LAG(MON_AMT) OVER (ORDER BY MON) AS DIFF
FROM   MONTHLY
ORDER BY MON

-- 사고별 지급액의 누적합 (시간 순서대로)
SELECT
    PAY_DT,
    PAY_AMT,
    SUM(PAY_AMT) OVER (
        PARTITION BY ACC_NO
        ORDER BY     PAY_DT
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CUMUL_AMT    -- 이 사고의 누적 지급액
FROM CLAIM_PAY
WHERE ACC_NO = 'ACC-2024-003847'

-- 접근 가능한 테이블 전체 목록
SELECT TABLE_NAME, OWNER, NUM_ROWS
FROM   ALL_TABLES
WHERE  TABLE_NAME LIKE '%CLAIM%'  -- 이름에 CLAIM 포함
ORDER BY TABLE_NAME

-- 특정 테이블의 컬럼 구조 조회
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    NULLABLE,
    COMMENTS   -- 컬럼 설명이 있으면 여기 나옴
FROM   ALL_TAB_COLUMNS  C
LEFT JOIN ALL_COL_COMMENTS CM
    ON C.TABLE_NAME  = CM.TABLE_NAME
    AND C.COLUMN_NAME = CM.COLUMN_NAME
WHERE  C.TABLE_NAME = 'CLAIM_PAY'
ORDER BY COLUMN_ID

-- 테이블 목록
SHOW TABLES LIKE '%claim%';

-- 컬럼 구조
DESCRIBE claim_pay;

-- 또는 더 자세하게
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'claim_pay'
ORDER BY ORDINAL_POSITION

-- Oracle: 특정 테이블에 걸린 FK 찾기
SELECT
    A.TABLE_NAME     AS "FK가 있는 테이블",
    A.COLUMN_NAME    AS "FK 컬럼",
    C.TABLE_NAME     AS "참조하는 테이블",
    C.COLUMN_NAME    AS "참조 컬럼(PK)"
FROM   ALL_CONS_COLUMNS   A
JOIN   ALL_CONSTRAINTS    B  ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
JOIN   ALL_CONS_COLUMNS   C  ON B.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE  B.CONSTRAINT_TYPE = 'R'   -- R = Foreign Key
  AND  A.TABLE_NAME LIKE '%CLAIM%'

-- Oracle: 실행계획 확인 (쿼리 앞에 붙임)
EXPLAIN PLAN FOR
SELECT * FROM CLAIM_PAY WHERE ACC_NO = 'ACC-2024-003847';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- MySQL: 실행계획
EXPLAIN
SELECT * FROM claim_pay WHERE acc_no = 'ACC-2024-003847';