Failing SQL Feature
PostgreSQL GROUPS frame mode used inside a window function's frame specification.
PostgreSQL supports three frame_mode options on window function frames: RANGE, ROWS, and GROUPS. The GROUPS mode (added in PostgreSQL 11) defines the frame in terms of peer groups — sets of rows that are equivalent according to
the window's ORDER BY clause — rather than individual rows or value ranges. It is valid SQL-standard syntax in PostgreSQL and appears in analytical queries that need to aggregate over a fixed number of distinct ordered groups.
See the PostgreSQL docs for details: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
SQL Example
CREATE TABLE events (
id SERIAL PRIMARY KEY,
user_id INT,
ts INT,
value INT
);
INSERT INTO events (user_id, ts, value) VALUES
(1, 1, 10),
(1, 1, 20),
(1, 2, 30),
(1, 3, 40),
(1, 3, 50),
(1, 4, 60);
SELECT
id,
ts,
value,
SUM(value) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS sum_last_2_groups
FROM events
ORDER BY ts, id;
Parsing error
ParseException: Encountered: <S_IDENTIFIER> / "GROUPS", at line 7, column 9, in lexical state DEFAULT.
Failing SQL Feature
PostgreSQL
GROUPSframe mode used inside a window function's frame specification.PostgreSQL supports three
frame_modeoptions on window function frames:RANGE,ROWS, andGROUPS. TheGROUPSmode (added in PostgreSQL 11) defines the frame in terms of peer groups — sets of rows that are equivalent according tothe window's
ORDER BYclause — rather than individual rows or value ranges. It is valid SQL-standard syntax in PostgreSQL and appears in analytical queries that need to aggregate over a fixed number of distinct ordered groups.See the PostgreSQL docs for details: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
SQL Example
Parsing error
ParseException: Encountered: <S_IDENTIFIER> / "GROUPS", at line 7, column 9, in lexical state DEFAULT.