窗口函数 SELECT - OVER Clause (Transact-SQL)
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;
相关文章
- SQL思维快速上手使用Pandas
- SQL 函数
- Oracle中模拟SQL中的isnull函数
- 第135章 SQL函数 SUBSTR
- 第九十八章 SQL函数 MONTHNAME
- 第九十一章 SQL函数 LOWER
- 第八十五章 SQL函数 $LISTGET
- 第四十九章 SQL函数 DAYOFWEEK
- 第三十五章 SQL函数 CURRENT_DATE
- 第十五章 SQL窗口函数概述(二)
- 重要的 SQL Server 函数 - 其他函数
- Java Date Time 教程-java.sql.Date
- 《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》——1.1 窗口函数的背景
- 《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》——1.6 窗口定义的重复使用
- 《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》——1.7 小结
- 《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》导读
- SQL Server连接数据库失败,可能的问题!
- SQL数据库基础
- sql server CDC报错:超出存储过程、函数、触发器的最大嵌套层数(最大层为32)
- 如何查看正在执行sql的语句及其父语句调用?如何查看正在执行SQL的具体参数值与执行计划?xml执行计划转为图形计划
- (1.2)sql server窗口函数,sql server分析函数(以及top ties/tablesample)
- java实现sql批量插入参数
- SQL单行函数和多行函数
- SQL Server 用ip地址登录 127.0.0.1