开窗函数

描述

对一组行进行操作的函数（称为开窗），并基于行组计算每行的返回值。 开窗函数可用于处理任务，如计算移动平均值、计算累积统计信息或访问给定当前行的相对位置的行值。

语法

function OVER { window_name | ( window_name ) | window_spec }

function
  { ranking_function | analytic_function | aggregate_function }

over_clause
  OVER { window_name | ( window_name ) | window_spec }

window_spec
  ( [ PARTITION BY partition [ , ... ] ] [ order_by ] [ window_frame ] )

参数

• function

  对窗口运行的函数。 不同种类的函数支持窗口规范的不同配置。

  • ranking_function

    任何排名窗口函数。
    如果指定，则 window_spec 必须包含 ORDER BY 子句，但不能包含 window_frame 子句。

  • analytic_function

    任何分析窗口函数。

  • aggregate_function

    任何聚合函数。
    如果指定，则该函数不得包含 FILTER 子句。

• window_name

  标识由查询定义的命名窗口规范。

• window_spec

  此子句定义如何在组中对行进行分组和排序，以及函数对分区中的哪些行运行。

  • partition

    一个或多个表达式，用于指定一个行组，该组定义函数在哪个范围运行。 如果未指定 PARTITION 子句，则分区将由所有行组成。

  • order_by

    ORDER BY 子句指定行在分区中的顺序。

  • window_frame

    window frame 子句指定分区中对其运行聚合或分析函数的行的滑动子集。

示例

CREATE TABLE employees
   (name STRING, dept STRING, salary INT, age INT);

INSERT INTO employees
   VALUES ('Lisa', 'Sales', 10000, 35),
          ('Evan', 'Sales', 32000, 38),
          ('Fred', 'Engineering', 21000, 28),
          ('Alex', 'Sales', 30000, 33),
          ('Tom', 'Engineering', 23000, 33),
          ('Jane', 'Marketing', 29000, 28),
          ('Jeff', 'Marketing', 35000, 38),
          ('Paul', 'Engineering', 29000, 23),
          ('Chloe', 'Engineering', 23000, 25);

SELECT name,
         dept,
         RANK() OVER (PARTITION BY dept ORDER BY salary) AS rank
  FROM employees;

+-------------+---------------+---------------+
|         name|           dept|           rank|
+-------------+---------------+---------------+
|         Fred|    Engineering|              1|
+-------------+---------------+---------------+
|          Tom|    Engineering|              2|
+-------------+---------------+---------------+
|        Chloe|    Engineering|              2|
+-------------+---------------+---------------+
|         Paul|    Engineering|              3|
+-------------+---------------+---------------+
|         Jane|      Marketing|              1|
+-------------+---------------+---------------+
|         Jeff|      Marketing|              2|
+-------------+---------------+---------------+
|         Lisa|          Sales|              1|
+-------------+---------------+---------------+
|         Alex|          Sales|              2|
+-------------+---------------+---------------+
|         Evan|          Sales|              3|
+-------------+---------------+---------------+



SELECT name,
         dept,
         age,
         CUME_DIST() OVER (PARTITION BY dept ORDER BY age
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume_dist
    FROM employees;

+-------------+---------------+-------------+--------------------+
|         name|           dept|          age|           cume_dist|
+-------------+---------------+-------------+--------------------+
|         Paul|    Engineering|           23|                0.25|
+-------------+---------------+-------------+--------------------+
|        Chloe|    Engineering|           25|                 0.5|
+-------------+---------------+-------------+--------------------+
|         Fred|    Engineering|           28|                0.75|
+-------------+---------------+-------------+--------------------+
|          Tom|    Engineering|           33|                   1|
+-------------+---------------+-------------+--------------------+
|         Jane|      Marketing|           28|                 0.5|
+-------------+---------------+-------------+--------------------+
|         Jeff|      Marketing|           38|                 1.0|
+-------------+---------------+-------------+--------------------+
|         ALex|          Sales|           33|  0.3333333333333333|
+-------------+---------------+-------------+--------------------+
|         Lisa|          Sales|           35|  0.6666666666666666|
+-------------+---------------+-------------+--------------------+
|         Evan|          Sales|           38|                 1.0|
+-------------+---------------+-------------+--------------------+

SELECT name,
         salary,
         LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag,
         LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
    FROM employees;

+-------------+---------------+-------------+---------------+
|         name|         salary|          lag|           lead|
+-------------+---------------+-------------+---------------+
|         Fred|          21000|         NULL|          23000|
+-------------+---------------+-------------+---------------+
|          Tom|          23000|        21000|          23000|
+-------------+---------------+-------------+---------------+
|        Chloe|          23000|        23000|          29000|
+-------------+---------------+-------------+---------------+
|         Paul|          29000|        23000|              0|
+-------------+---------------+-------------+---------------+
|         Jane|          29000|         NULL|          35000|
+-------------+---------------+-------------+---------------+
|         Jeff|          35000|        29000|              0|
+-------------+---------------+-------------+---------------+
|         Lisa|          10000|         NULL|          30000|
+-------------+---------------+-------------+---------------+
|         Alex|          30000|        10000|          32000|
+-------------+---------------+-------------+---------------+
|         Evan|          32000|        30000|              0|
+-------------+---------------+-------------+---------------+

相关文档

SELECT