쿼리 공부(1)

WITH ScoreRanges AS (
    SELECT
        COALESCE(cd, 'Total') AS cd,
        COUNT(*) AS count
    FROM
        your_table_name
    WHERE
        cd IN ('01', '02', '03')
    GROUP BY
        ROLLUP(cd)
)

SELECT 
    COALESCE(SBMS_YN, 'TOTAL') AS TYPE,
    NVL(SUM(CASE WHEN DICU_SCR BETWEEN 0 AND 20 THEN 1 END), 0) AS SCORE1,
    NVL(SUM(CASE WHEN DICU_SCR BETWEEN 21 AND 40 THEN 1 END), 0) AS SCORE2,
    NVL(SUM(CASE WHEN DICU_SCR BETWEEN 41 AND 60 THEN 1 END), 0) AS SCORE3,
    NVL(SUM(CASE WHEN DICU_SCR BETWEEN 61 AND 80 THEN 1 END), 0) AS SCORE4,
    NVL(SUM(CASE WHEN DICU_SCR BETWEEN 81 AND 100 THEN 1 END), 0) AS SCORE5
FROM 
    your_table_name
WHERE 
    CL_CD = '03'
GROUP BY 
    ROLLUP(SBMS_YN)
UNION ALL
SELECT
    cd,
    count,
    NULL AS TYPE,
    0 AS SCORE1,
    0 AS SCORE2,
    0 AS SCORE3,
    0 AS SCORE4,
    0 AS SCORE5
FROM ScoreRanges
ORDER BY TYPE, cd;

 

 

CTE는 Common Table Expressions의 약자로, 일시적으로 정의된 이름이 있는 결과 집합을 나타냅니다. WITH 절을 사용하여 쿼리 내에서 임시 테이블처럼 사용할 수 있습니다. 주로 복잡한 쿼리를 단순화하고 가독성을 높이기 위해 사용됩니다.

위에서 사용한 ScoreRanges는 CTE입니다. 이 CTE는 점수 범위에 따른 건수를 계산하는 일시적인 결과 집합을 생성하는데 사용되었습니다. CTE를 사용하면 복잡한 쿼리를 단일한 부분으로 분리할 수 있어 가독성이 향상되고 쿼리를 이해하기 쉬워집니다.