>From Simon Dew <[email protected]>:
Simon Dew has uploaded this change for review. (
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/18251 )
Change subject: DOC-12137: Add ARRAY_MEDIAN() Function
......................................................................
DOC-12137: Add ARRAY_MEDIAN() Function
... and associated MEDIAN() aggregation pseudo-function.
Add ARRAY_MEDIAN to the builtin functions
Add MEDIAN to aggregation pseudo-functions
Fix typo: SKEWENESS → SKEWNESS
Change-Id: Icb6459ea616244a048ed95757b3a0eb9094ba85a
---
M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
2 files changed, 55 insertions(+), 2 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/51/18251/1
diff --git
a/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md
b/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md
index 0657fb0..2cf493b 100644
--- a/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md
+++ b/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md
@@ -177,6 +177,43 @@
3.4
+### array_median ###
+ * Syntax:
+
+ array_median(num_collection)
+
+ * Gets the median value of the non-null and non-missing numeric items in the
given collection.
+
+ The function starts by sorting the numeric items.
+
+ - If there is an odd number of numeric items, the function returns the
item that is exactly in the middle of the range: that is, it has the same
number of items before and after.
+ - If there is an even number of numeric items, the function returns the
mean of the two items that are exactly in the middle of the range.
+
+ * Note: You cannot use the `DISTINCT` keyword with this function, or with the
`median` aggregation pseudo-function.
+ The `median` aggregation pseudo-function does support the `FILTER`
expression.
+ There is no `strict_median` function corresponding to this function.
+ * Arguments:
+ * `num_collection` could be:
+ * an `array` or `multiset`,
+ * or, a `null` value,
+ * or, a `missing` value.
+ * Return Value:
+ * a `double` value representing the median of the non-null and non-missing
numbers in the given collection,
+ * `null` is returned if the input is `null` or `missing`,
+ * `null` is returned if the given collection does not contain any non-null
and non-missing items,
+ * any other non-array and non-multiset input value will cause a type error,
+ * any other non-numeric value in the input collection will cause a type
error.
+ * Example:
+
+ { "v1": array_median( [1.2, 2.3, 3.4, 0, null, missing],
+ "v2": array_median( [1.2, 2.3, 3.4, 4.5, 0, null, missing] ) };
+
+ * The expected result is:
+
+ { "v1": 1.75,
+ "v2": 2.3 }
+
+
### array_stddev_samp ###
* Syntax:
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
index 04a65d1..a1b9aca 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -1215,7 +1215,7 @@
For example, `SELECT COUNT(*) FROM customers` simply returns the total number
of customers, whereas `SELECT COUNT(rating) FROM customers` returns the number
of customers who have known ratings (that is, their ratings are not `null` or
`missing`).
-Because the aggregation pseudo-functions sometimes restructure their operands,
they can be used only in query blocks where (explicit or implicit) grouping is
being done. Therefore the pseudo-functions cannot operate directly on arrays or
multisets. For operating directly on JSON collections, SQL++ provides a set of
ordinary functions for computing aggregations. Each ordinary aggregation
function (except the ones corresponding to `COUNT` and `ARRAY_AGG`) has two
versions: one that ignores `null` and `missing` values and one that returns
`null` if a `null` or `missing` value is encountered anywhere in the
collection. The names of the aggregation functions are as follows:
+Because the aggregation pseudo-functions sometimes restructure their operands,
they can be used only in query blocks where (explicit or implicit) grouping is
being done. Therefore the pseudo-functions cannot operate directly on arrays or
multisets. For operating directly on JSON collections, SQL++ provides a set of
ordinary functions for computing aggregations. Each ordinary aggregation
function (except as noted below) has two versions: one that ignores `null` and
`missing` values, and one that returns `null` if a `null` or `missing` value is
encountered anywhere in the collection. The names of the aggregation functions
are as follows:
| Aggregation pseudo-function; operates on groups only | Ordinary function:
Ignores NULL or MISSING values | Ordinary function: Returns NULL if NULL or
MISSING are encountered|
|----------|----------|--------|
@@ -1224,11 +1224,12 @@
| MAX | ARRAY_MIN| STRICT_MIN |
| MIN | ARRAY_AVG| STRICT_AVG |
| COUNT |ARRAY_COUNT|STRICT_COUNT (see exception below) |
+| MEDIAN | ARRAY_MEDIAN | |
|STDDEV_SAMP|ARRAY_STDDEV_SAMP| STRICT_STDDEV_SAMP |
|STDDEV_POP|ARRAY_STDDEV_POP| STRICT_STDDEV_POP |
|VAR_SAMP|ARRAY_VAR_SAMP| STRICT_VAR_SAMP |
|VAR_POP|ARRAY_VAR_POP| STRICT_VAR_POP |
-|SKEWENESS|ARRAY_SKEWNESS| STRICT_SKEWNESS |
+|SKEWNESS|ARRAY_SKEWNESS| STRICT_SKEWNESS |
|KURTOSIS|ARRAY_KURTOSIS| STRICT_KURTOSIS |
| |ARRAY_AGG| |
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/18251
To unsubscribe, or for help writing mail filters, visit
https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: neo
Gerrit-Change-Id: Icb6459ea616244a048ed95757b3a0eb9094ba85a
Gerrit-Change-Number: 18251
Gerrit-PatchSet: 1
Gerrit-Owner: Simon Dew <[email protected]>
Gerrit-MessageType: newchange
