SQL Window함수 - 그룹 내 행 순서 관련 함수, 순위함수

학습 주제  🗒️

SQL Window 함수

 

Window 함수의 종류

구분	함수	반환 유형 (Return Type)
순위 함수	RANK, DENSE_RANK, ROW_NUMBER	bigint
그룹 내 행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD	same type as value
그룹 내 비율 함수	RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE	percent_rank, cume_dist : double precision ntile : integer
일반 집계 함수	SUM, MAX, MIN, AVG, COUNT

• 순위함수
  • ROW_NUMBER
• 그룹 내 행 순서 관련 함수
  
  • FIRST_VALUE, LAST_VALUE

---

주요 메모 사항 📔

(1) 순위함수 : ROW_NUMBER

SELECT column1, column2,
       ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_num
FROM table_name;

• ROW_NUMBER의 역할은 컬럼 하나를 추가하는 역할을 한다.
• 순위함수인 RANK(), DENSE_RANK() 도 비슷한 역할을한다.
  • RANK() : 특정 컬럼의 순위를 구하는 함수이다. 동일한 값에 대해서는 같은 순위를 부여한다. (1, 1, 3, 4, 4, 6, ...)
  • DENSE_RANK() : 동일한 값에 대해서는 같은 순위를 부여하지만 중간 순위를 비우지 않는다. (1, 1, 2, 3, 3, 4, ...)
  • 반면, ROW_NUMBER은 고유한 순위를 부여한다. (1, 2, 3, 4, 5, 6 ...)
• Window 함수 포맷인 ROW_NUMBER() OVER (PARTITION BY ~ ORDER BY ~) FROM [TABLE NAME] 으로 작성한다.

 

예시1) employee 테이블에 대해 부서별로 사원을 정렬하고, 사원에 번호를 할당한다면?

• row_num 이라는 컬럼이 하나 생겨서, 컬럼내부에 부서간 employee_id를 기준으로 정렬한 순서가 나온다.

SELECT employee_id, employee_name, department_id,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS row_num
FROM employees;

결과값 테이블

employee_id	employee_name	department_id	row_num
1	John	101	1
3	Bob	101	2
2	Alice	102	1
5	Carol	102	2
4	Eve	103	1

 

예시2) 임시테이블 cte를 만들어서 ROW_NUMBER() 윈도우 함수를 사용한다. 그룹 내 순번을 할당해 순서를 지정하는데 사용한다.

• 임시테이블 하나는 오름차순, 다른 하나는 내림차순으로 정리해서 JOIN하여 가장 큰 값과, 가장 작은값을 가지고 온다.

WITH cte AS (
 SELECT userid, channel, (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY st.ts asc))
AS arn, (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY st.ts desc)) AS drn
 FROM raw_data.user_session_channel usc
 JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)
SELECT cte1.userid, cte1.channel AS first_touch, cte2.channel AS last_touch
FROM cte cte1
JOIN cte cte2 ON cte1.userid = cte2.userid and cte2.drn = 1
WHERE cte1.arn = 1
ORDER BY 1;

 

 

(2) 그룹 내 행 순서 관련 함수 : FIRST_VALUE,  LAST_VALUE

• FIRST_VALUE, LAST_VALUE 는 SQL윈도우 함수 중 하나이고, 특정 열의 첫 번째 그리고 마지막 값을 반환하는데 사용된다.

위의 예시2) 를 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 raw_data.user_session_channel A
LEFT JOIN raw_data.session_timestamp B
ON A.sessionid = B.sessionid;

• rows between unbounded preceding and unbounded following 라는 문법이 따라주어야 first, last _value 를 가지고 올 수 있다. 이것은 윈도우 범위를 정해주는 방법이다.
• unbounded의 글자만 채워주면 된다. 현재  레코드를 기준으로 앞애 몇개, 뒤에 몇개 볼건지에 대한것을 의미한다.

SELECT
 SUM(value)
 OVER (order by value rows between 2 preceding and 2 following) AS rolling_sum
FROM raw_data.rows_test ;

• order by value rows between 2 preceding and 2 following 라는 식으로 정해주면 아래의 그림과 같이 값들을 sliding 하면서 범위를 지정하여 볼 수 있다.
• 결론적으로, unbounded 를 명시하면, 모든 범위를 보려는 것을 의미한다.

슬라이딩 윈도우

 

 

https://postgresql.kr/docs/9.5/functions-window.html

Window Functions

 

---

추후 알아볼 사항 🔎 

그룹 내 비율 함수