Window函數(shù)在SQL中用于在不合并結(jié)果的情況下，對與當(dāng)前行相關(guān)的多行數(shù)據(jù)進行計算。它區(qū)別于常規(guī)聚合函數(shù)（如SUM或AVG），能保留原始表的所有行，并添加計算值，例如運行總計、排名或移動平均。常見用途包括：1. 行排名（使用RANK(), DENSE_RANK(), ROW_NUMBER()）；2. 累計總和（如按月累計銷售額）；3. 當(dāng)前行與前后行對比；4. 獲取每組前N名?；窘Y(jié)構(gòu)為function_name(arguments) OVER ([PARTITION BY] [ORDER BY] [frame_clause])，其中PARTITION BY分組，ORDER BY排序，frame_clause定義計算范圍。適用于需對比組內(nèi)數(shù)據(jù)、顯示聚合值而不丟失細節(jié)、避免自連接的場景，且僅可在SELECT或ORDER BY中使用。掌握后將極大簡化復(fù)雜查詢。

Window functions in SQL are tools that let you do calculations across a set of table rows that are somehow related to the current row — without collapsing the result into a single value like regular aggregate functions (like SUM() or AVG()) would.

They’re super handy when you want to keep all your original rows but also add computed values, like running totals, rankings, or moving averages.

What Makes Window Functions Different?

Regular aggregates condense data. For example, if you use SUM(sales) without a window function, it’ll give you one total sum for the entire dataset (or grouped subsets). But with a window function, you can show each individual row and its corresponding total, rank, or average at the same time.

For example:

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department)
FROM employees;

This shows every employee’s salary and the average salary in their department — all in one view.

So instead of summarizing and losing detail, window functions preserve context while letting you compute smart summaries.

Common Uses of Window Functions

Here are some of the most common things people use them for:

• Ranking rows: Like ranking salespeople by how much they sold.
• Running totals / cumulative sums: Such as total sales over time.
• Comparing current row to previous/next row: Useful in time-series analysis.
• Getting top N per group: Like top 3 students per class.

Let’s look at a few examples.

Ranking Rows

You can use RANK(), DENSE_RANK(), or ROW_NUMBER() to assign ranks within groups:

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;

This ranks employees by salary within each department, so you can easily see who's the highest earner in each.

Running Totals

If you're tracking monthly sales, a running total helps show growth over time:

SELECT month, sales,
       SUM(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM monthly_sales;

That gives you a cumulative total up to each month.

Basic Structure of a Window Function

The general syntax looks like this:

function_name (arguments) OVER (
    [PARTITION BY column_list]
    [ORDER BY sort_columns]
    [frame_clause]
)

• PARTITION BY: Similar to GROUP BY — it divides rows into groups to apply the function separately.
• ORDER BY: Controls the order of rows inside the window.
• frame_clause: Defines which rows are included in the calculation (like from the first row to current row).

Some databases have slightly different defaults or supported clauses, so it's good to check what's available in your system.

When Should You Use Them?

Use window functions when you need to:

• Compare a row to others in the same group.
• Show aggregated values next to raw data.
• Avoid self-joins just to calculate something relative.

They simplify complex queries and often perform better than alternatives like correlated subqueries or multiple joins.

Just remember: window functions only work in SELECT or ORDER BY clauses — not in WHERE.

基本上就這些。Once you get the hang of them, they become one of those SQL features you wonder how you lived without.

以上是SQL中的窗口功能是什么？的詳細內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！