SQL_WINDOW FUNCTION(윈도우 함수)_집계(SUM/MIN/MAX..), 비집계(LAG, LEAD..)_MySQL

안녕하세요!

 

SQL의 윈도우 함수에 대한 내용입니다.

 

SELECT 함수(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)

윈도우 함수의 기본 형태
ORDER BY 에서 default 는 오름차순, 내림차순 정렬 시 DESC

 

윈도우 함수는 그룹으로 묶고 순서대로 정렬한 값을 모든 행에 나타냅니다.

OVER 절의

PARTITION BY -> 지정된 컬럼에서 동일한 값의 행을 그룹으로 묶음.

ORDER BY -> 지정한 컬럼에서 각각의 값을 순서대로 정렬 .

 

윈도우 함수는 OVER 절과 함께 사용되는 것으로,

집계(SUM, COUNT, MAX, MIN 등)과 비집계(RANK, DENSE_RANK 등)에 쓰입니다.

DISTINCT는 함께 쓰이지 않습니다.

자세한 내용은 MySQL_Window Function Documentation을 참고해 주세요.

더불어, SQL 작동 논리 순서는 여기를 참고해 주세요.

 

---

- 테이블 예시

 

---

1. 집계 함수

 

SUM, MIN, MAX, AVG, COUNT 등의 집계 함수를 윈도우 함수로 사용할 수 있습니다.

SELECT SUM(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
SELECT MIN(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
SELECT MAX(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
SELECT AVG(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
SELECT COUNT(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)

 

- 예시

SELECT up_date
     , name
     , salary
     , COUNT(salary) OVER(PARTITION BY up_date ORDER BY up_date) window_cnt
     , SUM(salary) OVER(PARTITION BY up_date ORDER BY salary) window_sum
     , MIN(salary) OVER(PARTITION BY up_date ORDER BY up_date) window_min
     , MAX(salary) OVER(PARTITION BY up_date ORDER BY up_date) window_max
     , AVG(salary) OVER(PARTITION BY up_date ORDER BY salary) window_avg
FROM user1 u
  INNER JOIN income i ON u.number = i.number
  INNER JOIN up_date d ON u.number = d.number ;

PARTITION BY 를 사용하여 up_date 별로 구분하고,

ORDER BY 에는 각각의 컬럼을 사용하여 차이를 확인해보았습니다.

 

1) up_date를 넣었을 경우 해당 일자의 전체를 계산한 값을 나타낸다.

-> PARTITION BY 기준으로 계산한 집계함수의 결과 값

 

2) salary를 넣었을 경우는 각 salary의 값을 누적하여 계산한다.

-> PARTITION BY 기준으로 계산한 ORDER BY 컬럼의 누적 집계 결과 값

 

* 누적을 확인하고 싶다면 ORDER BY 에 누적하여 계산하고 싶은 컬럼을 넣는다.

 

---

2. 비집계 함수

 

순위 함수를 설명한 이전 글과 이어집니다.

2022.04.28 - [배우기/SQL] - SQL_윈도우 함수_순위, 순서(RANK, DENSE_RANK, ROW_NUMBER)_MySQL

RANK, DENSE_RANK, ROW_NUMBER, LEAD, LAG 등의 윈도우 함수입니다.

 

이번에는 LEAD, LAG 입니다.

SELECT LAG(컬럼, 이동할 행의 크기, 빈 행에 넣을 값) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
SELECT LEAD(컬럼, 이동할 행의 크기, 빈 행에 넣을 값) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)

 

- 예시

SELECT u.number
     , name
     , LAG(u.number, 1) OVER() lag_1
     , LEAD(u.number, 1) OVER() lead_1
FROM user1 u
  INNER JOIN up_date d ON u.number = d.number

number 컬럼에서 1만큼 행을 이동하도록 LAG, LEAD 함수를 사용하였습니다.

LAG 함수는 뒤로 밀고, LEAD 함수는 앞으로 당겨옵니다.

 

SELECT u.number
     , name
     , LAG(u.number, 3, 13) OVER() lag_3
     , LEAD(u.number, 2, '가') OVER() lead_2
FROM user1 u
  INNER JOIN up_date d ON u.number = d.number

lag_3 컬럼을 보면 3행 씩 뒤로 밀린 후 빈 자리에 13이 채워진 것을 확인할 수 있습니다.

lead_2 컬럼은 2행 씩 앞으로 당긴 후 빈 자리에 '가' 라는 텍스트가 채워진 것을 확인할 수 있습니다.

---

 

 

순서(RANK, DENSE_RANK, ROW_NUMBER)를 계산하는 윈도우 함수는 아래의 링크에서 확인할 수 있습니다.

2022.04.28 - [배우기/SQL] - SQL_WINDOW FUNCTION(윈도우 함수)_순위/순서(RANK, DENSE_RANK, ROW_NUMBER)_MySQL