개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › SQL

윈도우 함수

행을 줄이지 않고 순위와 누계를 붙이려면

GROUP BY는 여러 행을 한 행으로 합쳐서 합계를 냅니다. 윈도우 함수는 행을 그대로 둔 채, 행마다 순위나 누계 같은 값을 하나씩 옆에 붙입니다.

실행으로 대조 · MySQL 8.4에서 스물네 가지 조합의 SQL을 실행해 행 수와 행마다 붙은 값을 대조

이 실습에 나오는 개념

윈도우 함수
행을 합치지 않고, 각 행마다 "그 행과 관련된 여러 행"을 보고 계산한 값을 하나씩 붙여 주는 함수다. 함수 뒤에 OVER (...)를 적어 어떤 행들을 어떤 순서로 놓고 계산할지 정한다. GROUP BY가 묶음 하나를 결과 한 행으로 줄이는 것과 달리 결과의 행 수가 그대로이므로, 각 행의 원래 값과 순위나 누계를 한 줄에서 함께 볼 수 있다. MySQL에서는 8.0부터 쓸 수 있다. SELECT 절과 ORDER BY 절에만 쓸 수 있어, 윈도우 함수의 결과로 걸러내려면 서브쿼리로 한 번 감싸야 한다.
ROW_NUMBER, RANK, DENSE_RANK
OVER 안의 ORDER BY 순서대로 행에 번호나 순위를 붙이는 윈도우 함수들이다. 세 함수의 차이는 정렬 값이 같은 행(동점)을 다루는 방법이다. ROW_NUMBER는 동점이어도 서로 다른 번호를 주고, RANK는 같은 순위를 준 뒤 그만큼 다음 순위를 건너뛰며(1, 1, 3), DENSE_RANK는 같은 순위를 주고 건너뛰지 않는다(1, 1, 2). ROW_NUMBER에서 동점인 행 가운데 누가 앞 번호를 받는지는 정해져 있지 않으므로, 결과가 항상 같아야 하면 정렬 기준에 겹치지 않는 컬럼을 더한다.
PARTITION BY
윈도우 함수의 OVER 안에 적어, 계산할 범위를 지정한 컬럼의 값이 같은 행끼리 나누는 구문이다. 나뉜 묶음 하나를 파티션이라고 하며, 번호와 순위와 누계는 파티션마다 처음부터 다시 계산된다. GROUP BY와 비슷해 보이지만 행을 합치지 않는다는 점이 다르다. PARTITION BY를 적지 않으면 결과 전체가 하나의 파티션이 된다.
LAG와 LEAD
정렬된 줄에서 지금 행의 앞이나 뒤에 있는 행의 값을 가져오는 윈도우 함수다. LAG는 앞 행의 값을, LEAD는 뒤 행의 값을 가져온다. 어제와 오늘의 차이처럼 이웃한 행끼리 비교하는 계산을, 같은 테이블을 한 번 더 조인하지 않고 쓸 수 있게 해 준다. 가져올 행이 없는 파티션의 첫 행(LAG)이나 마지막 행(LEAD)에서는 NULL이 된다.
GROUP BY, WHERE, HAVING
GROUP BY는 지정한 컬럼의 값이 같은 행들을 묶어 그룹별 집계(합계, 개수 등)를 구한다. WHERE는 그룹으로 묶기 전에 개별 행을 걸러내고, HAVING은 묶은 뒤에 그룹을 걸러낸다. 따라서 집계 결과에 대한 조건은 HAVING에, 그 외의 조건은 WHERE에 쓴다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. FROM score: score 테이블에서 6행을 읽습니다. 한 행이 한 사람의 점수입니다. 90점이 두 명(가은, 나래), 70점이 두 명(다솜, 마루) 있습니다.
  2. OVER (ORDER BY pts DESC): 값을 계산할 때 행들을 어떻게 놓고 볼지 정합니다. 전체 6행을 점수가 높은 순으로 줄을 세웁니다. 줄만 세울 뿐 행을 합치지 않습니다.
  3. 가은(90점): RANK()는 자기보다 점수가 높은 행의 수에 1을 더합니다. 자기보다 높은 행이 없으므로 1입니다.
  4. 나래(90점): 자기보다 점수가 높은 행이 없으므로 1입니다. 앞 행과 동점이라 순위가 같습니다.
  5. 라온(80점): 자기보다 점수가 높은 행이 2개이므로 3입니다. 앞의 동점 두 행이 자리를 둘 차지했으므로 2위는 건너뜁니다.
  6. 다솜(70점): 자기보다 점수가 높은 행이 3개이므로 4입니다.
  7. 마루(70점): 자기보다 점수가 높은 행이 3개이므로 4입니다. 앞 행과 동점이라 순위가 같습니다.
  8. 바다(60점): 자기보다 점수가 높은 행이 5개이므로 6입니다. 앞의 동점 두 행이 자리를 둘 차지했으므로 5위는 건너뜁니다.
  9. ORDER BY pts DESC: 결과를 내보냅니다. 6행이 그대로 6행이고, 행마다 rnk 컬럼의 값이 하나씩 붙었습니다.

실행 결과

코드

-- score 테이블 (6행)
-- name  team  pts
-- 가은   A     90
-- 나래   A     90
-- 다솜   A     70
-- 라온   B     80
-- 마루   B     70
-- 바다   B     60

SELECT name, team, pts,
       RANK() OVER (ORDER BY pts DESC) AS rnk
  FROM score
 ORDER BY pts DESC;

이 모형의 전제

스물네 가지 조합 모두, 화면의 SQL과 같은 문장을 이 PC의 MySQL 8.4에서 실행해 결과 행 수와 행마다 붙은 값을 대조했습니다. 누계에 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 적었을 때의 값도 같은 방법으로 확인했습니다. 동점인 행 가운데 어느 행이 먼저 놓이는지는 SQL이 정하지 않으므로(MySQL 문서의 ROW_NUMBER 설명), 화면에서 동점인 두 사람의 순서는 한 가지 예이며 대조는 이름이 아니라 팀, 점수, 붙은 값으로 했습니다. 윈도우 함수는 MySQL 8.0부터 쓸 수 있습니다. 단계 문장의 계산 방법(자기보다 높은 행의 수에 1을 더함 등)은 함수의 정의를 풀어 쓴 것이고, 실제 DB는 정렬한 뒤 한 번 훑으며 값을 구합니다.

앞 실습: 서브쿼리 · 다음 실습: OFFSET과 키셋 페이징

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요