윈도우 함수(OVER / PARTITION BY)

Oracle

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_no,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no,
  LAG(salary)    OVER (PARTITION BY department ORDER BY salary)      AS prev_salary
FROM employee;

윈도우 함수는 행을 그룹화하지 않고(행 수를 줄이지 않고) 각 행에 대해 '윈도우' 범위로 계산한다. PARTITION BY는 윈도우를 나누고, ORDER BY는 정렬한다. Oracle은 이러한 분석 함수를 선도적으로 도입했으며 ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, SUM() OVER 등 풍부한 함수를 제공한다.

PostgreSQL

SELECT name, department, salary,
  ROW_NUMBER() OVER w AS row_no,
  RANK()       OVER w AS rank_no,
  LEAD(salary)   OVER w AS next_salary
FROM employee
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

PostgreSQL은 모든 표준 윈도우 함수를 지원하며, 반복되는 윈도우 정의에 WINDOW ... AS로 이름을 붙일 수 있다. ROWS/RANGE/GROUPS 프레임 정의로 누적 합계, 이동 평균 등을 계산할 수 있다.

MySQL

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_no,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no,
  LAG(salary)    OVER (PARTITION BY department ORDER BY salary)      AS prev_salary
FROM employee;

MySQL 8.0(및 MariaDB 10.2+)은 윈도우 함수를 지원하며, 그 이전에는 사용자 변수나 서브쿼리로 흉내 냈다. ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG/LEAD, FIRST_VALUE/LAST_VALUE와 집계 함수의 OVER 형식을 사용할 수 있다.

SQL Server

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_no,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no,
  SUM(salary)    OVER (PARTITION BY department) AS dept_total
FROM employee;

SQL Server는 2005부터 순위 윈도우 함수를, 2012부터 프레임 집계와 LAG/LEAD 같은 함수를 지원한다. ROW_NUMBER는 페이징(OFFSET/FETCH와 함께)과 그룹별 상위 N개 행 찾기에 흔히 사용된다.

MongoDB

// Closest equivalent: $setWindowFields (MongoDB 5.0+)
db.employee.aggregate([
  { $setWindowFields: {
      partitionBy: "$department",
      sortBy: { salary: -1 },
      output: { row_no: { $rank: {} },
                prev_salary: { $shift: { output: "$salary", by: -1 } } }
  } }
])

MongoDB 5.0+의 $setWindowFields 단계는 SQL 윈도우 함수에 해당한다. partitionBy와 sortBy로 윈도우를 정의하며 $rank/$denseRank/$documentNumber, $shift(LAG/LEAD), 누적 합계 같은 출력을 생성한다. 이전 버전에서는 $group + 코드로 해결했다.

Redis

# Not applicable: Redis has no window functions.

Redis는 분석용 윈도우 함수를 제공하지 않는다. 정렬된 결과와 순위에는 Sorted Set(ZSET) 구조를 사용한다. ZADD로 점수를 부여하고 ZRANK/ZREVRANK로 순위를, ZRANGE로 범위를 읽는다. 복잡한 분석은 애플리케이션에서 수행한다.

Elasticsearch

# Closest equivalent: top_hits and pipeline aggregations
GET employee/_search
{
  "size": 0,
  "aggs": { "dept": { "terms": { "field": "department" },
    "aggs": { "top_paid": { "top_hits": {
      "sort": [ { "salary": "desc" } ], "size": 3 } } } } }
}

Elasticsearch에는 직접적인 윈도우 함수가 없다. 그룹별 상위 N개 레코드에는 'top_hits' 하위 aggregation을, 이동/누적 계산에는 'derivative', 'cumulative_sum', 'moving_fn' 같은 pipeline aggregation을 사용한다. 정렬과 분할은 terms aggregation으로 수행한다.