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 2021/01/15 17:46:43 UTC
[incubator-age] branch master updated: Add aggregate functions
percentileCont & percentileDisc
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 3e0bd99 Add aggregate functions percentileCont & percentileDisc
3e0bd99 is described below
commit 3e0bd99cce47d4c5a7632f3aea30c7f6be7e7a4a
Author: John Gemignani <jr...@gmail.com>
AuthorDate: Tue Jan 12 16:56:28 2021 -0800
Add aggregate functions percentileCont & percentileDisc
Added the openCypher aggregate functions percentileCont & percentileDisc.
Added regression tests.
---
age--0.3.0.sql | 42 +++++++-
regress/expected/expr.out | 60 ++++++++++--
regress/sql/expr.sql | 31 ++++--
src/backend/utils/adt/agtype.c | 217 +++++++++++++++++++++++++++++++++++++++++
4 files changed, 333 insertions(+), 17 deletions(-)
diff --git a/age--0.3.0.sql b/age--0.3.0.sql
index 667faf2..121ed29 100644
--- a/age--0.3.0.sql
+++ b/age--0.3.0.sql
@@ -1286,7 +1286,7 @@ CREATE AGGREGATE ag_catalog.age_stdevp (float8)
);
--
--- aggregate transfers function for min & max
+-- aggregate transfer functions for min & max
--
-- max
CREATE FUNCTION ag_catalog.age_agtype_larger_aggtransfn(agtype, agtype)
@@ -1324,6 +1324,46 @@ CREATE AGGREGATE ag_catalog.age_min (agtype)
);
--
+-- aggregate transfer/final functions for percentileCont & percentileDisc
+--
+CREATE FUNCTION ag_catalog.age_percentile_aggtransfn(internal, float8, float8)
+RETURNS internal
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION ag_catalog.age_percentile_cont_aggfinalfn(internal)
+RETURNS float8
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION ag_catalog.age_percentile_disc_aggfinalfn(internal)
+RETURNS float8
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+CREATE AGGREGATE ag_catalog.age_percentilecont(float8, float8)
+(
+ stype = internal,
+ sfunc = ag_catalog.age_percentile_aggtransfn,
+ finalfunc = ag_catalog.age_percentile_cont_aggfinalfn,
+ parallel = safe
+);
+
+CREATE AGGREGATE ag_catalog.age_percentiledisc(float8, float8)
+(
+ stype = internal,
+ sfunc = ag_catalog.age_percentile_aggtransfn,
+ finalfunc = ag_catalog.age_percentile_disc_aggfinalfn,
+ 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 318db8f..c714c78 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -4342,40 +4342,82 @@ 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()
+-- aggregate functions stDev() & stDevP()
--
-SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN stdev(u.gpa), stdevp(u.gpa) $$)
-AS (stdev agtype, stdevp agtype);
+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);
+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);
+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);
+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...
+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);
+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...
+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.
--
+-- aggregate functions percentileCont() & percentileDisc()
+--
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileCont(u.gpa, .55), percentileDisc(u.gpa, .55), percentileCont(u.gpa, .9), percentileDisc(u.gpa, .9) $$)
+AS (percentileCont1 agtype, percentileDisc1 agtype, percentileCont2 agtype, percentileDisc2 agtype);
+ percentilecont1 | percentiledisc1 | percentilecont2 | percentiledisc2
+-----------------+-----------------+-----------------+-----------------
+ 3.765 | 3.75 | 3.94 | 4.0
+(1 row)
+
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileCont(u.gpa, .55) $$)
+AS (percentileCont agtype);
+ percentilecont
+----------------
+ 3.765
+(1 row)
+
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileDisc(u.gpa, .55) $$)
+AS (percentileDisc agtype);
+ percentiledisc
+----------------
+ 3.75
+(1 row)
+
+-- should return null
+SELECT * FROM cypher('UCSC', $$ RETURN percentileCont(NULL, .5) $$) AS (percentileCont agtype);
+ percentilecont
+----------------
+
+(1 row)
+
+SELECT * FROM cypher('UCSC', $$ RETURN percentileDisc(NULL, .5) $$) AS (percentileDisc agtype);
+ percentiledisc
+----------------
+
+(1 row)
+
+-- should fail
+SELECT * FROM cypher('UCSC', $$ RETURN percentileCont(.5, NULL) $$) AS (percentileCont agtype);
+ERROR: percentile value NULL is not a valid numeric value
+SELECT * FROM cypher('UCSC', $$ RETURN percentileDisc(.5, NULL) $$) AS (percentileDisc agtype);
+ERROR: percentile value NULL is not a valid numeric value
+--
-- Cleanup
--
SELECT * FROM drop_graph('UCSC', true);
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index 52a6eb9..3ff73a5 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -1826,16 +1826,33 @@ SELECT * FROM cypher('UCSC', $$ RETURN min() $$) AS (min agtype);
SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agtype);
--
--- aggregate functions stdev() & stdevp()
+-- aggregate functions stDev() & stDevP()
--
-SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN stdev(u.gpa), stdevp(u.gpa) $$)
-AS (stdev agtype, stdevp agtype);
+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);
+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);
+SELECT * FROM cypher('UCSC', $$ RETURN stDev() $$) AS (stDev agtype);
+SELECT * FROM cypher('UCSC', $$ RETURN stDevP() $$) AS (stDevP agtype);
+
+--
+-- aggregate functions percentileCont() & percentileDisc()
+--
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileCont(u.gpa, .55), percentileDisc(u.gpa, .55), percentileCont(u.gpa, .9), percentileDisc(u.gpa, .9) $$)
+AS (percentileCont1 agtype, percentileDisc1 agtype, percentileCont2 agtype, percentileDisc2 agtype);
+
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileCont(u.gpa, .55) $$)
+AS (percentileCont agtype);
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileDisc(u.gpa, .55) $$)
+AS (percentileDisc agtype);
+-- should return null
+SELECT * FROM cypher('UCSC', $$ RETURN percentileCont(NULL, .5) $$) AS (percentileCont agtype);
+SELECT * FROM cypher('UCSC', $$ RETURN percentileDisc(NULL, .5) $$) AS (percentileDisc agtype);
+-- should fail
+SELECT * FROM cypher('UCSC', $$ RETURN percentileCont(.5, NULL) $$) AS (percentileCont agtype);
+SELECT * FROM cypher('UCSC', $$ RETURN percentileDisc(.5, NULL) $$) AS (percentileDisc agtype);
--
-- Cleanup
diff --git a/src/backend/utils/adt/agtype.c b/src/backend/utils/adt/agtype.c
index 233a297..5014f60 100644
--- a/src/backend/utils/adt/agtype.c
+++ b/src/backend/utils/adt/agtype.c
@@ -30,7 +30,10 @@
#include "access/htup_details.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_aggregate_d.h"
#include "catalog/pg_collation_d.h"
+#include "catalog/pg_operator_d.h"
+#include "executor/nodeAgg.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "parser/parse_coerce.h"
@@ -57,6 +60,19 @@ typedef struct agtype_in_state
agtype_value *res;
} agtype_in_state;
+/* State structure for Percentile aggregate functions */
+typedef struct PercentileGroupAggState
+{
+ /* percentile value */
+ float8 percentile;
+ /* Sort object we're accumulating data in: */
+ Tuplesortstate *sortstate;
+ /* Number of normal rows inserted into sortstate: */
+ int64 number_of_rows;
+ /* Have we already done tuplesort_performsort? */
+ bool sort_done;
+} PercentileGroupAggState;
+
typedef enum /* type categories for datum_to_agtype */
{
AGT_TYPE_NULL, /* null, so we didn't bother to identify */
@@ -7256,3 +7272,204 @@ Datum age_agtype_smaller_aggtransfn(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(agtype_smaller);
}
+
+/* borrowed from PGs float8 routines for percentile_cont */
+static Datum float8_lerp(Datum lo, Datum hi, double pct)
+{
+ double loval = DatumGetFloat8(lo);
+ double hival = DatumGetFloat8(hi);
+
+ return Float8GetDatum(loval + (pct * (hival - loval)));
+}
+
+/* Code borrowed and adjusted from PG's ordered_set_transition function */
+PG_FUNCTION_INFO_V1(age_percentile_aggtransfn);
+
+Datum age_percentile_aggtransfn(PG_FUNCTION_ARGS)
+{
+ PercentileGroupAggState *pgastate;
+
+ /* verify we are in an aggregate context */
+ Assert(AggCheckCallContext(fcinfo, NULL) == AGG_CONTEXT_AGGREGATE);
+
+ /* if this is the first invocation, create the state */
+ if (PG_ARGISNULL(0))
+ {
+ MemoryContext old_mcxt;
+ float8 percentile;
+
+ /* validate the percentile */
+ if (PG_ARGISNULL(2))
+ ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("percentile value NULL is not a valid numeric value")));
+
+ percentile = PG_GETARG_FLOAT8(2);
+ if (percentile < 0 || percentile > 1 || isnan(percentile))
+ ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("percentile value %g is not between 0 and 1",
+ percentile)));
+
+ /* switch to the correct aggregate context */
+ old_mcxt = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+ /* create and initialize the state */
+ pgastate = palloc(sizeof(PercentileGroupAggState));
+ pgastate->percentile = percentile;
+ /*
+ * Percentiles need to be calculated from a sorted set. We are only
+ * using float8 values, using the less than operator, and flagging
+ * randomAccess to true - as we can potentially be reusing this
+ * sort multiple times in the same query.
+ */
+ pgastate->sortstate = tuplesort_begin_datum(FLOAT8OID,
+ Float8LessOperator,
+ InvalidOid, false, work_mem,
+ NULL, true);
+ pgastate->number_of_rows = 0;
+ pgastate->sort_done = false;
+
+ /* restore the old context */
+ MemoryContextSwitchTo(old_mcxt);
+ }
+ /* otherwise, retrieve the state */
+ else
+ pgastate = (PercentileGroupAggState *) PG_GETARG_POINTER(0);
+
+ /* Load the datum into the tuplesort object, but only if it's not null */
+ if (!PG_ARGISNULL(1))
+ {
+ tuplesort_putdatum(pgastate->sortstate, PG_GETARG_DATUM(1), false);
+ pgastate->number_of_rows++;
+ }
+ /* return the state */
+ PG_RETURN_POINTER(pgastate);
+}
+
+/* Code borrowed and adjusted from PG's percentile_cont_final function */
+PG_FUNCTION_INFO_V1(age_percentile_cont_aggfinalfn);
+
+Datum age_percentile_cont_aggfinalfn(PG_FUNCTION_ARGS)
+{
+ PercentileGroupAggState *pgastate;
+ float8 percentile;
+ int64 first_row = 0;
+ int64 second_row = 0;
+ Datum val;
+ Datum first_val;
+ Datum second_val;
+ double proportion;
+ bool isnull;
+
+ /* verify we are in an aggregate context */
+ Assert(AggCheckCallContext(fcinfo, NULL) == AGG_CONTEXT_AGGREGATE);
+
+ /* If there were no regular rows, the result is NULL */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ /* retrieve the state and percentile */
+ pgastate = (PercentileGroupAggState *) PG_GETARG_POINTER(0);
+ percentile = pgastate->percentile;
+
+ /* number_of_rows could be zero if we only saw NULL input values */
+ if (pgastate->number_of_rows == 0)
+ PG_RETURN_NULL();
+
+ /* Finish the sort, or rescan if we already did */
+ if (!pgastate->sort_done)
+ {
+ tuplesort_performsort(pgastate->sortstate);
+ pgastate->sort_done = true;
+ }
+ else
+ tuplesort_rescan(pgastate->sortstate);
+
+ /* calculate the percentile cont*/
+ first_row = floor(percentile * (pgastate->number_of_rows - 1));
+ second_row = ceil(percentile * (pgastate->number_of_rows - 1));
+
+ Assert(first_row < pgastate->number_of_rows);
+
+ if (!tuplesort_skiptuples(pgastate->sortstate, first_row, true))
+ elog(ERROR, "missing row in percentile_cont");
+
+ if (!tuplesort_getdatum(pgastate->sortstate, true, &first_val, &isnull, NULL))
+ elog(ERROR, "missing row in percentile_cont");
+ if (isnull)
+ PG_RETURN_NULL();
+
+ if (first_row == second_row)
+ {
+ val = first_val;
+ }
+ else
+ {
+ if (!tuplesort_getdatum(pgastate->sortstate, true, &second_val, &isnull, NULL))
+ elog(ERROR, "missing row in percentile_cont");
+
+ if (isnull)
+ PG_RETURN_NULL();
+
+ proportion = (percentile * (pgastate->number_of_rows - 1)) - first_row;
+ val = float8_lerp(first_val, second_val, proportion);
+ }
+
+ PG_RETURN_DATUM(val);
+}
+
+/* Code borrowed and adjusted from PG's percentile_disc_final function */
+PG_FUNCTION_INFO_V1(age_percentile_disc_aggfinalfn);
+
+Datum age_percentile_disc_aggfinalfn(PG_FUNCTION_ARGS)
+{
+ PercentileGroupAggState *pgastate;
+ double percentile;
+ Datum val;
+ bool isnull;
+ int64 rownum;
+
+ Assert(AggCheckCallContext(fcinfo, NULL) == AGG_CONTEXT_AGGREGATE);
+
+ /* If there were no regular rows, the result is NULL */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ pgastate = (PercentileGroupAggState *) PG_GETARG_POINTER(0);
+ percentile = pgastate->percentile;
+
+ /* number_of_rows could be zero if we only saw NULL input values */
+ if (pgastate->number_of_rows == 0)
+ PG_RETURN_NULL();
+
+ /* Finish the sort, or rescan if we already did */
+ if (!pgastate->sort_done)
+ {
+ tuplesort_performsort(pgastate->sortstate);
+ pgastate->sort_done = true;
+ }
+ else
+ tuplesort_rescan(pgastate->sortstate);
+
+ /*----------
+ * We need the smallest K such that (K/N) >= percentile.
+ * N>0, therefore K >= N*percentile, therefore K = ceil(N*percentile).
+ * So we skip K-1 rows (if K>0) and return the next row fetched.
+ *----------
+ */
+ rownum = (int64) ceil(percentile * pgastate->number_of_rows);
+ Assert(rownum <= pgastate->number_of_rows);
+
+ if (rownum > 1)
+ {
+ if (!tuplesort_skiptuples(pgastate->sortstate, rownum - 1, true))
+ elog(ERROR, "missing row in percentile_disc");
+ }
+
+ if (!tuplesort_getdatum(pgastate->sortstate, true, &val, &isnull, NULL))
+ elog(ERROR, "missing row in percentile_disc");
+
+ /* We shouldn't have stored any nulls, but do the right thing anyway */
+ if (isnull)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_DATUM(val);
+}