WINDOW clause

SQL standard defines the following syntax for WINDOW clause:

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

and the following syntax for window functions:

function_call OVER { window_name | "(" window_specification ")" }

where window_specification in both is as follows:

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:

	WINDOW clause	Window function
BigQuery	✔️	✔️
DB2		✔️
DB2i		✔️
Hive	✔️	✔️
MySQL	✔️	✔️
MariaDB		✔️
N1QL1	✔️	✔️
PL/SQL		✔️
PostgreSQL1	✔️	✔️
Redshift		✔️
SingleStoreDB		✔️
Snowflake		✔️
Spark	✔️	✔️
SQLite1	✔️	✔️
TiDB	✔️	✔️
Trino1, 2	✔️	✔️
Transact-SQL	✔️	✔️

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.