共用方式為

WINDOW 子句

  • 發行項

適用於：核取記號為「是」Databricks SQL 核取記號為「是」Databricks Runtime

window 子句可讓您定義並命名一次或多個相異的 window 規格，並在相同查詢內的許多 window 函式之間共用。

語法

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

Parameters

  • window_name

    透過 identifier 可以參考 window 規格。 identifier 在 WINDOW 子句內必須是唯一的。

  • window_spec

    window 規格 要跨一或多個 window 函式共用。

範例

> 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