WINDOW 子句

  • 项目

适用于：check marked yes Databricks SQL check marked yes Databricks Runtime

使用 window 子句可以一次性定义一个或多个不同的窗口规范并为其命名，并在同一查询中的多个窗口函数之间共享它们。

语法

WINDOW { window_name AS window_spec } [, ...]

参数

  • window_name

    一个标识符，可通过它引用窗口规范。 标识符在 WINDOW 子句中必须是唯一的。

  • window_spec

    要跨一个或多个窗口函数共享的窗口规范。

示例

> 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 round(avg(age) OVER win, 1) AS salary,
         round(avg(salary) OVER win, 1) AS avgsalary,
         min(salary) OVER win AS minsalary,
         max(salary) OVER win AS maxsalary,
         count(1) OVER win AS numEmps
    FROM employees
    WINDOW win AS (ORDER BY age
                   ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
25.3    27000.0 23000   29000   3
26.0    25500.0 21000   29000   4
27.4    25000.0 21000   29000   5
29.4    25200.0 21000   30000   5
31.4    22600.0 10000   30000   5
33.4    23800.0 10000   35000   5
35.4    26000.0 10000   35000   5
36.0    26750.0 10000   35000   4
37.0    25666.7 10000   35000   3