Rookiss Part5 데이터베이스 : 윈도우함수

🙇‍♀️윈도우 함수

• 윈도우 함수

• 행들의 서브 집합을 대상으로, 각 행별로 계산을 해서 스칼라(단일 고정)값을 출력하는 함수

• 느낌상 GROUPING이랑 비슷한가?
• SUM, COUNT, AVG 집계함수

SELECT *
FROM salaries
ORDER BY salary DESC;

SELECT playerID, MAX(salary)
FROM salaries
GROUP BY playerID
ORDER BY MAX(salary) DESC;

🪐OVER

~OVER([PARTITION]) [ORDER BY] [ROWS])

• 전체 데이터를 연봉 순서로 나열하고, 순위 표기
• 오버내에 들어가 있는 애는 최종적인 데이터나열에 관여 X

  SELECT *,
    ROW_NUMBER() OVER (ORDER BY salary DESC), -- 행 번호
    RANK() OVER (ORDER BY salary DESC), -- 랭킹 (공동1등이 생기면 2등없음)
    DENSE_RANK() OVER (ORDER BY salary DESC), -- 랭킹 (공동1등이 있더라도 2등 존재)
    NTILE(100) OVER (ORDER BY salary DESC) -- 백분율
  FROM salaries
  

• playerID 별 순위를 따로 하고 싶다면

  SELECT *,
   RANK() OVER (PARTITION BY playerID ORDER BY salary DESC)
   FROM salaries
   ORDER BY playerID;
  

🪐자주 사용하는 함수들

LAG, LEAD

• LAG(바로 이전), LEAD(바로 다음)

  SELECT *,
   RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
   LAG(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS preSalary,
   LEAD(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS nextSalary
   FROM salaries
   ORDER BY playerID;
  

FIRST_VALUE, LAST_VALUE

• 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;