WINDOW FUNCTION 개요
행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수
•
윈도우 함수는 분석 함수나 순위 함수로도 알려져 있다.
윈도우 함수는 기존에 사용하던 집계 함수도 있고, 새로이 윈도우 함수 전용으로 만들어진 기능도 있다.
윈도우 함수는 다른 함수와 달리 중첩해서 사용은 못하지만, 서브쿼리에는 사용할 수 있다
•
구버젼의 mysql의 경우, window 함수를 지원하지 않아 group by를 써야할 수도 있다.
•
Group by 와의 차이점
◦
예시 : 각 부서별로 가장 많이 버는 사람을 찾기
◦
Group by : 그룹으로 보고 싶은 연산만 볼 수 있음
◦
행별로 칼럼을 추가해서 볼 수 있음
Syntax: (OVER 문구가 키워드로 필수 포함)
•
function(expression) OVER ( [ PARTITION BY expression] [ ORDER BY expression ] )
•
함수(칼럼) OVER ( PARTITION BY 칼럼 ORDER BY 칼럼 )
◦
함수 : SUM, AVG, COUNT
•
활용법
◦
SELECT WINDOW_FUNCTION (ARGUMENTS)
OVER ( [ PARTITION BY 컬럼] [ ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;
▪
WINDOW_FUNCTION : 윈도우 함수
▪
ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다.
▪
PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
▪
ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.
▪
WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (sql server 에서는 지원하지 않음)
WINDOW FUNCTION 종류
WINDOW FUNCTION 는 크게 4가지 그룹으로 분류할 수 있다. (벤더별로 지원하는 함수 차이가 있음)
1.
그룹 내 순위(RANK) 관련 함수
a.
RANK, DENSE_RANK, ROW_NUMBER
2.
그룹 내 집계(AGGREGATE) 관련 함수
a.
SUM, MAX, MIN, AVG, COUNT (sql server는 OVER 절의 OREDER BY 지원 X)
3.
그룹 내 행 순서 관련 함수
a.
FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)
4.
그룹 내 비율 관련 함수
a.
CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
5.
Useful functions:
•
ROW_NUMBER, FIRST_VALUE, LAST_VALUE
•
Math functions: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE
그룹 내 집계(AGGREGATE) 관련 함수 (MAX, SUM 등)
•
MAX(칼럼) OVER (PARTITION BY 칼럼)
◦
각 부서별 가장 많이 버는 사람 찾기
•
SUM(칼럼) OVER (ORDER BY 칼럼)
◦
Line 별 누적합 구하기
◦
ID 별 누적합 구하기
•
누적합 구하기
◦
Window 함수 외의 방법
▪
Join 활용 → Inner join 사용
▪
SELECT(셀렉트) 서브 쿼리를 활용
순위 정하기
•
RANK(), DENSE_RANK(), ROW_NUMBER()
◦
() 안에 인자가 들어가지 않는다.
◦
ROW_NUMBER()
ROW_NUMBER 는 동일한 값이라도 고유한 순위를 부여한다. (중복 순위 X)
SELECT JOB, ENAME, VAL,
RANK() OVER (ORDER BY VAL DESC) ALL_RANK,
ROW_NUMBER() OVER (ORDER BY VAL DESC) ROW_NUMBER
FROM EMP ;
Python
복사
ROW_NUMBER 는 동일한 순위를 배제하기 위해 유니크한 순위를 정한다.
같은 값에 대해서 어떤 결과가 먼저 나올지 순서가를 정하고 싶다면 ORDER BY를 같이 기재하라.
◦
RANK(),DENSE_RANK()
▪
중복 순위 존재
▪
RANK, DENSE_RANK 가 동일한 값에 대해서는 동일한 순위를 부여
▪
DENSE_RANK() 는 숫자가 비는 것이 없이 rank가 먹여짐 (1123)
•
RANK → 1134
데이터 위치 바꾸기
•
LAG, LEAD
◦
LAG(칼럼, 칸 수), LEAD(칼럼, 칸 수) → 밀고 싶은 칸 수를 옵션으로 넣을 수 있다.
◦
LAG(칼럼, 칸 수, 디폴트 대체 값), LEAD(칼럼, 칸 수, 디폴트 대체 값)
◦
LAG
▪
한칸 씩 뒤로 미뤄주기
◦
LEAD
▪
뒤에 있는 데이터를 한칸씩 당겨오기
◦
칸 옵션 넣기
연습
SELECT
LABEL, PD_C, PD_NM, PD_BUY_CT,
RANK() OVER (PARTITION BY LABEL ORDER BY PD_BUY_CT DESC) PURCHASE_RANK
FROM lpoint.raw_data.L_POINT_NEW
WHERE PURCHASE_RANK <= 10;
SQL
복사
위의 쿼리는 잘못되었다 그 이유는?
이는 WHERE 절에서 PURCHASE_RANK를 사용하는 것이 RANK() 함수로 계산된 값에 대해 필터링하는 것이 불가능하기 때문입니다. WHERE 절은 RANK 함수로 계산된 값을 직접 참조할 수 없습니다.
RANK 함수로 계산된 PURCHASE_RANK 값을 필터링하려면 다음과 같이 서브쿼리를 사용해야 합니다
FIRST_VALUE, LAST_VALUE
Window 함수: ROWS BETWEEN AND 이해하기
•
Window 함수의 Sequence를 이해해보자
SELECT value FROM rows_test;
SELECT
SUM(value) OVER (
order by value
rows between 2 preceding and 2 following
) AS rolling_sum
FROM rows_test ;
SQL
복사
•
만약 앞에 5개 뒤에 10개를 보고 싶으면
rows between 5 preceding and 10 following
•
앞에는 다 보고 싶고 뒤에만 2개를 보고 싶으면
rows between unbounded preceding and 2 following
•
SUM 자리에 FIRST_VALUE, LAST_VALUE를 쓰면 이 시퀀스 안에서 첫번째, 뒤에서 첫번째를 찾을 수 있게됨
SELECT
*,
FIRST_VALUE(value) OVER (
partition by name
order by value
rows between unbounded preceding and unbounded following
) AS min_value,
LAST_VALUE(value) OVER (
partition by name
order by value
rows between unbounded preceding and unbounded following
) AS max_value
FROM rows_test
SQL
복사
rows between unbounded preceding and unbounded following
같은 파티션 내에 속한 레코드들이라면 시퀀스를 전부 다 지정
•
사용자별로 처음 채널과 마지막 채널 알아내기
•
ROW_NUMBER 사용
WITH RECORD AS (
SELECT /*사용자의 유입에 따른, 채널 순서 매기는 쿼리*/
userid,
channel,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts ASC) AS seq_first,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts DESC) AS seq_last
FROM user_session_channel u
LEFT JOIN session_timestamp t
ON u.sessionid = t.sessionid
)
SELECT /*유저의 첫번째 유입채널, 마지막 유입 채널 구하기*/
f.userid,
f.channel first_channel,
l.channel last_channel
FROM RECORD f
INNER JOIN RECORD l ON f.userid = l.userid
WHERE f.seq_first = 1 and l.seq_last = 1
ORDER BY userid
SQL
복사
◦
WITH 문을 사용한 TEMP RECORD테이블을 생성
◦
RECORD 테이블을 inner join하여 seq_first와 seq_last가 1인 user_id만 테이블에 남긴다.
◦
SELECT를 사용하여 사용자별로 처음 채널과 마지막 채널 알아내기를 수행
+------+-------------+------------+
|userid|first_channel|last_channel|
+------+-------------+------------+
| 27| Youtube| Instagram|
| 29| Naver| Naver|
| 33| Google| Youtube|
| 34| Youtube| Naver|
| 36| Naver| Youtube|
| 40| Youtube| Google|
| 41| Facebook| Youtube|
| 44| Naver| Instagram|
| 45| Youtube| Instagram|
| 59| Instagram| Instagram|
+------+-------------+------------+
only showing top 10 rows
SQL
복사
•
FIRST_VALUE, LAST_VALUE 사용
SELECT DISTINCT A.userid,
FIRST_VALUE(A.channel) over(partition by A.userid order by B.ts
rows between unbounded preceding and unbounded following) AS First_Channel,
LAST_VALUE(A.channel) over(partition by A.userid order by B.ts
rows between unbounded preceding and unbounded following) AS Last_Channel
FROM user_session_channel A
LEFT JOIN session_timestamp B
ON A.sessionid = B.sessionid
SQL
복사
+------+-------------+------------+
|userid|First_Channel|Last_Channel|
+------+-------------+------------+
| 27| Youtube| Instagram|
| 29| Naver| Naver|
| 33| Google| Youtube|
| 34| Youtube| Naver|
| 36| Naver| Youtube|
| 40| Youtube| Google|
| 41| Facebook| Youtube|
| 44| Naver| Instagram|
| 45| Youtube| Instagram|
| 59| Instagram| Instagram|
| 64| Youtube| Youtube|
| 65| Youtube| Organic|
| 68| Youtube| Organic|
| 69| Facebook| Instagram|
| 80| Organic| Naver|
| 84| Google| Youtube|
| 87| Youtube| Google|
| 97| Organic| Organic|
| 112| Facebook| Youtube|
| 113| Organic| Organic|
+------+-------------+------------+
only showing top 20 rows
SQL
복사