Draft of OQL aggregates documentation. [#130418485] - Needs revision to implement formatting/style/prose to indicate its experimental status. - Incomplete: changes not incoporporated into the grammar definition, because the grammar is not up to date. - Examples should be added (and verified) for each of the functions.
Project: http://git-wip-us.apache.org/repos/asf/incubator-geode/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-geode/commit/e1ffcd10 Tree: http://git-wip-us.apache.org/repos/asf/incubator-geode/tree/e1ffcd10 Diff: http://git-wip-us.apache.org/repos/asf/incubator-geode/diff/e1ffcd10 Branch: refs/staging/docs-grant1 Commit: e1ffcd10a9d90671465a5f961d9d908fa00c9334 Parents: d12bc23 Author: Karen Miller <[email protected]> Authored: Wed Sep 21 14:00:15 2016 -0700 Committer: Karen Miller <[email protected]> Committed: Wed Sep 21 14:00:15 2016 -0700 ---------------------------------------------------------------------- .../the_select_statement.html.md.erb | 118 +++++++++++++++++++ ...ictions_and_unsupported_features.html.md.erb | 14 ++- 2 files changed, 130 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/e1ffcd10/developing/query_select/the_select_statement.html.md.erb ---------------------------------------------------------------------- diff --git a/developing/query_select/the_select_statement.html.md.erb b/developing/query_select/the_select_statement.html.md.erb index 04d14df..04f9f3b 100644 --- a/developing/query_select/the_select_statement.html.md.erb +++ b/developing/query_select/the_select_statement.html.md.erb @@ -201,3 +201,121 @@ FROM /exampleRegion p, p.positions.values pos WHERE p.ID > 0 OR p.status = 'active' OR pos.secId OR pos.secId = 'IBM' ``` + +<div style="background-color:lightyellow;"> +<h2>(Experimental Feature) Aggregate Functions</h2> + +<p> +Some prose here about being experimental. +</p> + +<p> +The aggregate functions +<code>MIN</code>, +<code>MAX</code>, +<code>AVG</code>, +<code>AVG</code> over a DISTINCT expression, +<code>SUM</code> over a DISTINCT expression, +<code>COUNT</code>, and +<code>COUNT</code> over a DISTINCT expression +are supported. +The <code>GROUP BY</code> extension is also supported where appropriate. +</p> + +<p> +The <code>MIN</code> function returns the smallest of the selected +expression. +The type of the expression must evaluate to a +<code>java.lang.Comparable</code>. +</p> + +<p> +The <code>MAX</code> function returns the largest of the selected +expression. +The type of the expression must evaluate to a +<code>java.lang.Comparable</code>. +</p> + +<p> +The <code>AVG</code> function returns the arithmetic mean of the set +formed by the selected expression. +The type of the expression must evaluate to a +<code>java.lang.Number</code>. +For partitioned regions, +each node's buckets provide both a sum and the number of elements +to the node executing the query, +such that a correct average may be computed. +</p> + +<p> +The <code>AVG</code> function where the DISTINCT modifier is applied +to the expression returns the arithmetic mean of the set +of unique (distinct) values. +The type of the expression must evaluate to a +<code>java.lang.Number</code>. +For partitioned regions, +the distinct values in a node's buckets are returned +to the node executing the query. +The query node can then calculate the avarage over +the values that are unique across nodes, +after eliminating duplicate values that come from separate nodes. +</p> + +<p> +The <code>SUM</code> function returns the sum over the set +formed by the selected expression. +The type of the expression must evaluate to a +<code>java.lang.Number</code>. +For partitioned regions, +each node's buckets compute a sum over that node, +returning that sum +to the node executing the query, +when then sums across all nodes. +</p> + +<p> +The <code>SUM</code> function where the DISTINCT modifier is applied +to the expression returns the sum over the set +of unique (distinct) values. +The type of the expression must evaluate to a +<code>java.lang.Number</code>. +For partitioned regions, +the distinct values in a node's buckets are returned +to the node executing the query. +The query node can then calculate the sum over +the values that are unique across nodes, +after eliminating duplicate values that come from separate nodes. +</p> + +<p> +The <code>COUNT</code> function returns the quantity of values in the set +formed by the selected expression. +For example, to return the quantity of employees who have a +positive sales amount: +</p> +<pre> +SELECT count(e.sales) FROM /employees e WHERE e.sales > 0.0 +</pre> +<p> +The <code>COUNT</code> function where the DISTINCT modifier is applied +returns the quantity of unique (distinct) values in the set +formed by the selected expression. +</p> +</div> + +<div style="background-color:lightyellow;"> +<h2>(Experimental Feature) GROUP BY Extension for Aggregate Functions</h2> + +<p> +<code>GROUP BY</code> is required +when aggregate functions are used in combination +with other selected items. +It permits ordering. +For example, +</p> + +<pre> +SELECT ID, MAX(e.sales) FROM /employees e GROUP BY ID +</pre> + +</div> http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/e1ffcd10/developing/querying_basics/restrictions_and_unsupported_features.html.md.erb ---------------------------------------------------------------------- diff --git a/developing/querying_basics/restrictions_and_unsupported_features.html.md.erb b/developing/querying_basics/restrictions_and_unsupported_features.html.md.erb index 31a49b6..baac020 100644 --- a/developing/querying_basics/restrictions_and_unsupported_features.html.md.erb +++ b/developing/querying_basics/restrictions_and_unsupported_features.html.md.erb @@ -4,7 +4,6 @@ title: Query Language Restrictions and Unsupported Features At a high level, Geode does not support the following querying features: -- GROUPBY is not supported. - Indexes targeted for joins across more than one region are not supported - Static method invocations. For example, the following query is invalid: @@ -12,10 +11,21 @@ At a high level, Geode does not support the following querying features: SELECT DISTINCT * FROM /QueryRegion0 WHERE aDay = Day.Wednesday ``` -- Aggregate functions are not supported. - You cannot create an index on fields using Set/List types (Collection types) that are not comparable. The OQL index implementation expects fields to be Comparable. To workaround this, you can create a custom Collection type that implements Comparable. - ORDER BY is only supported with DISTINCT queries. In addition, there are some specific limitations on partitioned region querying. See [Partitioned Region Query Restrictions](../query_additional/partitioned_region_query_restrictions.html#concept_5353476380D44CC1A7F586E5AE1CE7E8). +<div style="background-color:lightyellow;"> +<h2> Features Supported on an Experimental Basis</h2> +<p> +These features are supported, +but their experimental implementation may change: +</p> + +<ul> +<li> GROUP BY +<li> The aggregate functions AVG, MIN, MAX, COUNT, SUM +</ul> +</div>
