Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
Postgres is a good reference implementation for standard SQL. We have some queries for windowed aggregation and their output against Postgres. The output is below. We need a mechanism to add tests in this format. Either the usual sql script + reference log, or an interpreter that can execute the output somehow.
```
foodmart=# select *, first_value(deptno) over () from emp;
ename | deptno | gender | first_value
------------------+------------
Jane | 10 | F | 10
Bob | 10 | M | 10
Eric | 20 | M | 10
Susan | 30 | F | 10
Alice | 30 | F | 10
Adam | 50 | M | 10
Eve | 50 | F | 10
Grace | 60 | F | 10
(8 rows)
foodmart=# select *, first_value(ename) over () from emp;
ename | deptno | gender | first_value
------------------+------------
Jane | 10 | F | Jane
Bob | 10 | M | Jane
Eric | 20 | M | Jane
Susan | 30 | F | Jane
Alice | 30 | F | Jane
Adam | 50 | M | Jane
Eve | 50 | F | Jane
Grace | 60 | F | Jane
(8 rows)
foodmart=# select *, first_value(ename) over (partition by deptno) from emp;
ename | deptno | gender | first_value
------------------+------------
Jane | 10 | F | Jane
Bob | 10 | M | Jane
Eric | 20 | M | Eric
Susan | 30 | F | Susan
Alice | 30 | F | Susan
Adam | 50 | M | Adam
Eve | 50 | F | Adam
Grace | 60 | F | Grace
(8 rows)
foodmart=# select *, first_value(ename) over (partition by deptno range current row) from emp;
ename | deptno | gender | first_value
------------------+------------
Jane | 10 | F | Jane
Bob | 10 | M | Jane
Eric | 20 | M | Eric
Susan | 30 | F | Susan
Alice | 30 | F | Susan
Adam | 50 | M | Adam
Eve | 50 | F | Adam
Grace | 60 | F | Grace
(8 rows)
foodmart=# select *, first_value(ename) over (partition by deptno range unbounded preceding) from emp;
ename | deptno | gender | first_value
------------------+------------
Jane | 10 | F | Jane
Bob | 10 | M | Jane
Eric | 20 | M | Eric
Susan | 30 | F | Susan
Alice | 30 | F | Susan
Adam | 50 | M | Adam
Eve | 50 | F | Adam
Grace | 60 | F | Grace
(8 rows)
foodmart=# select *, first_value(ename) over (partition by deptno order by gender range unbounded preceding) from emp;
ename | deptno | gender | first_value
------------------+------------
Jane | 10 | F | Jane
Bob | 10 | M | Jane
Eric | 20 | M | Eric
Alice | 30 | F | Alice
Susan | 30 | F | Alice
Eve | 50 | F | Eve
Adam | 50 | M | Eve
Grace | 60 | F | Grace
(8 rows)
foodmart=# select *, count
over (order by deptno) from emp;
ename | deptno | gender | count
------------------+------
Jane | 10 | F | 2
Bob | 10 | M | 2
Eric | 20 | M | 3
Susan | 30 | F | 5
Alice | 30 | F | 5
Adam | 50 | M | 7
Eve | 50 | F | 7
Grace | 60 | F | 8
(8 rows)
foodmart=# select *, count over (order by deptno), first_value(ename) over (order by deptno rows 2 following) from emp;
ERROR: frame starting from following row cannot end with current row
LINE 1: ...o), first_value(ename) over (order by deptno rows 2 followin...
```
---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/288
Created by: julianhyde
Labels:
Created at: Thu May 22 09:19:22 CEST 2014
State: closed