Implement window functions

[MichaelScofield]

A lot of useful window functions are not implemented now.

Check how DataFusion supports defining these "User Defined Window Function" and implement them.

[MichaelScofield]

related: apache/datafusion#4553

[killme2008]

Looks like we already support the time window functions? @MichaelScofield

[MichaelScofield]

@killme2008 last time I checked, window function in datafusion was lack of the filter clause support (see https://www.sqlite.org/windowfunctions.html). Will check it now.

[MichaelScofield]

@killme2008 Unfortunately, datafusion still lack the support for "filter clause on window function". However, the filter could be partially substituted by plain "where" clause. See below the experiment I ran:

First create table and insert data:

CREATE TABLE hello(a bigint, b varchar(10), primary key(b));
INSERT INTO hello VALUES(1, 'one'), (1, 'two'), (1, 'three'), (2, 'four'), (3, 'five'), (3, 'six');

Run query with filter:

SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a);

and using plain "where" to do filter:

SELECT a, b, sum(a) OVER win AS sum FROM hello where a != 1 WINDOW win AS (ORDER BY a);

Please note the difference between the output. Some rows are missing if using plain "where" to do the filter but the window function calculation is correct:

sqlite

sqlite> SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a);
1|one|
1|two|
1|three|
2|four|2
3|five|8
3|six|8
sqlite> SELECT a, b, sum(a) OVER win AS sum FROM hello where a != 1 WINDOW win AS (ORDER BY a);
2|four|2
3|five|8
3|six|8

pg

luofucong=# SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a);
 a |   b   | sum
---+-------+-----
 1 | one   |
 1 | two   |
 1 | three |
 2 | four  |   2
 3 | five  |   8
 3 | six   |   8
(6 rows)

luofucong=# SELECT a, b, sum(a) OVER win AS sum FROM hello where a != 1 WINDOW win AS (ORDER BY a);
 a |  b   | sum
---+------+-----
 2 | four |   2
 3 | five |   8
 3 | six  |   8
(3 rows)

mysql

mysql> SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a)' at line 1
mysql> SELECT a, b, sum(a) OVER win AS sum FROM hello where a != 1 WINDOW win AS (ORDER BY a);
+------+------+------+
| a    | b    | sum  |
+------+------+------+
|    2 | four |    2 |
|    3 | five |    8 |
|    3 | six  |    8 |
+------+------+------+
3 rows in set (0.00 sec)

greptimedb

mysql> SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a);
ERROR 1815 (HY000): Failed to execute query: SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a), source: Failed to parse SQL, source: SQL statement is not supported: SELECT a, b, sum(a) filter ( where a != 1 ) OVER win AS sum FROM hello WINDOW win AS (ORDER BY a), keyword: (
mysql> SELECT a, b, sum(a) OVER win AS sum FROM hello where a != 1 WINDOW win AS (ORDER BY a);
+------+------+------+
| a    | b    | sum  |
+------+------+------+
|    2 | four |    2 |
|    3 | five |    8 |
|    3 | six  |    8 |
+------+------+------+
3 rows in set (0.02 sec)

Since the datafusion does support other window function features but this "filter clause", I'm claiming this issue is done.