← Back to list
Database
#Window Function#OVER절#PARTITION BY#순위 함수#분석 함수
Last updated · 2026-11-13

Window Function

1. Overview

A Window Function is a SQL analytic function that, without collapsing result rows into one, performs aggregation, ranking, or offset computation over a set of rows logically related to the current row (a window) and returns one result value per row. The decisive difference from an ordinary aggregate function is that it specifies the window scope with the OVER clause.

Traditional aggregate functions (SUM, AVG, COUNT), when combined with GROUP BY, fold many rows into a single summary row. In practice, however, most analytic queries require that "each detail line be shown as-is, with a department average, running total, or ranking attached alongside it." In the past this forced analysts to self-join the same table or repeatedly write correlated subqueries, producing queries that were long, slow, and poorly readable. Window functions emerged precisely to close this gap — preserving detail data while delivering aggregate insight in parallel — in a single construct.

Window functions were formally introduced in the SQL:2003 standard and have since become the de facto standard tool for analytic queries. Oracle supported them early as Analytic Functions, SQL Server offered ranking functions and frame capabilities, PostgreSQL has supported them since version 8.4, and MySQL relatively late from 8.0 (2018), so today nearly every relational DBMS and analytic engine provides them. They are a core operation of data warehousing, BI, and real-time reporting, and from a professional engineer's viewpoint the key argument is the design implication of "raising the expressive power of aggregation declaratively, pushing application logic down into the DB."

Recalling how painful analytic queries were before window functions makes their necessity clear. To find "the highest-paid person in each department," you had to build a subquery computing each department's maximum salary and join it back to the source; to compute a "running total," you had to write a correlated subquery summing every row at or below the current one, sacrificing performance. Such queries were not only hard to write but hard to read for intent. By replacing this repetitive, error-prone pattern with standardized declarative syntax, window functions let analysts focus on the data question itself, a significant contribution to productivity.

Their characteristics can be summarized as follows. First, they preserve the number of rows (N input rows → N output rows). Second, they declaratively specify partition, ordering, and frame through the OVER clause. Third, they can be used only in the SELECT and ORDER BY clauses and not directly in WHERE, GROUP BY, or HAVING (because they are evaluated after these in logical execution order). Fourth, multiple window functions can be mixed with different windows in one query, giving very high expressive power.

Thanks to these properties, window functions are regarded as a technology that changed the paradigm of analytic queries, not merely syntactic convenience. Previously, obtaining detail data and aggregate insight together required reverting to procedural thinking (iterating a cursor to update accumulator variables) or writing complex nested queries, whereas window functions complete a declarative approach in which you declare only "what to compute" and the optimizer takes responsibility for "how to compute it." In a professional engineer's answer, presenting this in the context of "expanding the expressive power of set-oriented SQL" is persuasive.

2. Structure and Operating Principle of Window Functions

A window function is evaluated in the query's logical processing pipeline after FROM, WHERE, GROUP BY, and HAVING have all been passed, at a stage where the result set is nearly finalized. Because of this position, filtering on a window function result in the WHERE clause requires wrapping it once in a subquery or CTE, a practical constraint. The structure diagram below shows when a window function is evaluated.

flowchart TB
    A["FROM / JOIN<br/>(build source set)"] --> B["WHERE<br/>(row filtering)"]
    B --> C["GROUP BY / aggregate<br/>(group summary)"]
    C --> D["HAVING<br/>(group filtering)"]
    D --> E["window function evaluation<br/>(OVER: partition, order, frame)"]
    E --> F["SELECT projection"]
    F --> G["ORDER BY / LIMIT"]
    style E fill:#e1f5ff,stroke:#0288d1

As the diagram shows, a window function operates just before SELECT projection but after group aggregation. Thus a two-stage analysis is possible, laying a window function atop a result already summarized by GROUP BY to "compute per-group subtotals, then rank those subtotals." Failing to understand this execution order leads to the common mistake of "I used a window function in WHERE and got an error."

The standard pattern for working around this constraint is wrapping it once in a CTE (Common Table Expression) or inline view. The inner query computes rank or cumulative values with a window function as columns, and the outer query filters on those columns in its WHERE. For example, a Top-N query such as "only the #1 in each department" is written as a two-stage structure that assigns ROW_NUMBER inside and applies WHERE rn = 1 outside. Once this pattern is internalized, the execution-position constraint of window functions turns from a limitation into a readable structure that "explicitly separates analysis stages."

A. The Three Components of the OVER Clause

A window function's behavior is determined entirely by the three elements of the OVER clause — partition (PARTITION BY), ordering (ORDER BY), and frame (Frame). Combining these precisely controls "which rows to view around the current row."

PARTITION BY (partition split) divides the entire result set into several independent compartments by the values of the specified columns. Each partition is fully isolated, so the window function treats only rows within the same partition as computation targets. For example, PARTITION BY dept draws boundaries per department so that ranking and accumulation are computed only within a department. Omitting PARTITION BY makes the entire result set one partition. Conceptually it resembles GROUP BY, but the decisive difference is that whereas GROUP BY folds a group into a single row, PARTITION BY only limits the computation scope while preserving the rows. Note here that PARTITION BY is an entirely different concept from a table's physical storage partitioning — the former is a logical computation compartment at query execution time, the latter a data-placement strategy on disk; they share a name and are easily confused, but their layers are completely different.

ORDER BY (window ordering) sets the order of rows within each partition. Ranking functions (ROW_NUMBER, RANK, etc.) and offset functions (LAG, LEAD) have meaning only with this order, and running aggregates (running total) likewise accumulate values along the sort order. Note that the ORDER BY inside the OVER clause does not guarantee the ordering of the query's final result — it is merely the order for window computation, and the final output order must be specified with a separate top-level ORDER BY. Also, when NULLs are mixed into the sort key, ranking and offset results differ depending on NULLS FIRST/NULLS LAST, so specifying the NULL-ordering rule in the specification prevents defects.

Frame (window frame) is the mechanism that, within a sorted partition, more narrowly specifies "the range of rows actually included in the computation relative to the current row." There are three modes: ROWS BETWEEN ... AND ... (physical row-count basis), RANGE BETWEEN ... (sort-key value basis), and GROUPS BETWEEN ... (peer-group basis). For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW takes the preceding two rows and the current row — three rows — as the window to compute a moving average. If no frame is specified, the default frame applies: with ORDER BY, it is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (from the start to the current row); without it, the entire partition is the default.

OVER element Role Behavior when omitted
PARTITION BY Split computation scope into compartments Whole set is a single partition
ORDER BY Set row order within a partition No order (ranking/offset meaningless)
Frame (ROWS/RANGE/GROUPS) Limit inclusion range relative to current row Default frame per presence of ORDER BY

This default-frame rule is the most notorious source of pitfalls in practice. The reason LAST_VALUE(x) OVER (ORDER BY d) returns "the current row's value" rather than "the partition's last value" is precisely that the default frame is cut off at the current row. To use it correctly you must open the frame fully with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Using window functions properly means understanding precisely the default and explicit frame values.

The semantic difference between ROWS and RANGE must also be distinguished at design time. ROWS counts the range by sorted physical row number, creating a count-based window such as "the preceding three rows," whereas RANGE uses the sort key's value as the basis, bundling "peer rows sharing the current row's sort value" into one chunk. Thus, computing a running sum with RANGE on a table where the same date appears multiple times makes all rows of the same date share the same cumulative value — correct if that is the intent, but a bug if "row-by-row sequential accumulation" was wanted. The fact that the two modes diverge when the sort key has duplicates must be verified by tests.

3. Types of Window Functions

Window functions fall into three broad categories: ranking functions, offset (analytic) functions, and aggregate window functions. Each category differs in sensitivity to the frame and in typical use. The detail diagram below shows the process by which the frame moves along with the current row over a sorted partition and aggregation accumulates.

flowchart LR
    subgraph P["Partition: Dept A (sorted by sales)"]
      direction LR
      R1["row1<br/>100"] --> R2["row2<br/>150"] --> R3["row3<br/>200"] --> R4["row4<br/>250"]
    end
    R3 -. "current row" .-> CUR["frame:<br/>UNBOUNDED PRECEDING ~ CURRENT ROW<br/>running sum=450"]
    style R3 fill:#ffe0b2,stroke:#e65100
    style CUR fill:#e1f5ff,stroke:#0288d1

A. Ranking Functions

Ranking functions assign a rank to each row within a sorted partition. ROW_NUMBER() assigns a unique serial number 1, 2, 3… regardless of ties; RANK() gives ties the same rank but skips the next rank (1, 1, 3); DENSE_RANK() gives ties the same rank without skipping (1, 1, 2). NTILE(n) divides a partition into n equal buckets to compute quantiles (quartiles, deciles, etc.), and PERCENT_RANK() / CUME_DIST() return percentile position and cumulative distribution.

This subtle difference is decisive in practice. For example, in a Top-N query that "extracts only the top 3 by sales per department," the number of resulting people changes with the tie-handling policy. ROW_NUMBER returns exactly 3 even when there are ties, while RANK or DENSE_RANK includes all tied at 3rd and may return 3 or more. Whether an online mall, picking "the 10 bestsellers per category," exposes all tied products or arbitrarily cuts to 10 is exactly this choice. The Top-N pattern is the most representative case of a window function replacing a correlated subquery; the standard is a two-stage structure that ranks with PARTITION BY category ORDER BY sales DESC and then filters externally with WHERE rnk <= 10.

Ranking functions are also used as the standard technique for deduplication. On a table with duplicated natural keys, "keep only the most recent record" is cleanly solved by numbering each key group with ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY updated_at DESC) and taking only row 1. The logic of filtering out late-arriving duplicate events in an ingestion pipeline is exactly this idiom; whereas DISTINCT or group aggregation cannot control "which row to keep as the representative," window functions are superior in that they can specify the representative-row selection rule through the sort criterion. NTILE is used for segmentation that grades customers into spending deciles, and CUME_DIST for computing percentile position of grades or salaries.

B. Offset / Value Functions

Offset functions directly reference the value at a specific position before or after the current row. LAG(x, n) fetches the value n rows earlier and LEAD(x, n) the value n rows later, enabling period-over-period change analysis without a self-join. For example, in a monthly sales table, sales - LAG(sales,1) OVER (ORDER BY month) computes the month-over-month change in one line. FIRST_VALUE / LAST_VALUE / NTH_VALUE return the value at a specific position within the frame, used for relative comparison against each group's reference value (e.g., the name of the department's top earner).

As a real case, in financial data analysis the day-over-day return of a stock price is expressed as (close - LAG(close) OVER (PARTITION BY ticker ORDER BY date)) / LAG(close) OVER (...), compressing into a single construct a computation that previously required a complex self-join stitching dates together. In user churn analysis too, LEAD(login_date) - login_date is widely used to compute the gap until the next login and detect dormancy transition.

Offset functions are especially a core tool for sessionization and time-series interval splitting. In web log analysis, the logic of computing the gap between a single user's consecutive events as event_time - LAG(event_time) and cutting a new session when that gap exceeds 30 minutes is arduous to write without window functions. Applying a running sum again to the "session boundary flag" thus computed can assign session numbers, becoming the typical analytic-engineering idiom of combining offset and aggregate window functions in stages. In numbers, work that took tens of minutes to sessionize tens of millions of logs with procedural code is reported to be processed in tens of seconds with window-function-based SQL on an analytic engine.

C. Aggregate Window Functions

Attaching an OVER clause to existing aggregate functions such as SUM, AVG, COUNT, MIN, MAX makes them aggregate window functions. Depending on how the frame is specified, various analyses are possible: running total, moving average, share within group (each row value / partition sum), and so on. Cumulative sales are expressed as SUM(sales) OVER (ORDER BY date), and a 7-day moving average as AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

As an industry application, a retailer's daily sales dashboard shows the day's sales, cumulative sales, and the recent 7-day moving average on one screen, and all three metrics are produced by a single set of aggregate window functions over the same table. When a telecom's data-usage report computes "what percentage of the overall plan average this month's cumulative usage is," it is likewise solved in a single query in the form SUM(...) OVER (PARTITION BY subscriber ...) / AVG(...) OVER (), specifying numerator and denominator with different windows.

Which of the three categories to use is decided by "what you want to know around the current row." If you want relative position (rank, quantile), choose a ranking function; if you want the value of a specific row before or after, an offset function; if you want a summary such as the sum or average of related rows, an aggregate window function. A single dashboard often mixes all three, so the design capability to understand each function's frame sensitivity and combine them appropriately governs the quality of analytic queries.

Another strength of aggregate window functions is that they can compute share within group and overall share simultaneously. sales / SUM(sales) OVER (PARTITION BY dept) shows the within-department contribution and sales / SUM(sales) OVER () the company-wide contribution side by side in the same row. This is especially useful in analyses where "an individual value and a cumulative share must be seen together," such as Pareto analysis (what % of the total the top 20% occupy) or ABC inventory classification. For moving averages, varying the frame width to 7 days and 28 days extracts short- and medium-term trend lines in one query, enabling noise-removed trend comparison, and it also serves as a base metric for anomaly detection (flagging intervals where the actual value deviates from the moving average beyond a threshold).

4. Comparison with GROUP BY and Subqueries

To understand a window function's value, one must examine why the difference arises from the traditional techniques it replaces. The key lies in "whether rows are preserved" and "how many times the scan runs."

GROUP BY folds a group into a single row, so to view detail rows and aggregate values together you must join the source table back to the aggregate result. Window functions eliminate this join and return detail and aggregate simultaneously in one logical scan. The correlated-subquery approach repeatedly runs the inner aggregate for each row of the outer query, so it incurs inefficiency close to O(N²) in principle, whereas a window function finishes with a single sequential pass after sorting and is generally far more efficient. In measured cases, computing a running total over millions of rows with a correlated subquery that took tens of seconds is often shortened to within a few seconds with a window function.

Category GROUP BY aggregate Correlated subquery Window function
Row preservation No (folded) Yes Yes
Detail + aggregate together Extra join needed Possible but repeated runs Single construct
Performance profile Good O(N²) tendency Single pass after sort
Readability Moderate Low (nested) High (declarative)

That said, window functions are not always superior. If you simply need "one line of group totals," GROUP BY is more concise, and on large volumes with heavy sort cost, a window function's sort stage can become a bottleneck. Thus choosing the tool by the purpose of "must detail be preserved, or is aggregate only needed" is the practical implication.

Another notable difference is the handling of NULLs and empty groups. GROUP BY does not show a group at all in the result if there is nothing to aggregate, but a window function preserves every detail row, so even intervals with no value are explicitly revealed. This matters in continuous time-series reporting where "days with no data must show sales as 0"; joining a window function with a calendar table can draw a complete trend line including missing intervals. Conversely, the more a correlated subquery tries to meet such a requirement, the deeper its nesting and the harder its maintenance. Ultimately the choice among the three techniques comes down not to a simple performance issue but to how precisely the analytic intent (detail preservation, continuity, representative-row selection) is expressed in SQL.

5. Advanced: Performance Optimization and Recent Trends

In practice, window-function performance is governed almost entirely by sort cost. If an index whose sort order matches the PARTITION BY and ORDER BY columns exists, the DB can use the index order directly without an extra sort (index strategy), greatly reducing cost. Conversely, if the sort key diverges from the index, a large sort and temp spill occur and performance plummets. When multiple window functions within the same query share the same OVER specification, most optimizers perform the sort only once, so unifying window definitions or reusing them with the WINDOW clause (named window) is effective. The RANGE frame searches the same sort-value group each time and can be costlier than the ROWS frame, so prefer ROWS when the meaning is equal.

A named window (WINDOW clause) contributes to readability as well as performance. When multiple functions repeatedly reference the same PARTITION BY ... ORDER BY ... definition, defining it once at the end of the query as WINDOW w AS (...) and reusing it in each function as OVER w shortens the query and lets you fix one place on a definition change. The larger the analytic query, the more this idiom raises maintainability.

As for recent trends, analytics-specialized columnar engines (ClickHouse, DuckDB, Apache Spark, Snowflake, BigQuery, etc.) accelerate window functions with vectorized, parallel execution, providing real-time analysis even at billions of rows. The SQL standard has also evolved, adding the GROUPS frame mode and the frame-exclusion (EXCLUDE) option, which PostgreSQL 11 and later support. Window functions have also become a key means of pushing transformation logic down into SQL in the data warehouse's ELT pattern, establishing themselves as the standard idiom for expressing cumulative, ranking, and sessionization logic in the models of analytic-engineering tools such as dbt. In stream processing (e.g., Apache Flink) too, time-window-based analytic functions inherit the window concept of batch SQL and handle real-time aggregation.

However, note that windows in a streaming environment differ subtly in meaning from window functions in batch SQL. Batch window functions apply ordering and frames over an already-finalized finite result set, whereas streaming's time windows (tumbling, sliding, session windows) slice an endlessly flowing infinite stream along the time axis to aggregate and handle late-arriving data with watermarks. The two concepts share the term "window" and are easily confused, but the former is a problem of the expressive power of analytic queries, while the latter is a problem of state management and time semantics (event-time vs processing-time), so their approaches differ. Pointing out this distinction in a professional engineer's answer can reveal conceptual depth.

6. Considerations and Implications

From a professional engineer's viewpoint, the adoption and use of window functions must consider the following comprehensively.

  • Application strategy (embedding logic in the DB): Pushing analytic logic such as ranking, accumulation, and sessionization from application code down to the DB's window functions reduces data movement and improves maintainability through declarative expression. However, excessive concentration of business logic in the DB can create DBMS dependency and scaling limits, so a boundary limiting it to aggregate/analytic-natured logic is needed.

  • Performance trade-off (sort cost vs. expressive power): Window functions have high expressive power but incur sort and frame-evaluation cost. Cost must be managed through index design consistent with partition/sort keys, sharing identical windows, preferring the ROWS frame, and using columnar/analytic engines at large volume, and a selection criterion of "GROUP BY for simple aggregation, window functions when detail preservation is needed" must be established.

  • Accuracy and pitfall management: The LAST_VALUE error from the default frame, the constraint that window results cannot be referenced directly in WHERE, and result differences by tie-handling policy (RANK vs ROW_NUMBER) are pitfalls that must be explicitly verified at the design stage. Test cases must include tie, boundary, and NULL-ordering (NULLS FIRST/LAST) situations.

  • Standardization and portability: Window functions are based on the SQL standard, but support scope and syntax (especially frame options) differ by DBMS. In multi-cloud and heterogeneous-DB environments, a strategy of using the standard common subset or absorbing dialect differences with an abstraction layer such as dbt is required.

  • Governance and reproducibility: The more metrics such as rank, segment, and share are defined with window functions, the more subtle differences in tie-handling, frame, and sort rules change the metric figures. Thus governance that defines core metrics' window definitions once in a metric layer (semantic layer) for the whole company to share, and tracks definition changes with configuration management, is the key to guaranteeing the reproducibility and trust of figures.

  • Related-technology outlook: Window functions are a core operation of the data lakehouse, real-time analysis, and analytic engineering (ELT), growing in importance as they combine with columnar storage, vectorized execution, and streaming SQL. Since ranking and trend queries most frequently resolve into window functions even in future generative-AI-based natural-language-to-SQL translation, their value as a foundation of analytic-query design capability persists.

Likely exam directions include ① the difference between GROUP BY and window functions and selection criteria, ② the roles of the three OVER elements (partition, order, frame) and the default-frame pitfall, ③ the differences among the three ranking functions (ROW_NUMBER/RANK/DENSE_RANK) and their use in Top-N and deduplication, ④ writing practical queries for aggregate windows such as running sums and moving averages, and ⑤ performance optimization through sort cost and indexes. In composing an answer, a flow that starts from the background of "why detail-preserving aggregation is needed," deepens with the operating principle of the OVER clause and concrete query/numeric cases, and closes with comparison to traditional techniques, performance, and recent trends is persuasive.

References


In one line: A window function is a core SQL analytic tool that specifies a set of rows related to the current row via the OVER clause's partition, order, and frame to compute ranking, accumulation, and moving averages while preserving detail data, replacing self-joins and correlated subqueries to raise both expressive power and performance.