(Database : Basic-20) 윈도우 함수

Posted by : Database at Aug 21, 2021

Category : Database

-- 여기서 말하는 윈도우는 OS가 아님
-- 행들의 서브 집합을 대상으로,
-- 각 행별로 계산을 해서 스칼라(단일 고정)값을 출력하는 함수
-- GROUPING(SUM, COUNT ...)과 유사

SELECT playerID
FROM salaries
GROUP BY playerID
ORDER BY MAX(salary) DESC
-- 여기서 문제가 SELECT로 playerID는 볼 수 있지만 salary와 같은 다른 정보는 볼 수 없음
-- 윈도우 함수를 써보자

-- OVER([PARTITION] [ORDER BY] [ROWS])
-- 전체 데이터를 연봉 순서로 나열후 순위 표기
SELECT *,
    ROW_NUMBER() OVER (ORDER BY salary DESC),   -- 행 번호
    RANK() OVER (ORDER BY salary DESC),         -- 랭킹 (공동 1위 후 3위로 처리)
    DENSE_RANK() OVER (ORDER BY salary DESC),   -- 랭킹 (공동 1위 후 2위 처리)
    NTILE(100) OVER (ORDER BY salary DESC)      -- 백분률로 표기
FROM salaries

-- playerID 별 순위를 따로 정리
SELECT *,
    ROW_NUMBER() OVER (PARTITION BY playerID ORDER BY salary DESC)
FROM salaries
ORDER BY playerID

-- LAG(바로 이전), LEAD(바로 다음)
SELECT *,
    RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
    LAG(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS prevSalary,
    LEAD(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS nextSalary,
FROM salaries
ORDER BY playerID

-- FIRST_VALUE, LAST_VALUE
-- FRAME(범위) : First ~ current
SELECT *,
    RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
    FIRST_VALUE(salary) OVER (PARTITION BY playerID 
                                ORDER BY salary DESC 
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS best,
    LAST_VALUE(salary) OVER (PARTITION BY playerID 
                                ORDER BY salary DESC
                                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS worst,
FROM salaries
ORDER BY playerID