我是firewang: 用技术让数据说话,用数据让你我进化。
简介
窗口函数(window function), 也可以被称为 OLAP函数 或 分析函数。
窗口函数是在 ISO SQL 标准中定义的。窗口是用户指定的一组行。窗口函数计算从窗口派生的结果集中各行的值。
可以在单个查询中将多个排名或聚合窗口函数与单个 FROM 子句一起使用。
窗口函数是整个SQL语句最后被执行的部分,这意味着窗口函数是在SQL查询的结果集上进行的, 因此不会受到Group By, Having,Where子句的影响。
语法
-- 聚合函数Aggregate Window Functions< OVER_CLAUSE > :: = OVER ( [ PARTITIONBY value_expression , ... [ n ] ] [ORDER BY order_by_expression ] )
各个部分的解释如下:
PARTITION BY 将结果集分为多个分区。开窗函数分别应用于每个分区,并为每个分区重新启动计算。
value_expression 指定对相应 FROM 子句生成的行集进行分区所依据的列。value_expression 只能引用通过 FROM 子句可用的列。value_expression 不能引用选择列表中的表达式或别名。value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。
指定按其执行窗口函数计算的逻辑顺序。
order_by_expression 指定用于进行排序的列或表达式。order_by_expression 只能引用可供 FROM 子句使用的列 。不能将整数指定为表示列名或别名。
一句话总结:聚合函数 over(partition by 分组字段 order by 排序字段 排序方式) as 别名
特别需要注意的是,在SQL SERVER 2012之前的版本,是不支持聚合窗口函数和order by 连用的。如果是之前的版本,就只支持-- 聚合函数 over(partition by 分组字段) as 别名
优点
SQL更加简洁
执行效率更高
实例
实例的数据使用的是和 SQL笔试50题同样的数据, 使用的平台是SQLFIDDLE(提供在线数据库),鉴于近期全球病情的影响,各种网站都有一定几率无法提供服务,本次提供了SQLite数据库,已存入测试数据。
数据表结构如下:
下载地址(14天内有效):https://c-t.work/s/1786d12bba3e4c
运行结果如下:
假如我想知道累计的总分呢?
运行结果如下:
那很不幸的,我就是SQL SERVER 2012 以前的版本,比如SQL SERVER 2008的怎么办?
很简单,将第一个实例的结果,自连接一份,正好可以利用课程ID来作为累计合计的标志位。
本文项目地址:
https://github.com/firewang/sql50
参考网址:
https://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15