第十五章 SQL窗口函数概述(二)
第十五章 SQL窗口函数概述(二)
ROW子句
ROW
子句可以与FIRST_VALUE
(字段)和SUM
(字段)窗口函数一起使用。
可以为其他windows
函数指定它,但不执行任何操作(有或没有ROWS
子句的结果相同)。
ROWS
子句有两种语法形式:
ROWS framestart
ROWS BETWEEN framestart AND frameend
Framestart
和frameend
有五个可能的值:
UNBOUNDED PRECEDING /* 从当前分区的开头开始 */
offset PRECEDING /* 开始偏移当前行之前的行数 */
CURRENT ROW /* 从当前行开始 */
offset FOLLOWING /* 继续偏移当前行后面的行数 */
UNBOUNDED FOLLOWING /* 继续到当前分区的末尾 */
ROWS
子句语法可以在两个方向上指定一个范围。
例如,在无界前面和1后面之间的行以及在1后面和无界前面之间的行是完全相等的。
ROWS
框架的开始语法默认为CURRENT ROW
作为范围的第二个未指定的边界。
因此,以下是等价的:
ROWS UNBOUNDED PRECEDING | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
ROWS 1 PRECEDING | ROWS BETWEEN 1 PRECEDING AND CURRENT ROW |
ROWS CURRENT ROW | ROWS BETWEEN CURRENT ROW AND CURRENT ROW |
ROWS 1 FOLLOWING | ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING |
ROWS UNBOUNDED FOLLOWING | ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
如果没有指定ROWS
子句,则默认为在无界的前一行和当前行之间的行数。
ROW子句示例
下面的查询返回包含大量“噪声noise”
(随机变量)的分数。ROWS
子句用于“平滑smooth”
这些变化,方法是按排序顺序将每个分数与其前一个分数和紧随其后的分数相加,然后除以3,得到滚动平均分数:
SELECT Item,Score,SUM(Score)
OVER (ORDER BY Score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)/3
AS CohortScore FROM Sample.TestScores
操作为:(PrecedingScore+CurrentScore+FollowingScore)/3
,注意底部和顶部的CohortScore
值不准确,因为它们是将0加到两个计分值上,然后除以3
:(0+CurrentScore+FollowingScore)/3
和(PrecedingScore+CurrentScore+0)/3
。
使用窗口函数
窗口函数可用于:
SELECT list as a listed select-item
.
窗口函数不能嵌入到子查询或选择项列表中的聚合函数中。
ORDER BY clause
.
窗口函数不能在and ON
、WHERE、GROUP BY
或HAVING
子句中使用。
尝试这样做会导致SQLCODE -367
错误。
列名和别名
默认情况下,分配给窗口函数结果的列名是Window_n
,其中n
号后缀是列序号,正如在SELECT
列表中指定的那样。
因此,下面的例子创建了列名Window_3
和Window_6
:
SELECT Name,Home_State,ROW_NUMBER() OVER (PARTITION BY Home_State),Age,AVG(Age),ROW_NUMBER() OVER (ORDER BY Age)
FROM Sample.Person
如果要指定另一个列名(列别名),请使用AS关键字:
SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age
FROM Sample.Person
可以使用列别名在ORDER BY
子句中指定一个窗口字段:
SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age
FROM Sample.Person
ORDER BY StateRow
不能在ORDER BY
子句中使用默认的列名(如Window_3
)。
ORDER BY
因为在窗口函数求值之后,ORDER BY
子句应用于查询结果集,所以ORDER BY
不会影响选择项窗口函数分配的值。
相关文章
- Qt-QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connect...
- PL/SQL经典练习
- 你真的会玩SQL吗?实用函数方汇总
- Spark sql -- Spark sql中的窗口函数和对应的api
- Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
- 【学习总结】SQL的学习-1-初识数据库与sql
- mysql 必知必会整理—sql 计算函数[六]
- 【学习总结】SQL的学习-2-sql操作
- SQL工具性能实测:居然比Navicat还快,数百万行数据导出仅51秒
- Sql Server 常用函数
- 网友求助,关于function module DB_SQL_WRITE_TO_ITAB
- F4 value help and HANA native SQL
- SQL Server 为什么事务日志自动增长会降低你的性能
- 获取sql server与年、季度、月、周首尾有关的时间函数
- Atitit mybatis的扩展使用sql udf,js java等语言 目录 1.1. 默认,mybatis使用xml,sql等语言来书写业务流程1 2. 使用java扩展函数1 2.1.
- Atitit 得到mybatis 实际 sql 1.1. 使用mybatis工具提供的,只能出现问号一大堆不行1 1.2. 配置log 打印sql依然不行,里面有问号。。4 1.3. 配置p
- Atiitt 使用java语言编写sql函数或存储过程
- 怎样能写出性能优良的SQL语句 从sql语句提高数据库的性能
- Database之SQL:SQL之over partition by开窗函数的简介、使用方法(求各班级内各自排名/求各班级内第一名/求各班级内分数递增和等案例解析)之详细攻略
- SQL Server语句大全(增删改查数据、创建表、删除表、修改表)
- 开发一个不需要重写成Hive QL的大数据SQL引擎
- 【编程实践】如何使用 SQL 函数 How To Use Functions in SQL
- SQL Server DATEADD() 当前时间减7小时
- 优化sql语句
- sql 导航函数 lag
- 【SQL开发实战技巧】系列(二十七):数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
- 大数据不就是写sql吗?—— Hive:把sql解析后用MapReduce跑 SparkSQL:把sql解析后用Spark跑,比hive快点 Drill/Impala/Presto:交互式查询OLAP Druid/Kylin:强调预计算,同样是OLAP
- SQL FORMAT() 函数
- SQL SUM() 函数
- SQL MIN() 函数
- SQL Server日期函数
- 【项目实战】MyBatis-Plus 配置属性介绍以及如何开启与关闭SQL日志打印
- Mybatis3源码分析:Sql解析执行-结果集映射(ResultSetHandler)