Folks,
Here's Harada-san's moving average windowing function, expressed as a
contrib module. It has tests, etc.
Cheers,
David.
--
David Fetter <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/Makefile b/contrib/Makefile
index 738a28a..e9fc2f9 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -23,6 +23,7 @@ WANTED_DIRS = \
isn \
lo \
ltree \
+ movavg \
oid2name \
pageinspect \
pg_buffercache \
diff --git a/contrib/movavg/Makefile b/contrib/movavg/Makefile
new file mode 100644
index 0000000..b295575
--- /dev/null
+++ b/contrib/movavg/Makefile
@@ -0,0 +1,17 @@
+# $PostgreSQL$
+
+MODULES = movavg
+DATA_built = movavg.sql
+DATA = uninstall_movavg.sql
+REGRESS = install_movavg one_sliding
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/movavg
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/movavg/data/one_sliding.data
b/contrib/movavg/data/one_sliding.data
new file mode 100644
index 0000000..9811557
--- /dev/null
+++ b/contrib/movavg/data/one_sliding.data
@@ -0,0 +1,11 @@
+develop 10 5200 2007/08/01
+sales 1 5000 2006/10/01
+personnel 5 3500 2007/12/10
+sales 4 4800 2007/08/08
+sales 6 5500 2007/01/02
+personnel 2 3900 2006/12/23
+develop 7 4200 2008/01/01
+develop 9 4500 2008/01/01
+sales 3 4800 2007/08/01
+develop 8 6000 2006/10/01
+develop 11 5200 2007/08/15
diff --git a/contrib/movavg/expected/install_movavg.out
b/contrib/movavg/expected/install_movavg.out
new file mode 100644
index 0000000..43f11fa
--- /dev/null
+++ b/contrib/movavg/expected/install_movavg.out
@@ -0,0 +1,3 @@
+SET client_min_messages = warning;
+\set ECHO none
+RESET client_min_messages;
diff --git a/contrib/movavg/expected/one_sliding.out
b/contrib/movavg/expected/one_sliding.out
new file mode 100644
index 0000000..e398e4f
--- /dev/null
+++ b/contrib/movavg/expected/one_sliding.out
@@ -0,0 +1,32 @@
+CREATE TABLE empsalary(
+ depname varchar,
+ empno bigint,
+ salary int,
+ enroll_date date
+);
+\COPY empsalary FROM 'data/one_sliding.data'
+SELECT
+ depname,
+ salary,
+ movavg(salary::float8, 1) OVER w
+FROM empsalary
+WINDOW w AS (
+ PARTITION BY depname
+ ORDER BY salary
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+);
+ depname | salary | movavg
+-----------+--------+------------------
+ develop | 4200 | 4350
+ develop | 4500 | 4633.33333333333
+ develop | 5200 | 4966.66666666667
+ develop | 5200 | 5466.66666666667
+ develop | 6000 | 5600
+ personnel | 3500 | 3700
+ personnel | 3900 | 3700
+ sales | 4800 | 4800
+ sales | 4800 | 4866.66666666667
+ sales | 5000 | 5100
+ sales | 5500 | 5250
+(11 rows)
+
diff --git a/contrib/movavg/movavg.c b/contrib/movavg/movavg.c
new file mode 100644
index 0000000..b5e28ca
--- /dev/null
+++ b/contrib/movavg/movavg.c
@@ -0,0 +1,125 @@
+#include "postgres.h"
+#include "fmgr.h"
+#include "windowapi.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(movavg);
+
+Datum movavg(PG_FUNCTION_ARGS);
+
+typedef struct {
+ int64 exiting; /* absolute position of exiting row */
+ int64 entering; /* absolute position of entering row */
+ float8 svalue; /* trans value */
+ int count; /* the number of accumlated values */
+} movavg_data;
+
+Datum
+movavg(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ bool isnull, isout;
+ int64 cur = WinGetCurrentPosition(winobj);
+ int32 range;
+ Datum value;
+ int i;
+ bool const_range;
+
+ /* current row +/- <range> rows are the targets */
+ range = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull || range < 0)
+ {
+ elog(ERROR, "invalid range");
+ }
+ /* if it's stable Const value or not */
+ const_range = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
+
+ /*
+ * For variable range arguments, we only calculate exact
+ * average for all the target rows. Otherwise we can
+ * optimze it by subtract/add method.
+ */
+ if (!const_range)
+ {
+ float8 svalue = 0.0;
+ int count = 0;
+
+ for(i = cur - range; i <= cur + range; i++)
+ {
+ value = WinGetFuncArgInPartition(winobj, 0, i,
+ WINDOW_SEEK_HEAD, false,
&isnull, &isout);
+ if (!isnull && !isout)
+ {
+ svalue += DatumGetFloat8(value);
+ count++;
+ }
+ }
+
+ WinSetMarkPosition(winobj, cur - range);
+
+ if (count > 0)
+ PG_RETURN_FLOAT8(svalue / (float8) count);
+ PG_RETURN_NULL();
+ }
+ else
+ {
+ movavg_data *sdata = (movavg_data *)
+
WinGetPartitionLocalMemory(winobj, sizeof(movavg_data));
+
+ /* optimize for the single row case */
+ if (range == 0)
+ {
+ value = WinGetFuncArgCurrent(winobj, 0, &isnull);
+ if (isnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(value);
+ }
+
+ /*
+ * The first row is the only special case.
+ * Calculate avarage as usual. Otherwise,
+ * exiting row value is subtracted and entering
+ * row is added then returns result.
+ */
+ if (sdata->entering == 0)
+ {
+ for(i = 0; i <= range; i++)
+ {
+ value = WinGetFuncArgInPartition(winobj, 0, i,
+ WINDOW_SEEK_HEAD,
false, &isnull, &isout);
+ if (!isnull && !isout)
+ {
+ sdata->svalue += DatumGetFloat8(value);
+ sdata->count++;
+ }
+ }
+ sdata->exiting = -range;
+ sdata->entering = range;
+ }
+ else
+ {
+ value = WinGetFuncArgInPartition(winobj, 0,
sdata->exiting,
+ WINDOW_SEEK_HEAD, true,
&isnull, &isout);
+ if (!isnull && !isout)
+ {
+ sdata->svalue -= DatumGetFloat8(value);
+ sdata->count--;
+ }
+ sdata->exiting++;
+
+ sdata->entering++;
+ value = WinGetFuncArgInPartition(winobj, 0,
sdata->entering,
+ WINDOW_SEEK_HEAD,
false, &isnull, &isout);
+ if (!isnull && !isout)
+ {
+ sdata->svalue += DatumGetFloat8(value);
+ sdata->count++;
+ }
+ }
+
+ if (sdata->count > 0)
+ PG_RETURN_FLOAT8(sdata->svalue / (float8) sdata->count);
+ PG_RETURN_NULL();
+ }
+}
diff --git a/contrib/movavg/movavg.sql.in b/contrib/movavg/movavg.sql.in
new file mode 100644
index 0000000..2ffe3f9
--- /dev/null
+++ b/contrib/movavg/movavg.sql.in
@@ -0,0 +1,6 @@
+SET search_path = public;
+
+CREATE OR REPLACE FUNCTION movavg(float8, int4)
+RETURNS float8
+AS 'MODULE_PATHNAME', 'movavg'
+LANGUAGE C WINDOW;
diff --git a/contrib/movavg/sql/install_movavg.sql
b/contrib/movavg/sql/install_movavg.sql
new file mode 100644
index 0000000..cb58f23
--- /dev/null
+++ b/contrib/movavg/sql/install_movavg.sql
@@ -0,0 +1,5 @@
+SET client_min_messages = warning;
+\set ECHO none
+\i movavg.sql
+\set ECHO all
+RESET client_min_messages;
diff --git a/contrib/movavg/sql/one_sliding.sql
b/contrib/movavg/sql/one_sliding.sql
new file mode 100644
index 0000000..ec85d73
--- /dev/null
+++ b/contrib/movavg/sql/one_sliding.sql
@@ -0,0 +1,19 @@
+CREATE TABLE empsalary(
+ depname varchar,
+ empno bigint,
+ salary int,
+ enroll_date date
+);
+
+\COPY empsalary FROM 'data/one_sliding.data'
+
+SELECT
+ depname,
+ salary,
+ movavg(salary::float8, 1) OVER w
+FROM empsalary
+WINDOW w AS (
+ PARTITION BY depname
+ ORDER BY salary
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+);
diff --git a/contrib/movavg/uninstall_movavg.sql
b/contrib/movavg/uninstall_movavg.sql
new file mode 100644
index 0000000..2aab014
--- /dev/null
+++ b/contrib/movavg/uninstall_movavg.sql
@@ -0,0 +1,3 @@
+SET search_path = public;
+
+DROP FUNCTION IF EXISTS movavg(float8, int4);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
