[SQL/ORACLE] PARTITION BY 함수

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

프로그래머스

SELECT MONTH, CAR_ID, RECORDS
FROM 
(SELECT DISTINCT EXTRACT(MONTH FROM START_DATE) AS MONTH,
                 CAR_ID,
                 COUNT(*) OVER(PARTITION BY TO_CHAR(START_DATE, 'MM'), CAR_ID) AS RECORDS,
                 COUNT(*) OVER(PARTITION BY CAR_ID) AS CNT
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(START_DATE, 'YYYY-MM-DD') BETWEEN '2022-08-01' AND '2022-10-31'
)
WHERE CNT >= 5 
ORDER BY MONTH ASC, CAR_ID DESC

오라클에서 분석 함수를 사용할 때, PARTITION BY 쿼리를 활용하여 그룹으로 묶는 연산이 가능하다

분석함수([칼럼]) OVER(PARTITION BY 칼럼1, 칼럼2... [ORDER BY 절] [WINDOWING 절])

https://learnsql.com/blog/partition-by-with-over-sql/

 

https://gent.tistory.com/442

[Oracle] 오라클 PARTITION BY 사용법 정리 (분석함수)

각 카테고리 별로 최댓값을 구하기 위해서 ROW_NUMBER()을 활용하여 추출한다.

WITH TEMP01 AS (
    SELECT CATEGORY, PRICE, PRODUCT_NAME, ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS RNUM
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
)
SELECT CATEGORY, PRICE, PRODUCT_NAME 
FROM TEMP01
WHERE RNUM = 1
ORDER BY 2 DESC;