[SQL] 카테고리별 값이 가장 큰 항목 추출하기 (RANK)

카테고리별로 값이 가장 큰 항목을 추출하는 문제는 푸는데, 처음 작성한 코드가 비효율적이라고 생각되어 ChatGPT의 피드백을 받았습니다. RANK 함수를 쓰면 더 효율적으로 문제를 풀 수 있더라구요. 이에 RANK 함수에 대한 사용 방법을 정리해보았습니다.

문제: 시도별로 인구가 많은 상위 2개 시군구 추출하기

• 풀이 환경: BigQuery
• 데이터 출처: 행정안전부 주민등록 인구통계
• 테이블: demographics
• 필드: region(지역 정보), total(인구 수)
• 시도: 서울특별시, 경기도...
• 시군구: 종로구, 중구, 고양시...

region	total
서울특별시	9,360,400
서울특별시 종로구	139,093
서울특별시 중구	121,407
서울특별시 용산구	205,603
...	...

 

시도, 시군구 열 전처리

먼저 region 열을 시도, 시군구로 나누겠습니다. 첫 띄어쓰기를 기준으로 문자열을 나누었습니다. WHERE문은 테이블을 조회하고 바로 실행되기 때문에 sido, sigungu가 아닌 region을 사용했습니다.

SELECT
  region,
  REGEXP_EXTRACT(region, r'^(.*)\s+') AS sido,
  REGEXP_EXTRACT(region, r'^\S+\s+(.*)') AS sigungu,
  total
FROM
  study_sql.demographics
WHERE
  CONTAINS_SUBSTR(region, ' ')

region	sido	sigungu	total
서울특별시 종로구	서울특별시	종로구	139,093
서울특별시 중구	서울특별시	중구	121,407
서울특별시 용산구	서울특별시	용산구	205,603
...			...

 

시도 내 지역 인구 순위 부여하기 (RANK)

전처리한 테이블에 시도마다 시군구별로 인구수에 따른 순위를 부여할 차례입니다.

WITH demographics_sido AS (
  SELECT
    region,
    REGEXP_EXTRACT(region, r'^(.*)\s+') AS sido,
    REGEXP_EXTRACT(region, r'^\S+\s+(.*)') AS sigungu,
    total
  FROM
    study_sql.demographics
  WHERE
    CONTAINS_SUBSTR(region, ' ')
)

SELECT
  sido,
  sigungu,
  total,
  RANK() OVER (PARTITION BY sido ORDER BY total DESC) AS rank_total
FROM
  demographics_sido

sido	sigungu	total	rank_total
서울특별시	송파구	652,292	1
서울특별시	강서구	558,637	2
서울특별시	강남구	557,151	3
...	...	...	...

 

해석: RANK() OVER (PARTITION BY sido ORDER BY total DESC)

RANK()

• 기능: 정렬된 파티션 내에서 주어진 값을 기준으로 순위를 반환
• 동일한 값 처리: 동일한 순위 부여, 동일한 순위가 부여되면 다음 순위는 그만큼 건너뛰고 순위 부여
• OVER를 사용해 파티션과 기준이 되는 값 설정

OVER

• RANK 함수의 작동 범위 지정

PARTITION BY sido

• sido(시도)를 기준으로 파티션을 나눠 그룹을 만든 것으로 판단

ORDER BY total DESC

• 파티션 내에서 total(인구수)를 기준으로 내림차순으로 정렬

 

상위 2개 지역 추출하기

이제 마무리만 남았습니다. 만든 테이블에서 rank_total이 2 이상인 행만 가져오면 됩니다. 앞서 언급한 것처럼 WHERE문은 테이블을 조회하고 바로 실행되기 때문에 한번 더 조회합니다.

WITH demographics_sido AS (
  SELECT
    region,
    REGEXP_EXTRACT(region, r'^(.*)\s+') AS sido,
    REGEXP_EXTRACT(region, r'^\S+\s+(.*)') AS sigungu,
    total
  FROM
    study_sql.demographics
  WHERE
    CONTAINS_SUBSTR(region, ' ')
), demographics_sido_rank AS ( 
  SELECT
    sido,
    sigungu,
    total,
    RANK() OVER (PARTITION BY sido ORDER BY total DESC) AS rank_total
  FROM
    demographics_sido
)

SELECT
  sido,
  sigungu,
  total,
  rank_total
FROM
  demographics_sido_rank
WHERE
  rank_total <= 2

sido	sigungu	total	rank_total
서울특별시	송파구	652,292	1
서울특별시	강서구	558,637	2
경기도	수원시	1,196,671	1
...	...	...	...