WINDOW clause

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 ¹

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

   frame_units:
     ROWS | RANGE | GROUPS
   

Spark has a rudimentary WINDOW support:

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

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