窗口函数 SELECT - OVER Clause (Transact-SQL)

最新推荐文章于 2020-12-16 10:57:38 发布

转载最新推荐文章于 2020-12-16 10:57:38 发布 · 149 阅读

本文详细介绍了SQL中的窗口函数及其应用，包括如何通过PARTITION BY进行数据分组，以及ORDER BY子句如何定义每个分组内行的逻辑顺序。此外，还提供了在不同SQL Server版本中实现累计求和的具体示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Determines the partitioning and ordering of a rowset before the associated window function is applied.

That is, the OVER clause defines a window or user-specified set of rows within a query result set.

A window function then computes a value for each row in the window.

You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative累积的 aggregates, running totals, or a top N per group results.

分组

PARTITION BY
Divides the query result set into partitions.

The window function is applied to each partition separately and computation restarts for each partition.

根据什么进行分组

alue_expression
Specifies the column by which the rowset is partitioned.

value_expression can only refer to columns made available by the FROM clause.

value_expression cannot refer to expressions or aliases in the select list.

value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY clause>
Defines the logical order of the rows within each partition of the result set.

That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expression
Specifies a column or expression on which to sort.

order_by_expression can only refer to columns made available by the FROM clause.

An integer cannot be specified to represent a column name or alias.

sql server 2012以上的版本才支持

WITH temp2
AS ( SELECT Id ,
            [number] ,
            ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 )) AS SNO
     FROM   dbo.TestPartition )
SELECT temp2.Id ,
       temp2.number ,
       SUM(number) OVER ( PARTITION BY temp2.Id
                          ORDER BY temp2.SNO ) AS 'number 累计值'
FROM   temp2

sql server 2008以及以下的版本，使用下面的

/*
SQL Server 2005 以及 2008 做法
1.先分区，并编号*/
;
WITH cte
AS ( SELECT Id ,
            number ,
            ROW_NUMBER() OVER ( PARTITION BY Id
                                ORDER BY Id ) AS rnm
     FROM   dbo.TestPartition )
--调试语句
--SELECT * FROM cte
/*
2.再累加。用到了嵌套子查询
*/
SELECT Id ,
       number ,
       (   SELECT SUM(number)
           FROM   cte t1
           WHERE  t1.rnm <= t2.rnm
                  AND --
               t1.Id = t2.Id --保证是同一个人的数据在做累加
       ) AS 'number 累计值'
FROM   cte t2;