Select .. Group By


If GROUP BY clause is added to SELECT statement, results are grouped by each unique value evaluated in GROUP BY clause.

Note that likewise everywhere in JS/SQL, you can use arbitrary JavaScript expression in GROUP BY clause. The expression will be evaluated in the context of document where fields are bound as local variable in the JavaScript executing context.

On the other hand, if GROUP BY is used SELECT clause should contain aggregation expression in the following format AGGREGATOR(expression), where expression is evaluated for each document, but aggregators, such as SUM, AVG or MAX is used to combine all values within each group. Clusterpoint provides standard set of aggregators: SUM, AVG, COUNT, MIN, MAX. Simple example:

SELECT MAX(price) AS price, category FROM product GROUP BY category

GROUP BY is mandatory for any aggregation function. But it is possible to use "GROUP BY constant" (constant could be true or 1, etc.), constant makes one group of values. An example:

SELECT COUNT(price) AS price FROM product GROUP BY 1

Using an example of product collection, the following is an aggregation query that computes sum of prices for each of product categories:

SELECT SUM(price), category
FROM product
GROUP BY category

The real power of JS/SQL is applied when instead of simple field you use arbitrary JavaScript expression or function to create a category. For example, the following statement counts number of producing in each price range.

function PriceBucket(price) {
  var boundaries = [0, 1, 5, 10, 50, 100, 200, 500, 1000];
  for (var i = 1; i < boundaries.length; i++) {
    if (price >= boundaries[i - 1] && price < boundaries[i])
      return boundaries[i - 1].toString() + " to " + boundaries[i].toString();
  }
  return "above " + boundaries[boundaries.length - 1].toString();
}
SELECT PriceBucket(price), COUNT()
FROM product
GROUP BY PriceBucket(price)

Array Aggregation

Because JS/SQL operates with JavaScript type system that include repeated values like JavaScript Array, this opens up new opportunities. For example, if Array is passed in as value of GROUP BY clause, it means value will be added to each group specified by each Arrays value.

So query like this:

SELECT COUNT()
FROM product
GROUP BY name.split(" ")

actually counts how often each word is present in the name field of product documents. What happens here split function produces an array of words by splitting name on space, then COUNT() is called for each word.

Custom Aggregators

Clusterpoint provides a standard set of aggregators such as: SUM, AVG, COUNT, MIN, MAX.

However, since JS/SQL simply executes JavaScript code in every clause, it allows users to define custom aggregators that combine values from of the given expressions.  Aggregators are objects that take value of the expression it is aggregating in constructor and provide combine() method to combine intermediate result with another aggregator object and result() method to return final result.

GROUP_KEY() function

GROUP_KEY() function returns key or combination of keys for each group as an array. General syntax:

SELECT GROUP_KEY() FROM collection GROUP BY x, y


An example of simple GROUP_KEY() request, executed on "product" collection:

SELECT GROUP_KEY()
FROM product
GROUP BY category, availability

The response could look like:

{
  "results": [
    {
      "GROUP_KEY()": [
        "Bike",
        "Limited"
      ]
    },
    {
      "GROUP_KEY()": [
        "Urban_vehicle",
        "-"
      ]
    },
    {
      "GROUP_KEY()": [
        "Bicycles",
        "In Stock"
      ]
    },
    {
      "GROUP_KEY()": [
        "Bike",
        "In Stock"
      ]
    }
  ]
}


GROUP_KEY() also does not call grouping expressions repeatedly, that is why even temporary expressions (e.g. Math.random()) will return correct result.

Example of custom aggregation (request is build on data, used for JOIN examples):

AVG_custom = function() {
if (arguments.length > 0 && __cp_isNumber(arguments[0])) {
this.sum = parseFloat(arguments[0]);
this.count = 1;
} else {
this.sum = 0;
this.count = 0;
}
}

AVG_custom.prototype.combine = function(other) {
if (__cp_isNumber(other.sum) && __cp_isNumber(other.count)) {
this.sum += parseFloat(other.sum);
this.count += parseFloat(other.count);
}
return this;
}

AVG_custom.prototype.result = function() {
if (this.count == 0) {
return Number.NaN;
}
return this.sum / this.count;
}

SELECT GROUP_KEY(), new AVG_custom(price) AS avg_price
FROM sales
GROUP BY person_id