数据湖计算 DLC 窗口函数-SQL 语法-文档中心-腾讯云

row_number
函数语法：
  row_number()
支持引擎：SparkSQL、Presto
使用说明：为每一行分配一个唯一的连续编号
返回类型：int 
示例：
﻿
SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
A1	1	1
A1	1	2
A1	2	3
A2	3	1
rank
函数语法：
  rank()
支持引擎：SparkSQL、Presto
使用说明：计算某个值在一组值中的排名。如果出现排名相等，则在排名序列中留出空位。
返回类型：int
示例：
SELECT a, b, rank() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	1
A1	2	3
A2	3	1
dense_rank
函数语法：
  dense_rank()
支持引擎：SparkSQL、Presto
使用说明：计算某个值在一组值中的排名，如果出现排名相等，与函数rank不同，dense_rank不会在排名序列中产生空位。
返回类型：int
示例：
SELECT a, b, dense_rank() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	1
A1	2	2
A2	3	1
percent_rank()
函数语法：
  percent_rank()
﻿
支持引擎：SparkSQL、Presto
使用说明：计算某个值在一组值中的百分比排名。返回值以 0 到 1 之间的小数表示。
返回类型：double
示例：
SELECT a, b, percent_rank() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	0.0
A1	1	0.0
A1	2	1.0
A2	3	0.0
cume_dist
函数语法：
cume_dist()  
支持引擎：SparkSQL、Presto
使用说明：计算某个值在分区中相对于所有值的位置。
返回类型：double
示例：
SELECT a, b, cume_dist() OVER (PARTITION BY a ORDER BY b)  FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	0.6666666666666666
A1	1	0.6666666666666666
A1	2	1.0
A2	3	1.0
first_value
函数语法：
  first_value(col)
支持引擎：SparkSQL、Presto
使用说明：返回分区中某列第一条数据的值
返回类型：col列的数据类型
示例：
SELECT a, b, first_value(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	1
A1	2	1
A2	3	3
last_value
函数语法：
  last_value(col)
支持引擎：SparkSQL、Presto
使用说明：返回分区中某列最后一条数据的值
返回类型：int
示例：
SELECT a, b, last_value(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	1
A1	2	2
A2	3	3
lag
函数语法：
 lag(col[, n [, default]]) 
支持引擎：SparkSQL、Presto
使用说明：返回窗口中当前行向上第 n 行的值。n 默认值为 1，default 默认值为 null。如果第 n 行的 值为 null，则返回 null。如果不存在这样的偏移行（例如，当偏移量为1时，窗口的第一行没有任何向上行），则返回 default 。第一个参数为列名，第二个参数为之前第 n 行，第三个参数为默认值。
返回类型：col列的数据类型
示例：
SELECT a, b, lag(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	NULL
A1	1	1
A1	2	1
A2	3	NULL
lead
函数语法：
  lead(col[, n[, default]])
支持引擎：SparkSQL、Presto
使用说明：返回窗口中当前行向下第 n 行的值。n 默认值为 1，default 默认值为 null。如果第 n 行的 值为 null，则返回 null。如果不存在这样的偏移行（例如，当偏移量为1时，窗口的最后一行没有任何向下行），则返回 default 。第一个参数为列名，第二个参数为之前第 n 行，第三个参数为默认值。
返回类型：col列的数据类型
示例：
SELECT a, b, lead(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	2
A1	2	NULL
A2	3	NULL
nth_value
函数语法：
  nth_value(col[, n])
支持引擎：SparkSQL、Presto
使用说明：返回距窗口头第 n 行的值。n 从 1 开始。如果ignoreNulls=true，查找第 n 行时将跳过 null。否则，每一行都计入 n。如果不存在这样的第 n 行（例如，当 n 为10时，窗口大小小于10），则返回null。第一个参数为列名，第二个参数为之前第 n 行。
返回类型：col列的数据类型
示例：
SELECT a, b, nth_value(b, 2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	1
A1	2	1
A2	3	NULL 
ntile
函数语法：
  ntile(n)
支持引擎：SparkSQL、Presto
使用说明：将窗口分区的行划分为 n 个桶，返回行所在的桶数，范围从 1 到 n
返回类型：int
示例：
SELECT a, b, ntile(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)
A1	1	1
A1	1	1
A1	2	2
A2	3	1
CLUSTER_SAMPLE
函数语法：
  CLUSTER_SAMPLE(<int> N[, <int> M]) over (PARTITION BY col1 ORDER by col2)
支持引擎：SparkSQL
使用说明：在窗口内按指定的比例或数量采样。
N：必填，int 类型，当只有 N 时，表示采样出 N 条数据。采样结果接近 N 条，但不保证一定是 N 条。
M：可填，int 类型，当 M 被指定时，表示采样出 M/N * 窗口内总条数数量的数据。采样结果接近 M/N * 总条数。
返回类型：boolean 类型，true 表示被采样，false 表示未采样。
示例：
> SELECT a, b, cluster_sample(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
A1 2 true
A1 1 true
A2 3 true
A1 1 false
> SELECT a, b from (select a, b, cluster_sample(2) OVER (PARTITION BY a ORDER BY b) as c FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b)) where c;
A1 2
A1 1
A2 3
﻿
窗口函数

本页目录：

row_number

rank

dense_rank

percent_rank()

cume_dist

first_value

last_value

lag

lead

nth_value

ntile

CLUSTER_SAMPLE
