Klausa tentang bingkai jendela

Berlaku untuk: Databricks SQL Databricks Runtime

Menentukan subset baris geser dalam partisi tempat fungsi jendela agregat atau analitik beroperasi.

Sintaks

{ 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 }

Parameter

• frame_mode

  • ROWS

    Jika ditentukan, frame jendela geser diukur berdasarkan baris sebelumnya atau baris yang mengikuti baris saat ini.

  • kisaran

    Jika ditentukan, fungsi jendela harus menentukan klausa ORDER BY dengan ekspresi tunggal obExpr.

    Batas jendela geser kemudian dinyatakan sebagai offset dari obExpr untuk baris saat ini.

    Jika klausul ORDER BY hilang, Azure Databricks menaikkan DATATYPE_MISMATCH. RANGE_FRAME_WITHOUT_ORDER. Jika klausa ORDER BY memiliki beberapa ekspresi, Azure Databricks menaikkan DATATYPE_MISMATCH. RANGE_FRAME_MULTI_ORDER.

• frame_start

  Posisi awal bingkai jendela geser relatif terhadap baris saat ini.

  • TANPA BATAS SEBELUMNYA

    Menentukan bahwa bingkai jendela dimulai di awal partisi.

  • awal_offset MENDAHULUI

    Jika modenya berupa ROWS, offset_start merupakan bilangan bulat positif yang menentukan jumlah baris sebelum baris saat ini dimulai.

    Jika modenya berupa RANGE, offset_start merupakan nilai harfiah positif dari jenis yang dapat dikurangkan dari obExpr. Bingkai dimulai pada baris pertama partisi di mana obExpr lebih besar atau sama dengan obExpr - offset_start pada baris saat ini.

  • BARIS SAAT INI

    Menentukan bahwa bingkai dimulai pada baris saat ini.

  • offset_mulai MENGIKUTI

    Jika modenya berupa ROWS, offset_start merupakan bilangan harfiah integral positif yang menentukan jumlah baris yang melewati baris tempat bingkai dimulai. Jika mode berupa RANGE, offset_start merupakan nilai harfiah positif jenis yang dapat ditambahkan ke obExpr. Bingkai dimulai pada baris pertama partisi di mana obExpr lebih besar atau sama dengan obExpr + offset_start pada baris saat ini.

• frame_stop

  Ujung dari bingkai jendela geser relatif terhadap baris saat ini.

  Jika tidak ditentukan, bingkai akan berhenti di CURRENT ROW. Akhir jendela geser harus lebih besar dari awal bingkai jendela.

  • offset_stop SEBELUMNYA

    Jika frame_mode berupa ROWS, offset_stop merupakan bilangan harfiah integral positif yang menentukan jumlah baris sebelum baris saat ini di mana bingkai berhenti. Jika frame_mode berupa RANGE, offset_stop merupakan nilai harfiah positif jenis yang sama dengan offset_start. Bingkai berakhir pada baris terakhir dari partisi yang obExpr nya kurang dari atau sama dengan obExpr - offset_stop pada baris saat ini.

  • BARIS SAAT INI

    Menentukan bahwa bingkai berhenti di baris saat ini.

  • offset_stop BERIKUT

    Jika frame_mode berupa ROWS, offset_stop merupakan bilangan harfiah positif yang menentukan jumlah baris setelah baris saat ini di mana bingkai berakhir. Jika frame_mode berupa RANGE, offset_stop merupakan nilai harfiah positif jenis yang sama dengan offset_start. Bingkai berakhir pada baris terakhir partisi yang obExpr nya kurang dari atau sama dengan obExpr + offset_stop pada baris saat ini.

  • Mengikuti Tanpa Batas

    Menentukan bahwa bingkai jendela berhenti di akhir partisi.

Kondisi kesalahan umum

• DATATYPE_MISMATCH. RANGE_FRAME_INVALID_TYPE
• DATATYPE_MISMATCH. RANGE_FRAME_MULTI_ORDER
• DATATYPE_MISMATCH. RANGE_FRAME_WITHOUT_ORDER
• DATATYPE_MISMATCH. SPECIFIED_WINDOW_FRAME_INVALID_BOUND
• DATATYPE_MISMATCH. SPECIFIED_WINDOW_FRAME_UNACCEPTED_TYPE
• DATATYPE_MISMATCH. SPECIFIED_WINDOW_FRAME_WRONG_COMPARISON
• WINDOW_FUNCTION_AND_FRAME_MISMATCH

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

-- `RANGE` frame requires exactly one `ORDER BY` expression.
> SELECT SUM(salary) OVER (ORDER BY salary, age
                           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING)
    FROM employees;
  Error: DATATYPE_MISMATCH

-- `RANGE` frame requires an `ORDER BY` clause.
> SELECT SUM(salary) OVER (RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING)
    FROM employees;
  Error: DATATYPE_MISMATCH

Artikel terkait

• fungsi Jendela
• ORDER BY klausa