alamb commented on issue #15714:
URL: https://github.com/apache/datafusion/issues/15714#issuecomment-5151056351
I updated the title of this ticket to describe the gap in feature (rather
than this is a bug)
I think the core limitation today in DataFusion is that the window size must
be a constant (either rows or expression). Window sizes that are expressions
themselves are not supported.
As an example of what is supported today in DataFusion (54)
```sql
> SELECT
id, ts, val,
-- ROWS: fixed count of physical rows (1 preceding + current)
SUM(val) OVER (
ORDER BY ts, id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
-- RANGE: value-based offset on the ORDER BY column (1 day window)
SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
) AS range_frame,
-- GROUPS: counts distinct peer groups (tied ORDER BY values) as one unit
SUM(val) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM t
ORDER BY ts, id;
+----+---------------------+-----+------------+-------------+--------------+
| id | ts | val | rows_frame | range_frame | groups_frame |
+----+---------------------+-----+------------+-------------+--------------+
| 1 | 2024-01-01T00:00:00 | 10 | 10 | 30 | 30 |
| 2 | 2024-01-01T00:00:00 | 20 | 30 | 30 | 30 |
| 3 | 2024-01-02T00:00:00 | 30 | 50 | 60 | 60 |
| 4 | 2024-01-04T00:00:00 | 40 | 70 | 40 | 70 |
| 5 | 2024-01-05T00:00:00 | 50 | 90 | 90 | 90 |
+----+---------------------+-----+------------+-------------+--------------+
5 row(s) fetched.
Elapsed 0.002 seconds.
```
Here are examples of queries with arbitrary expressions that don't work
```sql
> -- FAILS: bound is an *expression* (spec's `expr PRECEDING`), issue
#15714
SELECT val, SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN (INTERVAL '1' DAY + INTERVAL '1' DAY) PRECEDING AND
CURRENT ROW
) FROM t;
Error during planning: Invalid window frame: frame offsets for RANGE must be
either a numeric value, a string value or an interval
```
```sql
> -- FAILS: same problem for ROWS with an integer expression
SELECT val, SUM(val) OVER (
ORDER BY ts
ROWS BETWEEN (1 + 1) PRECEDING AND CURRENT ROW
) FROM t;
Error during planning: Invalid window frame: frame offsets for ROWS / GROUPS
must be non negative integers
```
<details><summary>SQL Source</summary>
<p>
```sql
CREATE TABLE t (id INT, ts TIMESTAMP, val INT) AS VALUES
(1, TIMESTAMP '2024-01-01 00:00:00', 10),
(2, TIMESTAMP '2024-01-01 00:00:00', 20), -- same ts as row 1 -> tied
peer group
(3, TIMESTAMP '2024-01-02 00:00:00', 30),
(4, TIMESTAMP '2024-01-04 00:00:00', 40), -- gap of 2 days from previous
(5, TIMESTAMP '2024-01-05 00:00:00', 50);
SELECT
id, ts, val,
-- ROWS: fixed count of physical rows (1 preceding + current)
SUM(val) OVER (
ORDER BY ts, id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
-- RANGE: value-based offset on the ORDER BY column (1 day window)
SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
) AS range_frame,
-- GROUPS: counts distinct peer groups (tied ORDER BY values) as one unit
SUM(val) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM t
ORDER BY ts, id;
```
This is the smae as DuckDB
```sql
memory D CREATE TABLE t AS
SELECT * FROM (VALUES
(1, TIMESTAMP '2024-01-01 00:00:00', 10),
(2, TIMESTAMP '2024-01-01 00:00:00', 20), -- same ts as row
1 -> tied peer group
(3, TIMESTAMP '2024-01-02 00:00:00', 30),
(4, TIMESTAMP '2024-01-04 00:00:00', 40), -- gap of 2 days
from previous
(5, TIMESTAMP '2024-01-05 00:00:00', 50)
) AS t(id, ts, val);
memory D SELECT
id, ts, val,
-- ROWS: fixed count of physical rows (1 preceding + current)
SUM(val) OVER (
ORDER BY ts, id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
-- RANGE: value-based offset on the ORDER BY column (1 day
window)
SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
) AS range_frame,
-- GROUPS: counts distinct peer groups (tied ORDER BY values)
as one unit
SUM(val) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM t
ORDER BY ts, id;
┌───────┬─────────────────────┬───────┬────────────┬─────────────┬──────────────┐
│ id │ ts │ val │ rows_frame │ range_frame │
groups_frame │
│ int32 │ timestamp │ int32 │ int128 │ int128 │ int128
│
├───────┼─────────────────────┼───────┼────────────┼─────────────┼──────────────┤
│ 1 │ 2024-01-01 00:00:00 │ 10 │ 10 │ 30 │
30 │
│ 2 │ 2024-01-01 00:00:00 │ 20 │ 30 │ 30 │
30 │
│ 3 │ 2024-01-02 00:00:00 │ 30 │ 50 │ 60 │
60 │
│ 4 │ 2024-01-04 00:00:00 │ 40 │ 70 │ 40 │
70 │
│ 5 │ 2024-01-05 00:00:00 │ 50 │ 90 │ 90 │
90 │
└───────┴─────────────────────┴───────┴────────────┴─────────────┴──────────────┘
```
</p>
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
