SQL Study

오늘 학습할 내용은 병합(CONCAT) 및 윈도우 함수

SELECT *
FROM TABLE
WHERE Salary > (
	SELECT AVG(Salary)
    FROM TABLE
    WHERE CONDITION
    GROUP BY Id
)

SELECT *
FROM TABLE
WHERE COLUMN IN (
	SELECT A_COLUMN
    FROM TABLE
)

UNION, UNION ALL, INTERSECT, EXCEPT

• 컬럼 개수가 동일해야 하며 컬럼 순서도 동일해야 함.
• UNION의 경우 컬럼 개수가 동일해야 하며 컬럼 순서도 동일해야 함. (중복 제거)
• UNION ALL의 경우 두 쿼리 결과를 합치되 중복이 포함됌.
• INTERSECT : 두 쿼리 결과에 공통으로 존재하는 행만 출력
• EXCEPT : 첫 쿼리 결과에서 두 번째 쿼리 결과를 뺌. (Oracle에선 MINUS)

Oracle에서의 날짜 형태

• DATE : 날짜 + 시간 포함, 초 단위까지 저장
• TIMESTAMP : DATE보다 더 정밀한 시간 저장
• TIMESTAMP WITH TIME ZONE : 타임존 포함

MySQL에서의 날짜 형태

• DATE : 날짜만 저장
• DATETIME : 날짜 + 시간
• TIMESTAMP : 날짜 + 시간, 타임존 변환 영향 가능
• TIME : 시간
• YEAR : 연도

-- Oracle 월별 집계
SELECT 
	TRUNC(Fdate, 'MM') AS month,
    - IW는 ISO Week 기준, 월요일 시작 주차
    TRUNC(Fdate, 'IW') AS week,
    COUNT(*) AS counting
FROM TABLE
GROUP BY TRUNC(Fdate, 'MM')
ORDER BY month;

--MySQL
SELECT 
	DATE_FORMAT(Fdate, '%Y-%m-01') AS month,
    YEARWEEK(Fdate, 3) AS week,
    COUNT(*) AS counting
FROM TABLE
GROUP BY DATE_FORMAT(Fdate, '%Y-%m-01')
ORDER BY month;

-- 누적집계 Oracle, MySQL 공용
WITH monthly AS (
	SELECT
    	DATE_FORMAT(Fdate, '%Y-%m-01') AS month,
        COUNT(*) AS counting
    FROM TABLE
    GROUP BY DATE_FORMAT(Fdate, '%Y-%m-01')
)
SELECT 
	month,
    counting,
    SUM(count) OVER (
    	ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_counting
FROM monthly
ORDER BY month;

Window 함수

일반 집계 함수는 GROUP BY를 쓰면 행(row)이 줄어든다.
반면, 윈도우 함수는 행을 유지하며, 각 행 주변의 그룹 기준 계산값을 붙인다.

기본 문법은 다음과 같다.

함수명() OVER (
	PARTITION BY 그룹기준
    ORDER BY 정렬기준
)

-- ROW_NUMBER() 단순 순서 부여
SELECT
    role,
    name,
    years_employed,
    ROW_NUMBER() OVER (
        PARTITION BY role
        ORDER BY years_employed DESC
    ) AS rn
FROM employees;

-- 근속연수가 가장 오래된 직원
WITH ranked AS (
    SELECT
        role,
        name,
        years_employed,
        ROW_NUMBER() OVER (
            PARTITION BY role
            ORDER BY years_employed DESC
        ) AS rn
    FROM employees
)
SELECT *
FROM ranked
WHERE rn = 1;

-- RANK와 DENSE_RANK
-- RANK는 1,2,2,4 순서 // DENSE_RANK는 1,2,2,3 순서
WITH ranked AS (
    SELECT
        role,
        name,
        years_employed,
        RANK() OVER (
            PARTITION BY role
            ORDER BY years_employed DESC
        ) AS rank_no,
        DENSE_RANK() OVER (
        	PARTITION BY role
            ORDER BY years_employed DESC
        ) AS dense_rank_no
    FROM employees
)
SELECT *
FROM ranked
WHERE rn = 1;


-- LAG는 이전 행 값을 가져옴.
SELECT
    TSIZE,
    PMST_PDGP,
    TEST_CPLT_DTTM,
    DEV_LOAD_MIN,
    DEV_LOAD_MIN
      - LAG(DEV_LOAD_MIN) OVER (
            PARTITION BY TSIZE, PMST_PDGP
            ORDER BY TEST_CPLT_DTTM
        ) AS diff_from_prev
FROM tire_test_result;


-- LEAD는 다음 행을 가져옴.
SELECT
    title,
    year,
    LEAD(title) OVER (
        ORDER BY year
    ) AS next_movie
FROM movies;

-- SUM은 누적합
SELECT
    TEST_CPLT_DTTM,
    PMST_PDGP,
    COUNT(*) AS daily_count,
    SUM(COUNT(*)) OVER (
        PARTITION BY PMST_PDGP
        ORDER BY TEST_CPLT_DTTM
    ) AS cumulative_count
FROM tire_test_result
GROUP BY
    TEST_CPLT_DTTM,
    PMST_PDGP;

-- AVG() OVER은 이동 평균, 그룹 평균
SELECT
    TSIZE,
    PMST_PDGP,
    TEST_CPLT_DTTM,
    DEV_LOAD_MIN,
    AVG(DEV_LOAD_MIN) OVER (
        PARTITION BY TSIZE, PMST_PDGP
        ORDER BY TEST_CPLT_DTTM
        -- 앞 2개 행 + 현재 행 -> 총 3개 행
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM tire_test_result;

단, Oracle에선 Window Function 결과를 WHERE 절에서 사용할 수 없으므로, 인라인 뷰 (FROM 절 서브쿼리) 전략을 많이 취함.