SELECT ename, dept, COUNT(*) OVER(PARTITION BY dept) FROM emp;
직원 행 유지 + 부서별 직원 수 같이 표시
GROUP BY + 순위함수
SELECT dept, RANK() OVER(ORDER BY sal DESC) FROM emp GROUP BY dept;
오류 발생 (GROUP BY는 행을 줄여버려서 순위 매길 대상이 없음)
👀 WINDOW 함수는 SELECT 절에서 사용하며, 기본 쿼리 작성법은 아래👇와 같다.
-- 윈도우 함수 기본 문법
SELECT
WINDOW_FUNCTION(컬럼명) OVER ( -- OVER는 윈도우 함수가 계산될 범위를 정하는 키워드
PARTITION BY 컬럼명 -- 파티션 기준 그룹화
ORDER BY 컬럼명 -- 파티션 내 정렬 기준
) AS 별칭
FROM
테이블명;
SELECT WINDOW_FUNCTION(컬럼명) OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭
FROM 테이블명;
예시) ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
-> ROW_NUMBER 라는 윈도우 함수를 사용하고, 그 기준을 컬럼1로 , 정렬은 컬럼2로 지정
✔️ 순위부여 함수에는 () 안에 값이 들어가지 않는다.
함수 이름
괄호 안에 컬럼 필요 여부
설명
RANK()
❌ 없음
순위 (중복 순위 있음, 건너뜀)
DENSE_RANK()
❌ 없음
순위 (중복 순위 있음, 건너뜀 없음)
ROW_NUMBER()
❌ 없음
고유 순번 부여
PERCENT_RANK()
❌ 없음
백분율 순위 (0~1 사이)
CUME_DIST()
❌ 없음
누적 백분율 (현재 행 이하 비율)
NTILE(N)
✅ 있음 (N은 숫자)
N등분으로 나눠 등급 부여
LAG(컬럼)
✅ 있음
이전 행의 값
LAG(컬럼, 숫자)
✅ 있음
N번째 이전 행의 값
LEAD(컬럼)
✅ 있음
이후 행의 값
LEAD(컬럼, 숫자)
✅ 있음
N번째 이후 행의 값
FIRST_VALUE(컬럼)
✅ 있음
파티션 내 첫 번째 값
LAST_VALUE(컬럼)
✅ 있음
파티션 내 마지막 값
RATIO_TO_REPORT(컬럼)
✅ 있음
전체합 대비 현재값 비율 (Oracle)
SUM(컬럼)
✅ 있음
누적 합계 (윈도우 함수일 경우)
AVG(컬럼)
✅ 있음
누적 평균 등
‼️‼️‼️ 전체적인 구조는 이러하다
/* 집계 함수(Group Function),
윈도우 함수(Window Function),
GROUP BY, HAVING 등을 모두 사용하는 복합 쿼리 기본 구조 예시 */
SELECT
컬럼명,
그룹함수(컬럼명),
윈도우함수 OVER (
PARTITION BY 컬럼명
ORDER BY 컬럼명
) AS 별칭
FROM
테이블명
WHERE
조건식
GROUP BY
컬럼명
HAVING
조건식 -- GROUP BY에 대한 조건
ORDER BY
컬럼명 DESC,
컬럼명 ASC;
📌 WITH 구문
✅ 특징
구분
상세
정의
SQL 구문에서 사용되는 임시 테이블(가상 테이블)
사용 이유
쿼리의 가독성 향상 및 쿼리 성능 최적화
특징 & 장점
- 임시 테이블처럼 사용되며, 작성한 쿼리 내에서만 유효 - 여러 개의 WITH 문 선언 가능 - 한 테이블을 여러 번 조회해야 하는 경우, 1회만 조회해 성능 향상 (한 번만 저장해놓으면 계속 쓸 수 있음) - 복잡한 JOIN, UNION 등 연산을 효율적으로 처리
✅ 기본 구문
WITH 임시테이블명 AS (
SELECT
컬럼1,
컬럼2,
집계함수(컬럼3) AS 집계값,
윈도우함수() OVER (
PARTITION BY 컬럼1
ORDER BY 컬럼2
) AS 윈도우값
FROM 원본테이블명
WHERE 조건식
GROUP BY 컬럼1, 컬럼2
HAVING 집계조건식
)
SELECT
원하는컬럼1,
원하는컬럼2,
...
FROM 임시테이블명
ORDER BY 정렬기준컬럼 ASC;
✅ 예시
-- 첫 번째 임시 테이블 gogo 정의: 레벨 50 초과 유저
WITH gogo AS (
SELECT
game_account_id,
exp
FROM
basic.users
WHERE
`level` > 50
),
-- 두 번째 임시 테이블 hoho 정의: 카드 결제 정보
hoho AS (
SELECT DISTINCT
game_account_id,
pay_amount,
approved_at
FROM
basic.payment
WHERE
pay_type = 'CARD'
)
-- 결제 여부별 유저 수 집계
SELECT
CASE
WHEN b.game_account_id IS NULL THEN '결제x'
ELSE '결제o'
END AS gb,
COUNT(DISTINCT a.game_account_id) AS accnt
FROM
gogo AS a
LEFT JOIN
hoho AS b
ON
a.game_account_id = b.game_account_id
GROUP BY
CASE
WHEN b.game_account_id IS NULL THEN '결제x'
ELSE '결제o'
END;
오늘은 라이브 세션에서 알아두어야 할 내용들을 정리해 보았다. 작성 기본 구문을 눈에 익혀두자!!
