[MYSQL] WITH, RANK, PARTITION BY

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

프로그래머스

 

WITH TBL AS(
    SELECT FOOD_TYPE,REST_ID,REST_NAME,FAVORITES,
    RANK() OVER(PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RK
    FROM REST_INFO
)
SELECT FOOD_TYPE,REST_ID,REST_NAME,FAVORITES
FROM TBL
WHERE RK = 1
ORDER BY FOOD_TYPE DESC

 

RANK() OVER(ORDER BY attr DESC/ASC) AS attr_name

attr의 값으로 순서를 매겨 attr_name이라는 새로운 열을 생성.

 

RANK() OVER(PARTITION BY group_attr ORDER BY attr DESC/ASC) AS attr_name

이 때, 위처럼 partition by를 사용해 그룹화도 가능