[database] SQL window functions

 

SELECT 
  i.name, 
  i.dept_name, 
  i.salary,

  ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY dept_name ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS dense_rank

FROM instructor i
ORDER BY dept_name, salary DESC;


--- PARTITION BY dept_name: 학과별로 그룹을 나눈다


| 컬럼                           | 의미                                           |
| ----------------------------- | --------------------------------                |
| `name`, `dept_name`, `salary` | 교수 정보                                        |
| `row_num`                     | 학과별로 급여 기준 1등, 2등... (중복 허용 안 함)  |
| `rank`                        | 공동 순위 허용, 다음 순위 건너뜀                 |
| `dense_rank`                  | 공동 순위 허용, 다음 순위는 연속적               |

 

 

SELECT 
  c.dept_name,           -- 과목이 속한 학과 이름
  c.title,               -- 과목명
  c.credits,             -- 과목의 학점
  COUNT(*) OVER (PARTITION BY dept_name) AS course_count,  -- 학과별 전체 과목 수
  RANK() OVER (PARTITION BY dept_name ORDER BY credits DESC) AS credit_rank  -- 학과 내 학점 기준 순위
FROM course c
WHERE dept_name IS NOT NULL
ORDER BY dept_name, credits;

 

 

 

COUNT(*) OVER (PARTITION BY dept_name)  vs GROUP BY dept_name

 

 윈도우 함수 버전

SELECT dept_name, title, COUNT(*) OVER (PARTITION BY dept_name) AS course_count FROM course;

dept_name                                     title                                                                                                           course_count

 

CS	AI	3
CS	Database	3
CS	Programming	3
Math	Calculus	2
Math	Algebra	2

• 각 행은 그대로 존재하고, 학과별 COUNT만 추가됨

 

GROUP BY 버전

SELECT dept_name, COUNT(*) AS course_count FROM course GROUP BY dept_name;

dept_name                                                                                                                 course_count

 

CS	3
Math	2

• 행이 줄어듦 (학과별로 요약됨)

 

SELECT 
  dept_name,                     -- 학과명
  budget,                        -- 해당 학과의 예산
  SUM(budget) OVER (
    ORDER BY budget DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total             -- 누적 예산 총합
FROM department
ORDER BY budget DESC;




---ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

---UNBOUNDED PRECEDING	가장 첫 행부터
---CURRENT ROW	현재 행까지

---즉, "처음부터 현재 행까지의 합계를 구하라"는 의미 → 누적 합계 (Running Total)

 

 

SELECT name, dept_name, salary,
  CAST(
    ROUND(
      AVG(CAST(salary AS DECIMAL(10,2))) 
      OVER (
        PARTITION BY dept_name 
        ORDER BY salary DESC 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      ), 2
    ) AS DECIMAL(10,2)
  ) AS moving_avg_salary
FROM instructor
WHERE dept_name IS NOT NULL
ORDER BY dept_name, salary DESC;


--- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 
---현재 행을 기준으로 앞 1명, 뒤 1명 → 총 3명에 대해 평균