Delen via

Vensterkaderclausule

Van toepassing op:aangevinkt als ja Databricks SQL aangevinkt als ja Databricks Runtime

Hiermee geeft u een verschuivende subset van rijen binnen de partitie waarop de aggregatie- of analytische vensterfunctie wordt toegepast.

Syntaxis

{ frame_mode frame_start |
  frame_mode BETWEEN frame_start AND frame_end } }

frame_mode
{ RANGE | ROWS }

frame_start
{ UNBOUNDED PRECEDING |
  offset_start PRECEDING |
  CURRENT ROW |
  offset_start FOLLOWING }

frame_end
{ offset_stop PRECEDING |
  CURRENT ROW |
  offset_stop FOLLOWING |
  UNBOUNDED FOLLOWING }

Parameters

  • frame_mode

    • RIJEN

      Indien opgegeven, wordt het kader van het schuifvenster uitgedrukt in rijen die voorafgaan aan of volgen op de huidige rij.

    • BEREIK

      Indien opgegeven, moet de vensterfunctie een ORDER BY clausule gebruiken met een enkele expressie obExpr.

      De grenzen van het schuifvenster worden vervolgens uitgedrukt als een verschuiving ten opzichte van de obExpr van de huidige rij.

  • frame_start

    De beginpositie van het schuifvensterframe ten opzichte van de huidige rij.

    • NIET-GEBONDEN VOORAFGAANDE

      Hiermee geeft u op dat het vensterframe begint aan het begin van de partitie.

    • offset_start VOORGAANDE

      Als de modus is ROWS, offset_start is het positieve letterlijke getal dat bepaalt hoeveel rijen vóór de huidige rij het frame begint.

      Als de modus is RANGE, offset_start is dit een positieve letterlijke waarde van een type die kan worden afgetrokken van obExpr. Het frame begint bij de eerste rij van de partitie waarvoor geldt dat obExpr groter dan of gelijk aan obExpr - offset_start is in de huidige rij.

    • HUIDIGE RIJ

      Hiermee geeft u op dat het frame begint bij de huidige rij.

    • offset_start VOLGENDE

      Als de modus ROWS is, is offset_start het positieve gehele getal dat bepaalt hoeveel rijen na de huidige rij het frame begint. Als de modus RANGE is, is offset_start een positieve letterlijke waarde van een type dat kan worden toegevoegd aan obExpr. Het frame begint bij de eerste rij van de partitie waarvoor geldt dat obExpr groter dan of gelijk aan obExpr + offset_start is in de huidige rij.

  • frame_stop

    Het einde van het schuifvensterkader ten opzichte van de huidige rij.

    Als dit niet is opgegeven, stopt het frame bij de HUIDIGE RIJ. Het einde van het schuifvenster moet groter zijn dan het begin van het raamkader.

    • offset_stop VOORGAANDE

      Als frame_mode ROWS is, dan is offset_stop het positieve gehele getal dat bepaalt hoeveel rijen vóór de huidige rij het frame eindigt. Als frame_mode is RANGE, offset_stop is dit een positieve letterlijke waarde van hetzelfde type als offset_start. Het frame eindigt op de laatste rij van de partitie waarvoor obExpr kleiner is dan of gelijk aan obExpr - offset_stop voor de huidige rij.

    • HUIDIGE RIJ

      Specificeert dat het frame stopt bij de huidige rij.

    • offset_stop VOLGENDE

      Als frame_mode ROWS is, is offset_stop het positieve gehele getal dat bepaalt hoeveel rijen na de huidige rij het frame eindigt. Als frame_mode is RANGE, offset_stop is dit een positieve letterlijke waarde van hetzelfde type als offset_start. Het frame eindigt op de laatste rij van de partitie waarvoor obExpr kleiner dan of gelijk aan obExpr + offset_stop is bij de huidige rij.

    • NIET-AFHANKELIJKE VOLGENDE

      Hiermee geeft u op dat het vensterframe stopt aan het einde van de partitie.

Examples

> 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);

-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: cumulative sum of salary within each department.
> SELECT name,
         dept,
         salary,
         SUM(salary) OVER (PARTITION BY dept ORDER BY salary
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
    FROM employees;
  Fred Engineering  21000  21000
 Chloe Engineering  23000  44000
   Tom Engineering  23000  67000
  Paul Engineering  29000  96000
  Jane   Marketing  29000  29000
  Jeff   Marketing  35000  64000
  Lisa       Sales  10000  10000
  Alex       Sales  30000  40000
  Evan       Sales  32000  72000

-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: moving average over three adjacent rows.
> SELECT name,
         dept,
         salary,
         ROUND(AVG(salary) OVER (PARTITION BY dept ORDER BY salary
                                 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS moving_avg
    FROM employees;
  Fred Engineering  21000  22000
 Chloe Engineering  23000  22333
   Tom Engineering  23000  25000
  Paul Engineering  29000  26000
  Jane   Marketing  29000  32000
  Jeff   Marketing  35000  32000
  Lisa       Sales  10000  20000
  Alex       Sales  30000  24000
  Evan       Sales  32000  31000

-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: sum from the current row to the end of the partition.
> SELECT name,
         dept,
         salary,
         SUM(salary) OVER (PARTITION BY dept ORDER BY salary
                           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
    FROM employees;
  Fred Engineering  21000  96000
 Chloe Engineering  23000  75000
   Tom Engineering  23000  52000
  Paul Engineering  29000  29000
  Jane   Marketing  29000  64000
  Jeff   Marketing  35000  35000
  Lisa       Sales  10000  72000
  Alex       Sales  30000  62000
  Evan       Sales  32000  32000

-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: cumulative sum using value-based range.
-- Unlike ROWS mode, RANGE groups rows with equal `ORDER BY` values together.
> SELECT name,
         dept,
         salary,
         SUM(salary) OVER (PARTITION BY dept ORDER BY salary
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
    FROM employees;
  Fred Engineering  21000  21000
 Chloe Engineering  23000  67000
   Tom Engineering  23000  67000
  Paul Engineering  29000  96000
  Jane   Marketing  29000  29000
  Jeff   Marketing  35000  64000
  Lisa       Sales  10000  10000
  Alex       Sales  30000  40000
  Evan       Sales  32000  72000

-- RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING: sum of salaries within +/- 5000 of the current row's salary.
> SELECT name,
         dept,
         salary,
         SUM(salary) OVER (PARTITION BY dept ORDER BY salary
                           RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING) AS nearby_total
    FROM employees;
  Fred Engineering  21000  67000
 Chloe Engineering  23000  67000
   Tom Engineering  23000  67000
  Paul Engineering  29000  75000
  Jane   Marketing  29000  64000
  Jeff   Marketing  35000  35000
  Lisa       Sales  10000  10000
  Alex       Sales  30000  92000
  Evan       Sales  32000  62000

-- Comparing ROWS vs RANGE: the difference is visible when there are duplicate `ORDER BY` values.
-- With ROWS, `Chloe` and `Tom` have different running totals because each row is counted individually.
-- With RANGE, `Chloe` and `Tom` have the same total because they share the same salary value.
> SELECT name,
         salary,
         SUM(salary) OVER (ORDER BY salary
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS rows_total,
         SUM(salary) OVER (ORDER BY salary
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
    FROM employees
    WHERE dept = 'Engineering';
  Fred  21000  21000  21000
 Chloe  23000  44000  67000
   Tom  23000  67000  67000
  Paul  29000  96000  96000
  • Last updated on