[SQL] Window 함수, RANK, With절

01. WINDOW FUNCTION

스파르타 코딩 클럽

정의
윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수
역할
윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있습니다.
특징
윈도우 함수는 GROUP BY 구문과 병행하여 사용할 수 없습니다.
윈도우 함수로 인해 결과 건수가 줄어들지는 않습니다.
윈도우 함수와 group by 구문은 둘 다 파티션을 분할한다는 의미에서 유사합니다.
다른 함수와 달리 중첩해서 사용은 못하지만, 서브쿼리에는 사용할 수 있습니다.

대표사진 삭제

사진 설명을 입력하세요.

문법 SELECT 절에서 사용되며, 예시는 아래와 같습니다. ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2) = ROW_NUMBER 라는 윈도우 함수를 사용하고, 그 기준을 컬럼1로 , 정렬은 컬럼2로 지정 이렇듯, 다양항 윈도우 함수 사용을 통해 보다 용이한 데이터 핸들링을 할 수 있습니다!

대표사진 삭제

사진 설명을 입력하세요.

대표사진 삭제

사진 설명을 입력하세요.

윈도우 함수를 사용하면, 결과가 아래와 같이 나올 수 있어요 🤩

row_number() over(partition by USER_ID order by REVIEW_DATE desc )as rown
해석: row_number 이라는 윈도우 함수를 사용하여, 유저 ID 를 기준으로 나누고, 날짜를 오름차순으로 하여 rown 이라는 컬럼으로 명명

대표사진 삭제

사진 설명을 입력하세요.

순위 - RANK

스파르타 코딩 클럽

정의: ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수입니다. PARTITION 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있습니다. 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위를 비운 값이 출력됩니다.
문법: RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

대표사진 삭제

사진 설명을 입력하세요.

DENSE_RANK
정의:RANK와 작동법은 동일하나, 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않습니다.
문법: DENSE_RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

대표사진 삭제

사진 설명을 입력하세요.

🔥중요 - ROW_NUMBER (가장 많이 사용)
정의: RANK, DENSE_RANK는 동일한 값에 대해 동일 순위를 부여하지만 ROW_NUMBER은 동일한 값이어도 고유한 순위를 부여해줍니다.
문법: ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
예제

대표사진 삭제

사진 설명을 입력하세요.

대표사진 삭제

사진 설명을 입력하세요.

🚩 순서
FIRST_VALUE
정의: 파티션별 가장 먼저 나온 값을 구하여 출력합니다. 공동 등수를 인정하지 않고 처음 나온 행만 가져오며 MIN함수를 쓰는 것과 결과가 동일합니다.
문법: FIRST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

LAST_VALUE
정의: 파티션별 가장 마지막에 나온 값을 구하여 출력합니다. 공동 등수를 인정하지 않고 나중에 나온 행만 가져오며 MAX함수를 쓰는 것과 결과가 동일합니다.
문법: LAST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

중요 - LAG
정의: 이전 N 번째의 행을 가져오는 함수입니다. 별도 명시가 없는 경우, 기본값은 1 입니다. 가져올 행이 없을 경우 DEFAULT값을 지정해주는 것으로 NVL이나 ISNULL함수의 기능과 동일합니다.
문법: LAG(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) OR LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) # 숫자는 최대 3으로 3번째 전 행을 의미

대표사진 삭제

사진 설명을 입력하세요.

🔥중요 - LEAD
정의: 이후 N행의 값을 가져오는 함수입니다. 별도 명시가 없는 경우, 기본값은 1 입니다.
문법: LEAD(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) OR LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) # 숫자는 최대 3으로 3번째 후 행을 의미