窗口函数--日志服务-火山引擎

文档中心

立即注册

导航

窗口函数

最近更新时间：2024.09.29 14:09:12首次发布时间：2024.04.19 11:28:18

本文介绍日志服务支持的窗口函数语法及常见场景的使用示例。
窗口函数用于聚合、排序等操作，包含分区、排序和框架这三个核心元素。不同于普通聚合函数，窗口函数支持为每一行数据生成一个结果。

函数列表

说明

在日志服务分析语句（SQL 语句）中，需要使用单引号（''）包裹代表字符串的字符，无符号包裹或被双引号（""）包裹的字符为字段名或列名。例如'time' 代表字符串，time 或 "time" 代表字段名或列名。

函数名称	语法	说明
CUME_DIST 函数	CUME_DIST()	计算当前行在其所属窗口分区内的累积分布比例。
DENSE_RANK 函数	DENSE_RANK()	对窗口分区内值的排名。
FIRST_VALUE 函数	FIRST_VALUE(KEY)	返回各个窗口分区内第一行的值。
LAG 函数	LAG(KEY, offset, default_value)	查询分区内位于当前行上方第 offset 行的值。
LAST_VALUE 函数	LAST_VALUE(KEY)	返回各个窗口分区内最后一行的值。
LEAD 函数	LEAD(KEY, offset, default_value)	查询分区内位于当前行下方第 offset 行的值。
NTILE 函数	NTILE(n)	将各个窗口分区内的数据行有序且均匀得分成多组，并返回数据行所在的组号，从 1 开始。
NTH_VALUE 函数	NTH_VALUE(KEY, offset)	返回各个窗口分区内第 offset 行的 KEY 值。
PERCENT_RANK 函数	PERCENT_RANK()	计算各个窗口分区内各个行的相对位置，以百分比形式表示。
RANK 函数	RANK()	计算各个窗口分区内各个数据行的排名。同一个分区内相同值会获得相同的排名，下一个值的排名将跳过这些重复的排名值，
ROW_NUMBER 函数	ROW_NUMBER()	计算各个窗口分区内各个数据行的排名。每个值具有唯一的序号，从 1 开始。

CUME_DIST 函数

CUME_DIST 函数用于计算当前行在其所属窗口分区内的累积分布比例，计算公式为窗口分区内当前行及其之前的所有行占窗口内总行数的比例。

函数语法

语法格式

CUME_DIST() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

参数说明

参数	说明
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。

返回值说明
返回值为 Double 类型。

函数示例

场景
根据请求方法进行窗口分区，并计算各个分区内各个请求的累积分布比例。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  CUME_DIST() OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime DESC
  ) AS cume

检索和分析结果

DENSE_RANK 函数

DENSE_RANK 函数用于对窗口分区内值的排名。

函数语法

语法格式

DENSE_RANK() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

参数说明

参数	说明
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。

返回值说明
返回值为 Bigint 类型。

函数示例

场景
根据请求方法进行窗口分区，并计算各个分区内各个请求的请求时间排序。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  DENSE_RANK() OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime DESC
  ) AS rank
ORDER BY
  Method,
  rank

检索和分析结果

FIRST_VALUE 函数

FIRST_VALUE 函数用于返回各个窗口分区内第一行的值。

函数语法

语法格式

FIRST_VALUE(KEY) OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame]
)

参数说明

参数	说明
KEY	日志字段、表达式，其值可以为任意数据类型。
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。
frame	窗口框架，用于进一步细化窗口的范围。例如 `RANGE BETWEEN unbounded preceding AND unbounded following` 表示当前窗口分区内的所有行。

返回值说明
返回值与 KEY 的数据类型一致。

函数示例

场景
根据请求方法进行窗口分区，并计算各个分区内最小的请求时间。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  FIRST_VALUE(ReqTime) OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime
  ) AS firstvalue

检索和分析结果

LAG 函数

LAG 函数用于查询各个窗口分区内位于当前行上方第 offset 行的值。

函数语法

语法格式

LAG(KEY, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame]
)

参数说明

参数	说明
KEY	日志字段、表达式，其值可以为任意数据类型。
offset	指定偏移量。如果 offset 为 0，则返回当前行的值。
default_value	指定默认值，如果指定的偏移行不存在值，则返回您所指定的默认值。
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。
frame	窗口框架。

返回值说明
返回值与 KEY 的数据类型一致。

函数示例

场景
计算每小时的请求数量以及一小时内的请求数量与前一小时的占比。

检索和分析语句

* |
SELECT
  hour,
  total,
  total * 1.00000 /(LAG(total, 1, 1) over(ORDER BY hour)) AS ratio 
FROM  (
    SELECT
      COUNT(Action) AS total,
      DATE_TRUNC('hour', __time__) AS hour
    GROUP BY
      hour
  )

检索和分析结果

LAST_VALUE 函数

FIRST_VALUE 函数用于返回各个窗口分区内最后一行的值。

函数语法

语法格式

LAST_VALUE(KEY) OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame]
)

参数说明

参数	说明
KEY	日志字段、表达式，其值可以为任意数据类型。
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。
frame	窗口框架，用于进一步细化窗口的范围。例如 `RANGE BETWEEN unbounded preceding AND unbounded following` 表示当前窗口分区内的所有行。如果不指定 frame，则表示窗口范围为当前窗口内第一行到当前行。

返回值说明
返回值与 KEY 的数据类型一致。

函数示例

场景
根据请求方法进行窗口分区，并计算各个分区内最大的请求时间。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  LAST_VALUE(ReqTime) OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime
    RANGE BETWEEN unbounded preceding AND unbounded following
  ) AS lastvalue

检索和分析结果

LEAD 函数

LEAD 函数用于查询各个窗口分区内位于当前行下方第 offset 行的值。

函数语法

语法格式

LEAD(KEY, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame]
)

参数说明

参数	说明
KEY	日志字段、表达式，其值可以为任意数据类型。
offset	指定偏移量。如果 offset 为 0，则返回当前行的值。
default_value	指定默认值，如果指定的偏移行不存在值，则返回您所指定的默认值。
partition by partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order by order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。
frame	窗口框架，用于进一步细化窗口的范围。例如 `RANGE BETWEEN unbounded preceding AND unbounded following` 表示当前窗口分区内的所有行。

返回值说明
返回值与 KEY 的数据类型一致。

函数示例

场景
计算每小时的请求数量以及一小时内的请求数量与后一小时的占比。

检索和分析语句

* |
SELECT
  hour,
  total,
  total * 1.00000 /(LEAD(total, 1, 1) OVER(ORDER BY hour)) AS ratio 
FROM  (
    SELECT
      COUNT(Action) AS total,
      DATE_TRUNC('hour', __time__) AS hour
    GROUP BY
      hour
  )

检索和分析结果

NTILE 函数

NTILE 函数用于将各个窗口分区内的数据行有序且均匀得分成多组，并返回数据行所在的组号，从 1 开始。

函数语法

语法格式

NTILE(n) OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

参数说明

参数	说明
n	指定分组的数量。 NTILE 函数会尽可能均匀地将分区内的数据行分为 n 组。
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。

返回值说明
返回值为 Bigint 类型。

函数示例

场景
根据请求方法进行窗口分区，并将分区内的数据行分为 3 组。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  NTILE(3) OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime
  ) AS ntile

检索和分析结果

NTH_VALUE 函数

NTH_VALUE 函数用于返回各个窗口分区内第 offset 行的 KEY 值。

函数语法

语法格式

NTH_VALUE(KEY, offset) OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame]
)

参数说明

参数	说明
KEY	日志字段、表达式，其值可以为任意数据类型。
offset	从窗口分区的第一行开始计数的偏移量。如果没有足够的行满足偏移量时，将返回 null。
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。
frame	窗口框架，用于进一步细化窗口的范围。例如 `RANGE BETWEEN unbounded preceding AND unbounded following` 表示当前窗口分区内的所有行。

返回值说明
返回值与 KEY 的数据类型一致。

函数示例

场景
根据请求方法进行窗口分区，并返回各个分区内请求时间第三高的请求 ID。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  NTH_VALUE(ReqID, 3) OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime DESC
    RANGE BETWEEN unbounded preceding AND unbounded following
  ) AS value

检索和分析结果

PERCENT_RANK 函数

PERCENT_RANK 函数用于计算各个窗口分区内各个行的相对位置，以百分比形式表示。计算公式为 (rank - 1) / (total_rows - 1) ，rank 为当前行的排名，total_rows 为当前窗口分区内的总行数。

函数语法

语法格式

PERCENT_RANK() OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

参数说明

参数	说明
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。

返回值说明
返回值为 Double 类型。

函数示例

场景
根据请求方法进行窗口分区，并返回各个分区内各个请求时间的相对排名（百分比形式）。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  PERCENT_RANK() OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime DESC
  ) AS timerank

检索和分析结果

RANK 函数

RANK 函数用于计算各个窗口分区内各个数据行的排名。同一个分区内相同值会获得相同的排名，下一个值的排名将跳过这些重复的排名值，例如两个相同值的排名都为 1，那么下一个值的排名将为 3。

函数语法

语法格式

RANK()  OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

参数说明

参数	说明
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。

返回值说明
返回值为 Bigint 类型。

函数示例

场景
根据请求方法进行窗口分区，并返回各个分区内请求时间的排名。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  RANK() OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime
  ) AS timerank

检索和分析结果

ROW_NUMBER 函数

ROW_NUMBER 函数用于计算各个窗口分区内各个数据行的排名。每个值具有唯一的序号，从 1 开始。

函数语法

语法格式

ROW_NUMBER() OVER(
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

参数说明

参数	说明
partition_expression	指定窗口的分区表达式，系统会根据分区表达式将数据分成不同的分区。
order_expression	指定窗口的排序表达式，系统会根据排序表达式对各个分区内的各行数据进行排序。

返回值说明
返回值为 Bigint 类型。

函数示例

场景
根据请求方法进行窗口分区，并返回各个分区内请求时间的排名。

检索和分析语句

* |
SELECT
  Method,
  ReqID,
  ReqTime,
  ROW_NUMBER() OVER(
    PARTITION BY Method
    ORDER BY
      ReqTime
  ) AS timerank

检索和分析结果