SQL 순위함수

사용할 예제

대장균 크기에 따라 분류하기2

https://school.programmers.co.kr/learn/courses/30/lessons/301649

1. ROW_NUMBER()

WITH RANK_COLONY AS (
SELECT ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS 순위, ID, COUNT(*) OVER() AS TOTAL_COUNT
FROM ECOLI_DATA)

SELECT ID, CASE
    WHEN 순위 <= TOTAL_COUNT/4*1 THEN "CRITICAL"
    WHEN 순위 <= TOTAL_COUNT/4*2 THEN "HIGH"
    WHEN 순위 <= TOTAL_COUNT/4*3 THEN "MEDIUM"
    ELSE "LOW"
    END AS COLONY_NAME
FROM RANK_COLONY
ORDER BY ID

#GROUP BY 로 COUNT를 구하는건 CASE안에서 사용이 불가능하여
#WITH절에 COUNT(*) OVER()로 총갯수를 전달

2. RANK()

WITH RANK_COLONY AS (
SELECT RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS 순위, ID, COUNT(*) OVER() AS TOTAL_COUNT
FROM ECOLI_DATA)

SELECT ID, CASE
    WHEN 순위 <= TOTAL_COUNT/4*1 THEN "CRITICAL"
    WHEN 순위 <= TOTAL_COUNT/4*2 THEN "HIGH"
    WHEN 순위 <= TOTAL_COUNT/4*3 THEN "MEDIUM"
    ELSE "LOW"
    END AS COLONY_NAME
FROM RANK_COLONY
ORDER BY ID

3.NTILE()

WITH TILE_COLONY AS(
SELECT NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC) AS GROUP_NUM, ID, COUNT(*) OVER() AS TOTAL_COUNT
FROM ECOLI_DATA)

SELECT ID, CASE
    WHEN GROUP_NUM = 1 THEN "CRITICAL"
    WHEN GROUP_NUM = 2 THEN "HIGH"
    WHEN GROUP_NUM = 3 THEN "MEDIUM"
    WHEN GROUP_NUM = 4 THEN "LOW"
    END AS COLONY_NAME
FROM TILE_COLONY
ORDER BY ID

#문제의 가정이 데이터수는 4의 배수이기때문에 가능.