You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by zt...@apache.org on 2016/08/09 02:19:44 UTC
[2/6] incubator-hawq git commit: HAWQ-987. Add feature test for agg
derived win.
HAWQ-987. Add feature test for agg derived win.
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/e0a072b0
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/e0a072b0
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/e0a072b0
Branch: refs/heads/master
Commit: e0a072b0c7cf95f8d3d151c96ada53181485a7c0
Parents: d73117e
Author: ztao1987 <zh...@gmail.com>
Authored: Tue Aug 9 07:02:01 2016 +0800
Committer: ztao1987 <zh...@gmail.com>
Committed: Tue Aug 9 10:19:24 2016 +0800
----------------------------------------------------------------------
src/test/feature/query/ans/agg-derived-win.ans | 342 ++++++++++++++++++++
src/test/feature/query/sql/agg-derived-win.sql | 161 +++++++++
src/test/feature/query/test_aggregate.cpp | 6 +-
src/test/regress/expected/agg_derived_win.out | 322 ------------------
src/test/regress/known_good_schedule | 1 -
src/test/regress/sql/agg_derived_win.sql | 161 ---------
6 files changed, 508 insertions(+), 485 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/feature/query/ans/agg-derived-win.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/query/ans/agg-derived-win.ans b/src/test/feature/query/ans/agg-derived-win.ans
new file mode 100644
index 0000000..271250b
--- /dev/null
+++ b/src/test/feature/query/ans/agg-derived-win.ans
@@ -0,0 +1,342 @@
+-- start_ignore
+SET SEARCH_PATH=TestAggregate_TestAggregateDerivedWin;
+SET
+-- end_ignore
+-- Objective: test aggregate derived window functions in HAWQ.
+-- Aggregate derived window functions are nothing but user defined
+-- aggregates when used with "OVER()" clause. Refer to GPSQL-1418.
+-- Begin EMA (copied from attachment to MPP-14845)
+--
+-- Definition of ema -- exponential moving average
+--
+-- Given a sequence of numbers
+-- V = (V_0, V_1, ... , V_n-1)
+-- and a real number smoothing factor
+-- X such that 0 < X <= 1
+-- then
+-- ema(V) = E = (E_0, E_1, ... , E_n-1)
+-- and
+-- E_0 = V_0
+--
+-- E_i = E_i-1 * (1-X) + V_i * X
+-- = E_i-1 + (V_i - E_i-1) * X
+--
+-- Here the sequence V is represented by table ema_test ordered by k.
+drop type if exists ema_type cascade;
+psql:/tmp/TestAggregate_TestAggregateDerivedWin.sql:26: NOTICE: type "ema_type" does not exist, skipping
+DROP TYPE
+drop table if exists ema_test cascade;
+psql:/tmp/TestAggregate_TestAggregateDerivedWin.sql:27: NOTICE: table "ema_test" does not exist, skipping
+DROP TABLE
+create type ema_type as (x float, e float);
+CREATE TYPE
+create function ema_adv(t ema_type, v float, x float)
+ returns ema_type
+ as $$
+ begin
+ if t.e is null then
+ t.e = v;
+ t.x = x;
+ else
+ if t.x != x then
+ raise exception 'ema smoothing x may not vary';
+ end if;
+ t.e = t.e + (v - t.e) * t.x;
+ end if;
+ return t;
+ end;
+ $$ language plpgsql;
+CREATE FUNCTION
+create function ema_fin(t ema_type)
+ returns float
+ as $$
+ begin
+ return t.e;
+ end;
+ $$ language plpgsql;
+CREATE FUNCTION
+-- Work around for MPP-14845: define a placebo prefunc. This should
+-- never be called.
+create function ema_pre(s1 ema_type, s2 ema_type)
+ returns ema_type
+ as $$
+ select '(,)'::ema_type;
+ $$ language sql;
+CREATE FUNCTION
+create aggregate ema(float, float) (
+ sfunc = ema_adv,
+ stype = ema_type,
+ finalfunc = ema_fin,
+ prefunc = ema_pre,
+ initcond = '(,)'
+ );
+CREATE AGGREGATE
+create table ema_test
+ ( k int, v float )
+ distributed by (k);
+CREATE TABLE
+insert into ema_test
+ select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5)))
+ from generate_series(0,19) i(i);
+INSERT 0 20
+select
+ k, v,
+ ema(v, 0.9) over (order by k rows between unbounded preceding and current row)
+from ema_test
+order by k;
+ k | v | ema
+----+------------------+------------------
+ 0 | 32.5714285714286 | 32.5714285714286
+ 1 | 32.533375552346 | 32.5371808542543
+ 2 | 32.4195061015507 | 32.431273576821
+ 3 | 32.2306868343193 | 32.2507455085694
+ 4 | 31.9683547792877 | 31.9965938522158
+ 5 | 31.6345064417951 | 31.6707151828371
+ 6 | 31.231682609273 | 31.2755858666294
+ 7 | 30.7629490143185 | 30.8142126995496
+ 8 | 30.2318730026184 | 30.2901069723115
+ 9 | 29.642496383294 | 29.7072574421958
+ 10 | 28.999304668294 | 29.0700999456841
+ 11 | 28.307192934939 | 28.3834836360135
+ 12 | 27.5714285714286 | 27.6526340778871
+ 13 | 26.7976111888356 | 26.8831134777407
+ 14 | 25.9916300046853 | 26.0807783519908
+ 15 | 25.1596190224538 | 25.2517349554075
+ 16 | 24.3079103480979 | 24.4022928088288
+ 17 | 23.4429859989052 | 23.5389166798975
+ 18 | 22.5714285714286 | 22.6681773822755
+ 19 | 21.699871143952 | 21.7967017677843
+(20 rows)
+
+select
+ k, v,
+ ema(v, 0.9) over (order by k)
+from ema_test
+order by k;
+ k | v | ema
+----+------------------+------------------
+ 0 | 32.5714285714286 | 32.5714285714286
+ 1 | 32.533375552346 | 32.5371808542543
+ 2 | 32.4195061015507 | 32.431273576821
+ 3 | 32.2306868343193 | 32.2507455085694
+ 4 | 31.9683547792877 | 31.9965938522158
+ 5 | 31.6345064417951 | 31.6707151828371
+ 6 | 31.231682609273 | 31.2755858666294
+ 7 | 30.7629490143185 | 30.8142126995496
+ 8 | 30.2318730026184 | 30.2901069723115
+ 9 | 29.642496383294 | 29.7072574421958
+ 10 | 28.999304668294 | 29.0700999456841
+ 11 | 28.307192934939 | 28.3834836360135
+ 12 | 27.5714285714286 | 27.6526340778871
+ 13 | 26.7976111888356 | 26.8831134777407
+ 14 | 25.9916300046853 | 26.0807783519908
+ 15 | 25.1596190224538 | 25.2517349554075
+ 16 | 24.3079103480979 | 24.4022928088288
+ 17 | 23.4429859989052 | 23.5389166798975
+ 18 | 22.5714285714286 | 22.6681773822755
+ 19 | 21.699871143952 | 21.7967017677843
+(20 rows)
+
+-- End EMA (MPP-14845)
+--
+-- Aggregate derived equivalent of "lag()" window function.
+--
+create function mylag_transfn(st int[], val int, lag int)
+ returns int[]
+ as $$
+ declare
+ local_st int[] := st;
+ local_lag int := lag;
+ begin
+ if local_st is null then
+ local_st := '{}'::int[];
+ while local_lag >= 0
+ loop
+ select array_append(local_st, null::int) into local_st;
+ local_lag := local_lag - 1;
+ end loop;
+ end if;
+ return array_append(local_st[2:lag+1], val);
+ end;
+ $$ language plpgsql;
+CREATE FUNCTION
+create function mylag_finalfn(st int[])
+ returns int
+ as $$
+ begin
+ return st[1];
+ end;
+ $$ language plpgsql;
+CREATE FUNCTION
+create function mylag_prefn(st1 int[], st2 int[])
+ returns int[]
+ as $$
+ select '{}'::int[];
+ $$ language sql;
+CREATE FUNCTION
+create aggregate mylag(int, int) (
+ sfunc = mylag_transfn,
+ stype = int[],
+ finalfunc = mylag_finalfn,
+ prefunc = mylag_prefn,
+ initcond = '{null,null}'
+ );
+CREATE AGGREGATE
+-- This will be executed only on master, not on segments.
+select i, mylag(i, 2) over (order by i) from generate_series(1,10)i;
+ i | mylag
+----+-------
+ 1 |
+ 2 | 1
+ 3 | 2
+ 4 | 3
+ 5 | 4
+ 6 | 5
+ 7 | 6
+ 8 | 7
+ 9 | 8
+ 10 | 9
+(10 rows)
+
+create table t1 (a int, b int) distributed by (a);
+CREATE TABLE
+insert into t1 select i%3, 22*i/7 from generate_series(0,10)i;
+INSERT 0 11
+select a,b,mylag(b,1) over (order by b) from t1;
+ a | b | mylag
+---+----+-------
+ 0 | 0 |
+ 1 | 3 | 0
+ 2 | 6 | 3
+ 0 | 9 | 6
+ 1 | 12 | 9
+ 2 | 15 | 12
+ 0 | 18 | 15
+ 1 | 22 | 18
+ 2 | 25 | 22
+ 0 | 28 | 25
+ 1 | 31 | 28
+(11 rows)
+
+--
+-- Misc tests - cover different ways of defining a window.
+--
+CREATE AGGREGATE mysum (int) (
+ STYPE = bigint,
+ SFUNC = int4_sum,
+ prefunc = int8pl
+);
+CREATE AGGREGATE
+SELECT a,b,mysum(b) over (order by b) FROM t1;
+ a | b | mysum
+---+----+-------
+ 0 | 0 | 0
+ 1 | 3 | 3
+ 2 | 6 | 9
+ 0 | 9 | 18
+ 1 | 12 | 30
+ 2 | 15 | 45
+ 0 | 18 | 63
+ 1 | 22 | 85
+ 2 | 25 | 110
+ 0 | 28 | 138
+ 1 | 31 | 169
+(11 rows)
+
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3
+ a | b | mysum
+---+----+-------
+ 0 | 0 | 169
+ 2 | 6 | 169
+ 0 | 9 | 169
+ 2 | 15 | 169
+ 0 | 18 | 169
+ 2 | 25 | 169
+ 0 | 28 | 169
+ 1 | 3 | 169
+ 1 | 12 | 169
+ 1 | 22 | 169
+ 1 | 31 | 169
+(11 rows)
+
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3
+ a | b | mysum
+---+----+-------
+ 0 | 28 | 55
+ 0 | 9 | 55
+ 0 | 18 | 55
+ 0 | 0 | 55
+ 2 | 25 | 46
+ 2 | 15 | 46
+ 2 | 6 | 46
+ 1 | 3 | 68
+ 1 | 12 | 68
+ 1 | 22 | 68
+ 1 | 31 | 68
+(11 rows)
+
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3
+ a | b | mysum
+---+----+-------
+ 0 | 0 | 0
+ 1 | 3 | 3
+ 2 | 6 | 9
+ 0 | 9 | 18
+ 1 | 12 | 30
+ 2 | 15 | 45
+ 0 | 18 | 63
+ 1 | 22 | 85
+ 2 | 25 | 110
+ 0 | 28 | 138
+ 1 | 31 | 169
+(11 rows)
+
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3
+ a | b | mysum
+---+----+-------
+ 1 | 3 | 3
+ 1 | 12 | 15
+ 1 | 22 | 37
+ 1 | 31 | 68
+ 0 | 0 | 0
+ 0 | 9 | 9
+ 0 | 18 | 27
+ 0 | 28 | 55
+ 2 | 6 | 6
+ 2 | 15 | 21
+ 2 | 25 | 46
+(11 rows)
+
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as
+ (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row) order by a,b; -- mvd 1,2->3
+ a | b | mysum
+---+----+-------
+ 0 | 0 | 0
+ 0 | 9 | 9
+ 0 | 18 | 27
+ 0 | 28 | 46
+ 1 | 3 | 3
+ 1 | 12 | 15
+ 1 | 22 | 34
+ 1 | 31 | 53
+ 2 | 6 | 6
+ 2 | 15 | 21
+ 2 | 25 | 40
+(11 rows)
+
+select a,b,mylag(b, 1) over (partition by a order by b) from t1 order by a,b; -- mvd 1,2->3
+ a | b | mylag
+---+----+-------
+ 0 | 0 |
+ 0 | 9 | 0
+ 0 | 18 | 9
+ 0 | 28 | 18
+ 1 | 3 |
+ 1 | 12 | 3
+ 1 | 22 | 12
+ 1 | 31 | 22
+ 2 | 6 |
+ 2 | 15 | 6
+ 2 | 25 | 15
+(11 rows)
+
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/feature/query/sql/agg-derived-win.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/query/sql/agg-derived-win.sql b/src/test/feature/query/sql/agg-derived-win.sql
new file mode 100644
index 0000000..68f61cf
--- /dev/null
+++ b/src/test/feature/query/sql/agg-derived-win.sql
@@ -0,0 +1,161 @@
+-- Objective: test aggregate derived window functions in HAWQ.
+-- Aggregate derived window functions are nothing but user defined
+-- aggregates when used with "OVER()" clause. Refer to GPSQL-1418.
+
+-- Begin EMA (copied from attachment to MPP-14845)
+--
+-- Definition of ema -- exponential moving average
+--
+-- Given a sequence of numbers
+-- V = (V_0, V_1, ... , V_n-1)
+-- and a real number smoothing factor
+-- X such that 0 < X <= 1
+-- then
+-- ema(V) = E = (E_0, E_1, ... , E_n-1)
+-- and
+-- E_0 = V_0
+--
+-- E_i = E_i-1 * (1-X) + V_i * X
+-- = E_i-1 + (V_i - E_i-1) * X
+--
+-- Here the sequence V is represented by table ema_test ordered by k.
+
+drop type if exists ema_type cascade;
+drop table if exists ema_test cascade;
+
+create type ema_type as (x float, e float);
+
+create function ema_adv(t ema_type, v float, x float)
+ returns ema_type
+ as $$
+ begin
+ if t.e is null then
+ t.e = v;
+ t.x = x;
+ else
+ if t.x != x then
+ raise exception 'ema smoothing x may not vary';
+ end if;
+ t.e = t.e + (v - t.e) * t.x;
+ end if;
+ return t;
+ end;
+ $$ language plpgsql;
+
+create function ema_fin(t ema_type)
+ returns float
+ as $$
+ begin
+ return t.e;
+ end;
+ $$ language plpgsql;
+
+-- Work around for MPP-14845: define a placebo prefunc. This should
+-- never be called.
+create function ema_pre(s1 ema_type, s2 ema_type)
+ returns ema_type
+ as $$
+ select '(,)'::ema_type;
+ $$ language sql;
+
+create aggregate ema(float, float) (
+ sfunc = ema_adv,
+ stype = ema_type,
+ finalfunc = ema_fin,
+ prefunc = ema_pre,
+ initcond = '(,)'
+ );
+
+create table ema_test
+ ( k int, v float )
+ distributed by (k);
+
+insert into ema_test
+ select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5)))
+ from generate_series(0,19) i(i);
+
+select
+ k, v,
+ ema(v, 0.9) over (order by k rows between unbounded preceding and current row)
+from ema_test
+order by k;
+
+select
+ k, v,
+ ema(v, 0.9) over (order by k)
+from ema_test
+order by k;
+
+-- End EMA (MPP-14845)
+
+--
+-- Aggregate derived equivalent of "lag()" window function.
+--
+create function mylag_transfn(st int[], val int, lag int)
+ returns int[]
+ as $$
+ declare
+ local_st int[] := st;
+ local_lag int := lag;
+ begin
+ if local_st is null then
+ local_st := '{}'::int[];
+ while local_lag >= 0
+ loop
+ select array_append(local_st, null::int) into local_st;
+ local_lag := local_lag - 1;
+ end loop;
+ end if;
+ return array_append(local_st[2:lag+1], val);
+ end;
+ $$ language plpgsql;
+
+create function mylag_finalfn(st int[])
+ returns int
+ as $$
+ begin
+ return st[1];
+ end;
+ $$ language plpgsql;
+
+create function mylag_prefn(st1 int[], st2 int[])
+ returns int[]
+ as $$
+ select '{}'::int[];
+ $$ language sql;
+
+create aggregate mylag(int, int) (
+ sfunc = mylag_transfn,
+ stype = int[],
+ finalfunc = mylag_finalfn,
+ prefunc = mylag_prefn,
+ initcond = '{null,null}'
+ );
+
+-- This will be executed only on master, not on segments.
+select i, mylag(i, 2) over (order by i) from generate_series(1,10)i;
+
+create table t1 (a int, b int) distributed by (a);
+
+insert into t1 select i%3, 22*i/7 from generate_series(0,10)i;
+
+select a,b,mylag(b,1) over (order by b) from t1;
+
+--
+-- Misc tests - cover different ways of defining a window.
+--
+CREATE AGGREGATE mysum (int) (
+ STYPE = bigint,
+ SFUNC = int4_sum,
+ prefunc = int8pl
+);
+SELECT a,b,mysum(b) over (order by b) FROM t1;
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3
+SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as
+ (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row) order by a,b; -- mvd 1,2->3
+
+select a,b,mylag(b, 1) over (partition by a order by b) from t1 order by a,b; -- mvd 1,2->3
+
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/feature/query/test_aggregate.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/query/test_aggregate.cpp b/src/test/feature/query/test_aggregate.cpp
index fedb8c2..589ff4f 100644
--- a/src/test/feature/query/test_aggregate.cpp
+++ b/src/test/feature/query/test_aggregate.cpp
@@ -72,4 +72,8 @@ TEST_F(TestAggregate, TestAggregateWithNull) {
"1||aa|\n0||WET|\n0|51||\n");
}
-
+TEST_F(TestAggregate, TestAggregateDerivedWin) {
+ hawq::test::SQLUtility util;
+ util.execSQLFile("query/sql/agg-derived-win.sql",
+ "query/ans/agg-derived-win.ans");
+}
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/regress/expected/agg_derived_win.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/agg_derived_win.out b/src/test/regress/expected/agg_derived_win.out
deleted file mode 100644
index ab950e6..0000000
--- a/src/test/regress/expected/agg_derived_win.out
+++ /dev/null
@@ -1,322 +0,0 @@
--- Objective: test aggregate derived window functions in HAWQ.
--- Aggregate derived window functions are nothing but user defined
--- aggregates when used with "OVER()" clause. Refer to GPSQL-1418.
--- Begin EMA (copied from attachment to MPP-14845)
---
--- Definition of ema -- exponential moving average
---
--- Given a sequence of numbers
--- V = (V_0, V_1, ... , V_n-1)
--- and a real number smoothing factor
--- X such that 0 < X <= 1
--- then
--- ema(V) = E = (E_0, E_1, ... , E_n-1)
--- and
--- E_0 = V_0
---
--- E_i = E_i-1 * (1-X) + V_i * X
--- = E_i-1 + (V_i - E_i-1) * X
---
--- Here the sequence V is represented by table ema_test ordered by k.
-drop type if exists ema_type cascade;
-NOTICE: type "ema_type" does not exist, skipping
-drop table if exists ema_test cascade;
-NOTICE: table "ema_test" does not exist, skipping
-create type ema_type as (x float, e float);
-create function ema_adv(t ema_type, v float, x float)
- returns ema_type
- as $$
- begin
- if t.e is null then
- t.e = v;
- t.x = x;
- else
- if t.x != x then
- raise exception 'ema smoothing x may not vary';
- end if;
- t.e = t.e + (v - t.e) * t.x;
- end if;
- return t;
- end;
- $$ language plpgsql;
-create function ema_fin(t ema_type)
- returns float
- as $$
- begin
- return t.e;
- end;
- $$ language plpgsql;
--- Work around for MPP-14845: define a placebo prefunc. This should
--- never be called.
-create function ema_pre(s1 ema_type, s2 ema_type)
- returns ema_type
- as $$
- select '(,)'::ema_type;
- $$ language sql;
-create aggregate ema(float, float) (
- sfunc = ema_adv,
- stype = ema_type,
- finalfunc = ema_fin,
- prefunc = ema_pre,
- initcond = '(,)'
- );
-create table ema_test
- ( k int, v float )
- distributed by (k);
-insert into ema_test
- select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5)))
- from generate_series(0,19) i(i);
-select
- k, v,
- ema(v, 0.9) over (order by k rows between unbounded preceding and current row)
-from ema_test
-order by k;
- k | v | ema
-----+------------------+------------------
- 0 | 32.5714285714286 | 32.5714285714286
- 1 | 32.533375552346 | 32.5371808542543
- 2 | 32.4195061015507 | 32.431273576821
- 3 | 32.2306868343193 | 32.2507455085694
- 4 | 31.9683547792877 | 31.9965938522158
- 5 | 31.6345064417951 | 31.6707151828371
- 6 | 31.231682609273 | 31.2755858666294
- 7 | 30.7629490143185 | 30.8142126995496
- 8 | 30.2318730026184 | 30.2901069723115
- 9 | 29.642496383294 | 29.7072574421958
- 10 | 28.999304668294 | 29.0700999456841
- 11 | 28.307192934939 | 28.3834836360135
- 12 | 27.5714285714286 | 27.6526340778871
- 13 | 26.7976111888356 | 26.8831134777407
- 14 | 25.9916300046853 | 26.0807783519908
- 15 | 25.1596190224538 | 25.2517349554075
- 16 | 24.3079103480979 | 24.4022928088288
- 17 | 23.4429859989052 | 23.5389166798975
- 18 | 22.5714285714286 | 22.6681773822755
- 19 | 21.699871143952 | 21.7967017677843
-(20 rows)
-
-select
- k, v,
- ema(v, 0.9) over (order by k)
-from ema_test
-order by k;
- k | v | ema
-----+------------------+------------------
- 0 | 32.5714285714286 | 32.5714285714286
- 1 | 32.533375552346 | 32.5371808542543
- 2 | 32.4195061015507 | 32.431273576821
- 3 | 32.2306868343193 | 32.2507455085694
- 4 | 31.9683547792877 | 31.9965938522158
- 5 | 31.6345064417951 | 31.6707151828371
- 6 | 31.231682609273 | 31.2755858666294
- 7 | 30.7629490143185 | 30.8142126995496
- 8 | 30.2318730026184 | 30.2901069723115
- 9 | 29.642496383294 | 29.7072574421958
- 10 | 28.999304668294 | 29.0700999456841
- 11 | 28.307192934939 | 28.3834836360135
- 12 | 27.5714285714286 | 27.6526340778871
- 13 | 26.7976111888356 | 26.8831134777407
- 14 | 25.9916300046853 | 26.0807783519908
- 15 | 25.1596190224538 | 25.2517349554075
- 16 | 24.3079103480979 | 24.4022928088288
- 17 | 23.4429859989052 | 23.5389166798975
- 18 | 22.5714285714286 | 22.6681773822755
- 19 | 21.699871143952 | 21.7967017677843
-(20 rows)
-
--- End EMA (MPP-14845)
---
--- Aggregate derived equivalent of "lag()" window function.
---
-create function mylag_transfn(st int[], val int, lag int)
- returns int[]
- as $$
- declare
- local_st int[] := st;
- local_lag int := lag;
- begin
- if local_st is null then
- local_st := '{}'::int[];
- while local_lag >= 0
- loop
- select array_append(local_st, null::int) into local_st;
- local_lag := local_lag - 1;
- end loop;
- end if;
- return array_append(local_st[2:lag+1], val);
- end;
- $$ language plpgsql;
-create function mylag_finalfn(st int[])
- returns int
- as $$
- begin
- return st[1];
- end;
- $$ language plpgsql;
-create function mylag_prefn(st1 int[], st2 int[])
- returns int[]
- as $$
- select '{}'::int[];
- $$ language sql;
-create aggregate mylag(int, int) (
- sfunc = mylag_transfn,
- stype = int[],
- finalfunc = mylag_finalfn,
- prefunc = mylag_prefn,
- initcond = '{null,null}'
- );
--- This will be executed only on master, not on segments.
-select i, mylag(i, 2) over (order by i) from generate_series(1,10)i;
- i | mylag
-----+-------
- 1 |
- 2 | 1
- 3 | 2
- 4 | 3
- 5 | 4
- 6 | 5
- 7 | 6
- 8 | 7
- 9 | 8
- 10 | 9
-(10 rows)
-
-create table t1 (a int, b int) distributed by (a);
-insert into t1 select i%3, 22*i/7 from generate_series(0,10)i;
-select a,b,mylag(b,1) over (order by b) from t1;
- a | b | mylag
----+----+-------
- 0 | 0 |
- 1 | 3 | 0
- 2 | 6 | 3
- 0 | 9 | 6
- 1 | 12 | 9
- 2 | 15 | 12
- 0 | 18 | 15
- 1 | 22 | 18
- 2 | 25 | 22
- 0 | 28 | 25
- 1 | 31 | 28
-(11 rows)
-
---
--- Misc tests - cover different ways of defining a window.
---
-CREATE AGGREGATE mysum (int) (
- STYPE = bigint,
- SFUNC = int4_sum,
- prefunc = int8pl
-);
-SELECT a,b,mysum(b) over (order by b) FROM t1;
- a | b | mysum
----+----+-------
- 0 | 0 | 0
- 1 | 3 | 3
- 2 | 6 | 9
- 0 | 9 | 18
- 1 | 12 | 30
- 2 | 15 | 45
- 0 | 18 | 63
- 1 | 22 | 85
- 2 | 25 | 110
- 0 | 28 | 138
- 1 | 31 | 169
-(11 rows)
-
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3
- a | b | mysum
----+----+-------
- 0 | 0 | 169
- 2 | 6 | 169
- 0 | 9 | 169
- 2 | 15 | 169
- 0 | 18 | 169
- 2 | 25 | 169
- 0 | 28 | 169
- 1 | 3 | 169
- 1 | 12 | 169
- 1 | 22 | 169
- 1 | 31 | 169
-(11 rows)
-
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3
- a | b | mysum
----+----+-------
- 1 | 3 | 68
- 1 | 12 | 68
- 1 | 22 | 68
- 1 | 31 | 68
- 0 | 28 | 55
- 0 | 9 | 55
- 0 | 18 | 55
- 0 | 0 | 55
- 2 | 25 | 46
- 2 | 15 | 46
- 2 | 6 | 46
-(11 rows)
-
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3
- a | b | mysum
----+----+-------
- 0 | 0 | 0
- 1 | 3 | 3
- 2 | 6 | 9
- 0 | 9 | 18
- 1 | 12 | 30
- 2 | 15 | 45
- 0 | 18 | 63
- 1 | 22 | 85
- 2 | 25 | 110
- 0 | 28 | 138
- 1 | 31 | 169
-(11 rows)
-
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3
- a | b | mysum
----+----+-------
- 1 | 3 | 3
- 1 | 12 | 15
- 1 | 22 | 37
- 1 | 31 | 68
- 0 | 0 | 0
- 0 | 9 | 9
- 0 | 18 | 27
- 0 | 28 | 55
- 2 | 6 | 6
- 2 | 15 | 21
- 2 | 25 | 46
-(11 rows)
-
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as
- (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row); -- mvd 1,2->3
- a | b | mysum
----+----+-------
- 1 | 3 | 3
- 1 | 12 | 15
- 1 | 22 | 34
- 1 | 31 | 53
- 0 | 0 | 0
- 0 | 9 | 9
- 0 | 18 | 27
- 0 | 28 | 46
- 2 | 6 | 6
- 2 | 15 | 21
- 2 | 25 | 40
-(11 rows)
-
-select a,b,mylag(b, 1) over (partition by a order by b) from t1; -- mvd 1,2->3
- a | b | mylag
----+----+-------
- 0 | 0 |
- 0 | 9 | 0
- 0 | 18 | 9
- 0 | 28 | 18
- 2 | 6 |
- 2 | 15 | 6
- 2 | 25 | 15
- 1 | 3 |
- 1 | 12 | 3
- 1 | 22 | 12
- 1 | 31 | 22
-(11 rows)
-
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/regress/known_good_schedule
----------------------------------------------------------------------
diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule
index afd70ff..6c18613 100755
--- a/src/test/regress/known_good_schedule
+++ b/src/test/regress/known_good_schedule
@@ -170,7 +170,6 @@ ignore: percentile
ignore: resource_queue_function
ignore: gp_optimizer
ignore: co_nestloop_idxscan
-test: agg_derived_win
ignore: orca_udfs
#
# the following test needs some special filespace/tablespace.
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/regress/sql/agg_derived_win.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/agg_derived_win.sql b/src/test/regress/sql/agg_derived_win.sql
deleted file mode 100644
index 66b0b9d..0000000
--- a/src/test/regress/sql/agg_derived_win.sql
+++ /dev/null
@@ -1,161 +0,0 @@
--- Objective: test aggregate derived window functions in HAWQ.
--- Aggregate derived window functions are nothing but user defined
--- aggregates when used with "OVER()" clause. Refer to GPSQL-1418.
-
--- Begin EMA (copied from attachment to MPP-14845)
---
--- Definition of ema -- exponential moving average
---
--- Given a sequence of numbers
--- V = (V_0, V_1, ... , V_n-1)
--- and a real number smoothing factor
--- X such that 0 < X <= 1
--- then
--- ema(V) = E = (E_0, E_1, ... , E_n-1)
--- and
--- E_0 = V_0
---
--- E_i = E_i-1 * (1-X) + V_i * X
--- = E_i-1 + (V_i - E_i-1) * X
---
--- Here the sequence V is represented by table ema_test ordered by k.
-
-drop type if exists ema_type cascade;
-drop table if exists ema_test cascade;
-
-create type ema_type as (x float, e float);
-
-create function ema_adv(t ema_type, v float, x float)
- returns ema_type
- as $$
- begin
- if t.e is null then
- t.e = v;
- t.x = x;
- else
- if t.x != x then
- raise exception 'ema smoothing x may not vary';
- end if;
- t.e = t.e + (v - t.e) * t.x;
- end if;
- return t;
- end;
- $$ language plpgsql;
-
-create function ema_fin(t ema_type)
- returns float
- as $$
- begin
- return t.e;
- end;
- $$ language plpgsql;
-
--- Work around for MPP-14845: define a placebo prefunc. This should
--- never be called.
-create function ema_pre(s1 ema_type, s2 ema_type)
- returns ema_type
- as $$
- select '(,)'::ema_type;
- $$ language sql;
-
-create aggregate ema(float, float) (
- sfunc = ema_adv,
- stype = ema_type,
- finalfunc = ema_fin,
- prefunc = ema_pre,
- initcond = '(,)'
- );
-
-create table ema_test
- ( k int, v float )
- distributed by (k);
-
-insert into ema_test
- select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5)))
- from generate_series(0,19) i(i);
-
-select
- k, v,
- ema(v, 0.9) over (order by k rows between unbounded preceding and current row)
-from ema_test
-order by k;
-
-select
- k, v,
- ema(v, 0.9) over (order by k)
-from ema_test
-order by k;
-
--- End EMA (MPP-14845)
-
---
--- Aggregate derived equivalent of "lag()" window function.
---
-create function mylag_transfn(st int[], val int, lag int)
- returns int[]
- as $$
- declare
- local_st int[] := st;
- local_lag int := lag;
- begin
- if local_st is null then
- local_st := '{}'::int[];
- while local_lag >= 0
- loop
- select array_append(local_st, null::int) into local_st;
- local_lag := local_lag - 1;
- end loop;
- end if;
- return array_append(local_st[2:lag+1], val);
- end;
- $$ language plpgsql;
-
-create function mylag_finalfn(st int[])
- returns int
- as $$
- begin
- return st[1];
- end;
- $$ language plpgsql;
-
-create function mylag_prefn(st1 int[], st2 int[])
- returns int[]
- as $$
- select '{}'::int[];
- $$ language sql;
-
-create aggregate mylag(int, int) (
- sfunc = mylag_transfn,
- stype = int[],
- finalfunc = mylag_finalfn,
- prefunc = mylag_prefn,
- initcond = '{null,null}'
- );
-
--- This will be executed only on master, not on segments.
-select i, mylag(i, 2) over (order by i) from generate_series(1,10)i;
-
-create table t1 (a int, b int) distributed by (a);
-
-insert into t1 select i%3, 22*i/7 from generate_series(0,10)i;
-
-select a,b,mylag(b,1) over (order by b) from t1;
-
---
--- Misc tests - cover different ways of defining a window.
---
-CREATE AGGREGATE mysum (int) (
- STYPE = bigint,
- SFUNC = int4_sum,
- prefunc = int8pl
-);
-SELECT a,b,mysum(b) over (order by b) FROM t1;
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3
-SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as
- (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row); -- mvd 1,2->3
-
-select a,b,mylag(b, 1) over (partition by a order by b) from t1; -- mvd 1,2->3
-