Vensterfuncties (Databricks SQL)

Functies die worden gebruikt voor een groep rijen, aangeduid als een venster, en een retourwaarde berekenen voor elke rij op basis van de groep rijen. Vensterfuncties zijn handig voor het verwerken van taken, zoals het berekenen van een bewegend gemiddelde, het berekenen van een cumulatieve statistiek of het openen van de waarde van rijen op basis van de relatieve positie van de huidige rij.

Syntaxis

function OVER { window_name | ( window_name ) | window_spec }

function:
{ ranking_function | analytic_function | aggregate_function }

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

Parameters

  • Functie

    De functie die in het venster wordt gebruikt. Verschillende klassen van functies ondersteunen verschillende configuraties van vensterspecificaties.

  • window_spec

    Deze component definieert hoe de rijen worden gegroepeerd, gesorteerd binnen de groep en op welke rijen binnen een partitie een functie werkt.

    • partitie

      Een of meer expressies die worden gebruikt om een groep rijen op te geven die het bereik definiƫren waarop de functie werkt. Als er geen PARTITION-component is opgegeven, bestaat de partitie uit alle rijen.

    • order_by

      De ORDER BY-component geeft de volgorde van rijen binnen een partitie aan.

    • window_frame

      De vensterframeclausule geeft een sliding subset van rijen aan binnen de partitie waarop de aggregatie- of analysefunctie wordt gebruikt.

U kunt SORTEREN OP opgeven als alias voor ORDER BY.

U kunt ook CLUSTER BY of DISTRIBUTE BY opgeven als alias voor PARTITION BY.

Voorbeelden

> 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, salary, age FROM employees;
 Chloe Engineering 23000   25
  Fred Engineering 21000   28
  Paul Engineering 29000   23
 Helen   Marketing 29000   40
   Tom Engineering 23000   33
  Jane   Marketing 29000   28
  Jeff   Marketing 35000   38
  Evan       Sales 32000   38
  Lisa       Sales 10000   35
  Alex       Sales 30000   33

> SELECT name,
         dept,
         RANK() OVER (PARTITION BY dept ORDER BY salary) AS rank
  FROM employees;
  Lisa       Sales  10000    1
  Alex       Sales  30000    2
  Evan       Sales  32000    3
  Fred Engineering  21000    1
   Tom Engineering  23000    2
 Chloe Engineering  23000    2
  Paul Engineering  29000    4
 Helen   Marketing  29000    1
  Jane   Marketing  29000    1
  Jeff   Marketing  35000    3

> SELECT name,
         dept,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dense_rank
    FROM employees;
  Lisa       Sales  10000          1
  Alex       Sales  30000          2
  Evan       Sales  32000          3
  Fred Engineering  21000          1
   Tom Engineering  23000          2
 Chloe Engineering  23000          2
  Paul Engineering  29000          3
 Helen   Marketing  29000          1
  Jane   Marketing  29000          1
  Jeff   Marketing  35000          2

> 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;
  Alex       Sales     33 0.3333333333333333
  Lisa       Sales     35 0.6666666666666666
  Evan       Sales     38                1.0
  Paul Engineering     23               0.25
 Chloe Engineering     25               0.75
  Fred Engineering     28               0.25
   Tom Engineering     33                1.0
  Jane   Marketing     28 0.3333333333333333
  Jeff   Marketing     38 0.6666666666666666
 Helen   Marketing     40                1.0

> SELECT name,
         dept,
         salary,
         MIN(salary) OVER (PARTITION BY dept ORDER BY salary) AS min
    FROM employees;
  Lisa       Sales  10000 10000
  Alex       Sales  30000 10000
  Evan       Sales  32000 10000
 Helen   Marketing  29000 29000
  Jane   Marketing  29000 29000
  Jeff   Marketing  35000 29000
  Fred Engineering  21000 21000
   Tom Engineering  23000 21000
 Chloe Engineering  23000 21000
  Paul Engineering  29000 21000

> 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;
  Lisa       Sales  10000 NULL  30000
  Alex       Sales  30000 10000 32000
  Evan       Sales  32000 30000     0
  Fred Engineering  21000  NULL 23000
 Chloe Engineering  23000 21000 23000
   Tom Engineering  23000 23000 29000
  Paul Engineering  29000 23000     0
 Helen   Marketing  29000  NULL 29000
  Jane   Marketing  29000 29000 35000
  Jeff   Marketing  35000 29000     0