You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@age.apache.org by jg...@apache.org on 2020/12/09 22:33:50 UTC
[incubator-age] branch master updated: Add aggregate functions
stdev() & stdevp()
This is an automated email from the ASF dual-hosted git repository.
jgemignani pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-age.git
The following commit(s) were added to refs/heads/master by this push:
new 16c8893 Add aggregate functions stdev() & stdevp()
16c8893 is described below
commit 16c8893b75253a573aa0bdfa46ffc5119cd839ee
Author: John Gemignani <jr...@gmail.com>
AuthorDate: Tue Dec 8 16:08:08 2020 -0800
Add aggregate functions stdev() & stdevp()
Added the aggregate functions stdev() & stdevp().
These functions output float8 data, as agtype, for any int, float,
or numeric input data.
Added changes to the agtype_to_float8() routine to allow numerics
to be cast to float8.
Added regression tests.
---
age--0.2.0.sql | 41 ++++++++++++++++++++++++++++
regress/expected/expr.out | 54 ++++++++++++++++++++++++++++++-------
regress/sql/expr.sql | 16 +++++++++--
src/backend/utils/adt/agtype.c | 61 +++++++++++++++++++++++++++++++++++++++---
4 files changed, 156 insertions(+), 16 deletions(-)
diff --git a/age--0.2.0.sql b/age--0.2.0.sql
index 8197a89..46f532d 100644
--- a/age--0.2.0.sql
+++ b/age--0.2.0.sql
@@ -1242,6 +1242,47 @@ PARALLEL SAFE
AS 'MODULE_PATHNAME';
--
+-- aggregate functions components for stdev & stdevp
+--
+
+-- wrapper for the stdev final function to pass 0 instead of null
+CREATE FUNCTION ag_catalog.age_float8_stddev_samp_aggfinalfn(_float8)
+RETURNS float8
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+-- aggregate for stdev
+CREATE AGGREGATE ag_catalog.age_stdev(float8)
+(
+ stype = _float8,
+ sfunc = float8_accum,
+ finalfunc = age_float8_stddev_samp_aggfinalfn,
+ combinefunc = float8_combine,
+ finalfunc_modify = read_only,
+ initcond = '{0,0,0}',
+ parallel = safe
+);
+-- wrapper for the stdevp final function to pass 0 instead of null
+CREATE FUNCTION ag_catalog.age_float8_stddev_pop_aggfinalfn(_float8)
+RETURNS float8
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+-- aggregate for stdevp
+CREATE AGGREGATE ag_catalog.age_stdevp (float8)
+(
+ stype = _float8,
+ sfunc = float8_accum,
+ finalfunc = age_float8_stddev_pop_aggfinalfn,
+ combinefunc = float8_combine,
+ finalfunc_modify = read_only,
+ initcond = '{0,0,0}',
+ parallel = safe
+);
+
+--
-- function for typecasting an agtype value to another agtype value
--
CREATE FUNCTION ag_catalog.agtype_typecast_numeric(agtype)
diff --git a/regress/expected/expr.out b/regress/expected/expr.out
index 683f705..c4a2067 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -4181,7 +4181,7 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 2
---
(0 rows)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8, age: 23})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8::numeric, age: 23})$$) AS (a agtype);
a
---
(0 rows)
@@ -4191,21 +4191,21 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age:
---
(0 rows)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9, age: 20})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9::numeric, age: 20})$$) AS (a agtype);
a
---
(0 rows)
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype);
- vertex
-----------------------------------------------------------------------------------------------------------------
+ vertex
+-------------------------------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "name": "Jack"}}::vertex
{"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "name": "Jill"}}::vertex
{"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "name": "Jim"}}::vertex
{"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "name": "Rick"}}::vertex
- {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8, "name": "Ann"}}::vertex
+ {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex
{"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "name": "Derek"}}::vertex
- {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9, "name": "Jessica"}}::vertex
+ {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex
(7 rows)
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN avg(u.gpa), sum(u.gpa), sum(u.gpa)/count(u.gpa), count(u.gpa), count(*) $$)
@@ -4227,15 +4227,15 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Mike", age: 18})$$) AS
(0 rows)
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype);
- vertex
-----------------------------------------------------------------------------------------------------------------
+ vertex
+-------------------------------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "name": "Jack"}}::vertex
{"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "name": "Jill"}}::vertex
{"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "name": "Jim"}}::vertex
{"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "name": "Rick"}}::vertex
- {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8, "name": "Ann"}}::vertex
+ {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex
{"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "name": "Derek"}}::vertex
- {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9, "name": "Jessica"}}::vertex
+ {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex
{"id": 844424930131976, "label": "students", "properties": {"age": 24, "name": "Dave"}}::vertex
{"id": 844424930131977, "label": "students", "properties": {"age": 18, "name": "Mike"}}::vertex
(9 rows)
@@ -4317,6 +4317,40 @@ LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agt...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
--
+-- aggregate functions stdev() & stdevp()
+--
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN stdev(u.gpa), stdevp(u.gpa) $$)
+AS (stdev agtype, stdevp agtype);
+ stdev | stdevp
+-------------------+-------------------
+ 0.549566929066705 | 0.508800109100231
+(1 row)
+
+-- should return 0
+SELECT * FROM cypher('UCSC', $$ RETURN stdev(NULL) $$) AS (stdev agtype);
+ stdev
+-------
+ 0.0
+(1 row)
+
+SELECT * FROM cypher('UCSC', $$ RETURN stdevp(NULL) $$) AS (stdevp agtype);
+ stdevp
+--------
+ 0.0
+(1 row)
+
+-- should fail
+SELECT * FROM cypher('UCSC', $$ RETURN stdev() $$) AS (stdev agtype);
+ERROR: function ag_catalog.age_stdev() does not exist
+LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN stdev() $$) AS (stdev...
+ ^
+HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+SELECT * FROM cypher('UCSC', $$ RETURN stdevp() $$) AS (stdevp agtype);
+ERROR: function ag_catalog.age_stdevp() does not exist
+LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN stdevp() $$) AS (stde...
+ ^
+HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+--
-- Cleanup
--
SELECT * FROM drop_graph('UCSC', true);
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index b23f39a..b3e20b7 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -1782,9 +1782,9 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jack", gpa: 3.0, age: 2
SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jill", gpa: 3.5, age: 27})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jim", gpa: 3.75, age: 32})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 24})$$) AS (a agtype);
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8, age: 23})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8::numeric, age: 23})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age: 19})$$) AS (a agtype);
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9, age: 20})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9::numeric, age: 20})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype);
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN avg(u.gpa), sum(u.gpa), sum(u.gpa)/count(u.gpa), count(u.gpa), count(*) $$)
AS (avg agtype, sum agtype, sum_divided_by_count agtype, count agtype, count_star agtype);
@@ -1817,6 +1817,18 @@ SELECT * FROM cypher('UCSC', $$ RETURN min() $$) AS (min agtype);
SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agtype);
--
+-- aggregate functions stdev() & stdevp()
+--
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN stdev(u.gpa), stdevp(u.gpa) $$)
+AS (stdev agtype, stdevp agtype);
+-- should return 0
+SELECT * FROM cypher('UCSC', $$ RETURN stdev(NULL) $$) AS (stdev agtype);
+SELECT * FROM cypher('UCSC', $$ RETURN stdevp(NULL) $$) AS (stdevp agtype);
+-- should fail
+SELECT * FROM cypher('UCSC', $$ RETURN stdev() $$) AS (stdev agtype);
+SELECT * FROM cypher('UCSC', $$ RETURN stdevp() $$) AS (stdevp agtype);
+
+--
-- Cleanup
--
SELECT * FROM drop_graph('UCSC', true);
diff --git a/src/backend/utils/adt/agtype.c b/src/backend/utils/adt/agtype.c
index 50e6fe9..92d359f 100644
--- a/src/backend/utils/adt/agtype.c
+++ b/src/backend/utils/adt/agtype.c
@@ -46,6 +46,7 @@
#include "catalog/ag_graph.h"
#include "catalog/ag_label.h"
#include "utils/graphid.h"
+#include "utils/numeric.h"
typedef struct agtype_in_state
{
@@ -2172,15 +2173,16 @@ Datum agtype_to_float8(PG_FUNCTION_ARGS)
float8 result;
if (!agtype_extract_scalar(&agtype_in->root, &agtv) ||
- (agtv.type != AGTV_FLOAT && agtv.type != AGTV_INTEGER))
+ (agtv.type != AGTV_FLOAT &&
+ agtv.type != AGTV_INTEGER &&
+ agtv.type != AGTV_NUMERIC))
cannot_cast_agtype_value(agtv.type, "float");
PG_FREE_IF_COPY(agtype_in, 0);
if (agtv.type == AGTV_FLOAT)
result = agtv.val.float_value;
-
- if (agtv.type == AGTV_INTEGER)
+ else if (agtv.type == AGTV_INTEGER)
{
/*
* Get the string representation of the integer because it could be
@@ -2199,6 +2201,9 @@ Datum agtype_to_float8(PG_FUNCTION_ARGS)
ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("cannot cast to float8, integer value out of range")));
}
+ else if (agtv.type == AGTV_NUMERIC)
+ result = DatumGetFloat8(DirectFunctionCall1(numeric_float8,
+ NumericGetDatum(agtv.val.numeric)));
PG_RETURN_FLOAT8(result);
}
@@ -7006,7 +7011,6 @@ Datum age_timestamp(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(agtype_value_to_agtype(&agtv_result));
}
-
agtype_value *alter_property_value(agtype_value *properties, char *var_name, agtype *new_v, bool remove_property)
{
agtype_iterator *it;
@@ -7100,3 +7104,52 @@ agtype_value *alter_property_value(agtype_value *properties, char *var_name, agt
return parsed_agtype_value;
}
+PG_FUNCTION_INFO_V1(age_float8_stddev_samp_aggfinalfn);
+
+Datum age_float8_stddev_samp_aggfinalfn(PG_FUNCTION_ARGS)
+{
+ Datum result;
+ PGFunction func;
+
+ /* we can't use DirectFunctionCall1 as it errors for NULL values */
+ func = float8_stddev_samp;
+ result = (*func) (fcinfo);
+
+ /*
+ * Check to see if float8_stddev_samp returned null. If so, we need to
+ * return a float8 0.
+ */
+ if (fcinfo->isnull)
+ {
+ fcinfo->isnull = false;
+
+ PG_RETURN_FLOAT8(0.0);
+ }
+
+ return result;
+}
+
+PG_FUNCTION_INFO_V1(age_float8_stddev_pop_aggfinalfn);
+
+Datum age_float8_stddev_pop_aggfinalfn(PG_FUNCTION_ARGS)
+{
+ Datum result;
+ PGFunction func;
+
+ /* we can't use DirectFunctionCall1 as it errors for NULL values */
+ func = float8_stddev_pop;
+ result = (*func) (fcinfo);
+
+ /*
+ * Check to see if float8_stddev_pop returned null. If so, we need to
+ * return a float8 0.
+ */
+ if (fcinfo->isnull)
+ {
+ fcinfo->isnull = false;
+
+ PG_RETURN_FLOAT8(0.0);
+ }
+
+ return result;
+}