WINDOW clause

SQL standard defines the following syntax for WINDOW clause:

WINDOW { identifier AS "(" window_specification ")" } ["," ...]

window_specification:
  [identifier]
  [PARTITION BY { column [COLLATE collation] } ["," ...]]
  [ORDER BY sort_specification_list]
  [frame_definition]

frame_definition:
  frame_units {frame_start | frame_between} [frame_exclusion]

frame_units:
  ROWS | RANGE

frame_start:
  UNBOUNDED PRECEDING | CURRENT ROW | unsigned_value PRECEDING

frame_between:
  BETWEEN frame_bound AND frame_bound

frame_bound:
  frame_start | UNBOUNDED FOLLOWING | unsigned_value FOLLOWING

frame_exclusion:
    EXCLUDE CURRENT ROW
  | EXCLUDE GROUP
  | EXCLUDE TIES
  | EXCLUDE NO OTHERS

No dialect supports COLLATE in PARTITION BY. Other than that, the following dialects support everything else:

• BigQuery
• Hive
• MySQL
• Transact-SQL
• N1QL ¹
• PostgreSQL ¹
• SQLite ¹
• Trino ^{1, 2}

1. These dialects support an extra GROUPS option in frame_units:

   frame_units:
     ROWS | RANGE | GROUPS
   

2. Trino supports an additional pattern matching syntax around frame_definition:

    trino_frame_definition:
      [MEASURES measure_definition ["," ...]]
      frame_definition
      [AFTER MATCH skip_to]
      [INITIAL | SEEK]
      [PATTERN "(" row_pattern ")"]
      [SUBSET subset_definition ["," ...]]
      [DEFINE variable_definition ["," ...]]
   

   Trino does not support frame_exclusion.

Spark has a rudimentary WINDOW support:

WINDOW identifier ["," WINDOW identifier ...]

DB2, MariaDB, PL/SQL, Redshift, SingleStoreDB don't support WINDOW clause. Though they do support window functions.