Changeset: 8d3673565de3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8d3673565de3
Modified Files:
sql/test/analytics/Tests/analytics00.sql
sql/test/analytics/Tests/analytics00.stable.out
sql/test/analytics/Tests/analytics01.sql
sql/test/analytics/Tests/analytics01.stable.out
Branch: analytics
Log Message:
Approved tests for lag and lead window functions.
diffs (truncated from 1358 to 300 lines):
diff --git a/sql/test/analytics/Tests/analytics00.sql
b/sql/test/analytics/Tests/analytics00.sql
--- a/sql/test/analytics/Tests/analytics00.sql
+++ b/sql/test/analytics/Tests/analytics00.sql
@@ -73,6 +73,7 @@ select cast(prod(aa) over () as bigint)
select avg(aa) over () from analytics;
select count(aa) over () from analytics;
select count(*) over () from analytics;
+--select count(*) over (); FIXME this crashes :(
create table stressme (aa varchar(64), bb int);
insert into stressme values ('one', 1), ('another', 1), ('stress', 1), (NULL,
2), ('ok', 2), ('check', 3), ('me', 3), ('please', 3), (NULL, 4);
@@ -97,6 +98,40 @@ select count(*) over (partition by bb or
select count(*) over (partition by bb order by bb desc) from stressme;
select count(*) over (order by bb desc) from stressme;
+select min(bb) over (partition by aa) from stressme;
+select min(bb) over (partition by aa order by aa asc) from stressme;
+select min(bb) over (partition by aa order by aa desc) from stressme;
+select min(bb) over (order by aa desc) from stressme;
+
+select max(bb) over (partition by aa) from stressme;
+select max(bb) over (partition by aa order by aa asc) from stressme;
+select max(bb) over (partition by aa order by aa desc) from stressme;
+select max(bb) over (order by aa desc) from stressme;
+
+select cast(sum(bb) over (partition by aa) as bigint) from analytics;
+select cast(sum(bb) over (partition by aa order by aa asc) as bigint) from
analytics;
+select cast(sum(bb) over (partition by aa order by aa desc) as bigint) from
analytics;
+select cast(sum(bb) over (order by aa desc) as bigint) from analytics;
+
+select cast(prod(bb) over (partition by aa) as bigint) from analytics;
+select cast(prod(bb) over (partition by aa order by aa asc) as bigint) from
analytics;
+select cast(prod(bb) over (partition by aa order by aa desc) as bigint) from
analytics;
+
+select avg(bb) over (partition by aa) from analytics;
+select avg(bb) over (partition by aa order by aa asc) from analytics;
+select avg(bb) over (partition by aa order by aa desc) from analytics;
+select avg(bb) over (order by aa desc) from analytics;
+
+select count(bb) over (partition by aa) from stressme;
+select count(bb) over (partition by aa order by aa asc) from stressme;
+select count(bb) over (partition by aa order by aa desc) from stressme;
+select count(bb) over (order by aa desc) from stressme;
+
+select count(*) over (partition by aa) from stressme;
+select count(*) over (partition by aa order by aa asc) from stressme;
+select count(*) over (partition by aa order by aa desc) from stressme;
+select count(*) over (order by aa desc) from stressme;
+
create table debugme (aa real, bb int);
insert into debugme values (15, 3), (3, 1), (2, 1), (5, 3), (NULL, 2), (3, 2),
(4, 1), (6, 3), (8, 2), (NULL, 4);
diff --git a/sql/test/analytics/Tests/analytics00.stable.out
b/sql/test/analytics/Tests/analytics00.stable.out
--- a/sql/test/analytics/Tests/analytics00.stable.out
+++ b/sql/test/analytics/Tests/analytics00.stable.out
@@ -1165,6 +1165,395 @@ Ready.
[ 9 ]
[ 9 ]
[ 9 ]
+#select min(bb) over (partition by aa) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 2 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 1 ]
+#select min(bb) over (partition by aa order by aa asc) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 2 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 1 ]
+#select min(bb) over (partition by aa order by aa desc) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 3 ]
+[ 1 ]
+[ 2 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 2 ]
+[ 2 ]
+#select min(bb) over (order by aa desc) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select max(bb) over (partition by aa) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 4 ]
+[ 4 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 1 ]
+#select max(bb) over (partition by aa order by aa asc) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 4 ]
+[ 4 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 1 ]
+#select max(bb) over (partition by aa order by aa desc) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 3 ]
+[ 1 ]
+[ 2 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 4 ]
+[ 4 ]
+#select max(bb) over (order by aa desc) from stressme;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+#select cast(sum(bb) over (partition by aa) as bigint) from analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 1 # length
+[ 6 ]
+[ 6 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 3 ]
+#select cast(sum(bb) over (partition by aa order by aa asc) as bigint) from
analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 1 # length
+[ 6 ]
+[ 6 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 3 ]
+#select cast(sum(bb) over (partition by aa order by aa desc) as bigint) from
analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 1 # length
+[ 3 ]
+[ 2 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 6 ]
+[ 6 ]
+#select cast(sum(bb) over (order by aa desc) as bigint) from analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 2 # length
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+[ 22 ]
+#select cast(prod(bb) over (partition by aa) as bigint) from analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 1 # length
+[ 8 ]
+[ 8 ]
+[ 1 ]
+[ 2 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 3 ]
+#select cast(prod(bb) over (partition by aa order by aa asc) as bigint) from
analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 1 # length
+[ 8 ]
+[ 8 ]
+[ 1 ]
+[ 2 ]
+[ 2 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 3 ]
+#select cast(prod(bb) over (partition by aa order by aa desc) as bigint) from
analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 1 # length
+[ 3 ]
+[ 2 ]
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 2 ]
+[ 2 ]
+[ 1 ]
+[ 8 ]
+[ 8 ]
+#select avg(bb) over (partition by aa) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 1.5 ]
+[ 1.5 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 2 ]
+[ 3 ]
+#select avg(bb) over (partition by aa order by aa asc) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 3 ]
+[ 3 ]
+[ 1 ]
+[ 1.5 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
