Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: add full support for window frames #27100

Closed
yuzefovich opened this issue Jul 2, 2018 · 1 comment · Fixed by #28262
Closed

sql: add full support for window frames #27100

yuzefovich opened this issue Jul 2, 2018 · 1 comment · Fixed by #28262
Assignees
@yuzefovich
Labels
A-sql-semantics C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@yuzefovich
Copy link
Member

yuzefovich commented Jul 2, 2018 •
edited
Loading

PG release 11 plans to "support all options shown in the SQL:2011 standard", so we should do the same.

Main additions are support of GROUPS mode of framing, optional frame_exclusion clause, and optional FILTER. See https://www.postgresql.org/docs/11/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for reference.
@yuzefovich yuzefovich self-assigned this Jul 2, 2018
@yuzefovich yuzefovich changed the title Add support for GROUPS mode of framing Add full support for window frames Jul 2, 2018
@knz knz added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Jul 5, 2018
@knz knz changed the title Add full support for window frames sql: add full support for window frames Jul 5, 2018
@yuzefovich yuzefovich mentioned this issue Jul 19, 2018
Closed
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Jul 30, 2018
@yuzefovich
sql: use Datums for offsets of window frames
85ab8bc 
Adds support for storing non-integer offsets
of window frames necessary for RANGE mode.
Also checks that there is ordering on a single
column that window function is computed over
if in RANGE mode.

Incremental change towards: cockroachdb#27100.

Release note: None
@yuzefovich yuzefovich mentioned this issue Jul 30, 2018
Merged
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Jul 31, 2018
@yuzefovich
sql: use Datums for offsets of window frames
6f93fec 
Adds support for storing non-integer offsets
of window frames necessary for RANGE mode.
Also checks that there is ordering on a single
column that window function is computed over
if in RANGE mode.

Incremental change towards: cockroachdb#27100.

Release note: None
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Aug 1, 2018
@yuzefovich
sql: use Datums for offsets of window frames
7deffdd 
Adds support for storing non-integer offsets
of window frames necessary for RANGE mode.
Also checks that there is ordering on a single
column that window function is computed over
if in RANGE mode.

Incremental change towards: cockroachdb#27100.

Release note: None
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Aug 2, 2018
@yuzefovich
sql: use Datums for offsets of window frames
0e6590c 
Adds support for storing non-integer offsets
of window frames necessary for RANGE mode.
Also checks that there is ordering on a single
column that window function is computed over
if in RANGE mode.

Incremental change towards: cockroachdb#27100.

Release note: None
craig bot pushed a commit that referenced this issue Aug 2, 2018
@yuzefovich
Merge #28067
20d94d2 
28067: sql: use Datums for offsets of window frames r=yuzefovich a=yuzefovich

Adds support for storing non-integer offsets
of window frames necessary for RANGE mode.
Also checks that there is ordering on a single
column that window function is computed over
if in RANGE mode.

Incremental change towards: #27100.

Release note: None

Co-authored-by: yuzefovich <[email protected]>
@yuzefovich yuzefovich mentioned this issue Aug 3, 2018
Merged
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Aug 15, 2018
@yuzefovich
sql: add support for GROUPS mode of window framing
054cb58 
Adds support for GROUPS mode of window framing that
allows specifying the frame in terms of peer groups
which is like a mix of ROWS and RANGE introduced in
SQL:2011.

Peer groups are all rows not distinct in the ordering
columns. Let's go through an example: suppose we have
a table with the schema `(product_id INT PRIMARY KEY,
price INT)` with 6 rows: (1, 1), (2, 1), (3, 1), (4, 2),
(5, 3), (6, 3). If a window function has `ORDER BY price`,
we will have three peer groups (1: rows 1, 2, 3; 2: row 4;
3: rows 5, 6). GROUPS mode allows us specify the frame
in terms of the number of these peer groups. For example,
with `GROUPS BETWEEN 2 PRECEDING AND 1 FOLLOWING`, the
frame of row 3 will contain rows 1-4 (there are no
preceding peer groups), and the frame of row 4 will
contain rows 1-6. Other combinations are also possible:
`GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING` produces
an empty frame, `GROUPS BETWEEN CURRENT ROW AND CURRENT
ROW` will include only rows of the current row's peer
group.

Incremental change towards: cockroachdb#27100.

Release note (sql change): CockroachDB now supports
GROUPS mode of window frame specification.
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Aug 15, 2018
@yuzefovich
sql: add support for GROUPS mode of window framing
5451828 
Adds support for GROUPS mode of window framing that
allows specifying the frame in terms of peer groups
which is like a mix of ROWS and RANGE introduced in
SQL:2011.

Peer groups are all rows not distinct in the ordering
columns. Let's go through an example: suppose we have
a table with the schema `(product_id INT PRIMARY KEY,
price INT)` with 6 rows: (1, 1), (2, 1), (3, 1), (4, 2),
(5, 3), (6, 3). If a window function has `ORDER BY price`,
we will have three peer groups (1: rows 1, 2, 3; 2: row 4;
3: rows 5, 6). GROUPS mode allows us specify the frame
in terms of the number of these peer groups. For example,
with `GROUPS BETWEEN 2 PRECEDING AND 1 FOLLOWING`, the
frame of row 3 will contain rows 1-4 (there are no
preceding peer groups), and the frame of row 4 will
contain rows 1-6. Other combinations are also possible:
`GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING` produces
an empty frame, `GROUPS BETWEEN CURRENT ROW AND CURRENT
ROW` will include only rows of the current row's peer
group.

Incremental change towards: cockroachdb#27100.

Release note (sql change): CockroachDB now supports
GROUPS mode of window frame specification.
knz pushed a commit to yuzefovich/cockroach that referenced this issue Aug 16, 2018
@yuzefovich @knz
sql: add support for GROUPS mode of window framing
09609b3 
Adds support for GROUPS mode of window framing that
allows specifying the frame in terms of peer groups
which is like a mix of ROWS and RANGE introduced in
SQL:2011.

Peer groups are all rows not distinct in the ordering
columns. Let's go through an example: suppose we have
a table with the schema `(product_id INT PRIMARY KEY,
price INT)` with 6 rows: (1, 1), (2, 1), (3, 1), (4, 2),
(5, 3), (6, 3). If a window function has `ORDER BY price`,
we will have three peer groups (1: rows 1, 2, 3; 2: row 4;
3: rows 5, 6). GROUPS mode allows us specify the frame
in terms of the number of these peer groups. For example,
with `GROUPS BETWEEN 2 PRECEDING AND 1 FOLLOWING`, the
frame of row 3 will contain rows 1-4 (there are no
preceding peer groups), and the frame of row 4 will
contain rows 1-6. Other combinations are also possible:
`GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING` produces
an empty frame, `GROUPS BETWEEN CURRENT ROW AND CURRENT
ROW` will include only rows of the current row's peer
group.

Incremental change towards: cockroachdb#27100.

Release note (sql change): CockroachDB now supports
GROUPS mode of window frame specification.
craig bot pushed a commit that referenced this issue Aug 16, 2018
@yuzefovich
Merge #28244
8299fe8 
28244: sql: Adds support for GROUPS mode of window framing r=knz a=yuzefovich

Adds support for GROUPS mode of window framing that
allows specifying the frame in terms of peer groups
which is like a mix of ROWS and RANGE introduced in
SQL:2011.

Peer groups are all rows not distinct in the ordering
columns. Let's go through an example: suppose we have
a table with the schema `(product_id INT PRIMARY KEY,
price INT)`
with 6 rows: (1, 1), (2, 1), (3, 1), (4, 2),
(5, 3), (6, 3). If a window function has `ORDER BY price`,
we will have three peer groups (1: rows 1, 2, 3; 2: row 4;
3: rows 5, 6). GROUPS mode allows us specify the frame
in terms of the number of these peer groups. For example,
with `GROUPS BETWEEN 2 PRECEDING AND 1 FOLLOWING`, the
frame of row 3 will contain rows 1-4 (there are no
preceding peer groups), and the frame of row 4 will
contain rows 1-6. Other combinations are also possible:
`GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING` produces
an empty frame, `GROUPS BETWEEN CURRENT ROW AND CURRENT
ROW`
will include only rows of the current row's peer
group.

Incremental change towards: #27100.

Release note (sql change): CockroachDB now supports
GROUPS mode of window frame specification.

Co-authored-by: yuzefovich <[email protected]>
yuzefovich added a commit to yuzefovich/cockroach that referenced this issue Aug 16, 2018
@yuzefovich
sql: Add support for optional frame exclusion
3da0a45 
Adds support for optional frame_exclusion clause
of window frames.

Incremental change towards: cockroachdb#27100.

Release note (sql change): CockroachDB now supports
optional frame exclusion clause of window frames.
@yuzefovich
Copy link
Member Author

I will close this issue once #28262 is merged since all things I planned to implement with this issue will have been done. For a request for additional support of the specification, please open a new issue.

@craig craig bot closed this as completed in 79d97a7 Aug 19, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@yuzefovich yuzefovich

Labels
A-sql-semantics C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: add support for optional frame exclusion yuzefovich/cockroach
2 participants
@knz @yuzefovich