AVG¶

Description

The AVG function returns the average of the non-null values of a field of the table.

If all the values of the field are NULL, the function returns NULL.

Syntax

AVG( <expression> ) : double

• expression. Required. The type of the expression has to be int, long, float, double or decimal.

Examples

Example 1

Consider the following view ITEMS:

SELECT AVG(price) AS average_price
FROM ITEMS

Example 2

Consider the following view ITEMS_2:

SELECT AVG(price) AS average_price
FROM ITEMS_2

COUNT¶

Description

The COUNT function returns the number of tuples of the result of a selection operation.

If the parameter is *, it returns the number of tuples.

If the parameter is a field, it returns the number of non-null values

It is applicable to any type of field.

This function can be used in queries without a GROUP BY clause, but in that case, it can only be used with *.

Syntax

COUNT( <count parameter> )

<count parameter> ::=
    <field name:identifier>
  | *

• count parameter. Required. It can be either a field name or *.

Examples

Consider the following view ITEMS:

Example 1

SELECT COUNT(*)
FROM items

Example 2

SELECT COUNT(price)
FROM items

FIRST¶

Description

The FIRST function returns the first value of a field of each group of values.

This function ignores the ALL/DISTINCT modifier.

Syntax

FIRST ( <field name:identifier> )

• field name. Required. A field of the view.

Examples

Consider the following view V:

Example 1

SELECT FIRST(b)
FROM v

Example 2

SELECT a, FIRST(b)
FROM v
GROUP BY a

GROUP_CONCAT¶

Description

The GROUP_CONCAT function returns, for each group, a string with the concatenation of all the field/fields values of each group.

Syntax

GROUP_CONCAT( [ DISTINCT | ALL ] <field name:identifier>)
GROUP_CONCAT( [ <row separator:text> [, <field separator:text> ] ],
    <field name:identifier> [, <field name:identifier>]* )
GROUP_CONCAT( <ignore null:boolean> , <row separator:text>, <field separator:text>,
    <field name:identifier> [, <field name:identifier>]* )

• ignore null. Optional. If true and any of the fields field name of a row are NULL, GROUP_CONCAT ignores all the fields of that row. If false, no rows are ignored and NULL values are treated as empty characters. The default value is true.

• row separator. Optional. Literal used to separate the values of each row. Default value: ,.

• field separator. Optional. Literal used to separate the values of the fields of the same row. The default value is a whitespace.

• field name. Required. Field which contains the values to concatenate.

Examples

Consider the following view V:

Example 1

SELECT A, GROUP_CONCAT(':', c)
FROM v
GROUP BY a

Example 2

SELECT A, GROUP_CONCAT(':', ';', b, c)
FROM v
GROUP BY a

As the field B is NULL in the third row, GROUP_CONCAT ignores all the fields of that row. That is, it ignores the value three.

Example 3

SELECT a, GROUP_CONCAT(false, ':', ';', b, c)
FROM v
GROUP BY a

As the parameter ignoreNulls is false, GROUP_CONCAT does not ignore the value of the field C of the third row (three), even if the value of the field B of that row is NULL. In this case, NULL values are treated like empty characters.

LAST¶

Description

The LAST function returns the last value of a field of each group of values.

This function ignores the ALL/DISTINCT modifier.

Syntax

LAST ( <field name:identifier> )

• field name. Required. Field name of the view.

Examples

Consider the following view V:

Example 1

SELECT LAST(b)
FROM v

Example 2

SELECT a, LAST(b)
FROM v
GROUP BY a

LIST¶

Description

The LIST function returns an array with all the values of a specified field.

It has the same behavior as the function NEST (section NEST) when invoked with a single field as argument.

Syntax

LIST ( <field name:identifier> )

• field name. Required. Field of the view.

Examples

Consider the following view V:

Example 1

SELECT LIST(b)
FROM v

Example 2

SELECT a, LIST(b)
FROM v
GROUP BY a

MAX¶

Description

The MAX function returns the highest value of a field for each group of values.

This function ignores the ALL/DISTINCT modifier.

Syntax

MAX ( <expression> )

• expression. Required. Expression of type date (deprecated type), intervaldaysecond, intervalyearmonth, text, time, timestamp, timestamptz or any of the numeric data types.

The return type is the type of the input expression.

When the field is of text type, the function compares the Unicode value of each character of the value.

Examples

Consider the following view V:

Example 1

SELECT MAX(b)
FROM v

Example 2

SELECT a, MAX(b)
FROM v
GROUP BY a

Example 3

SELECT MAX(c)
FROM v

In this example, the result is “denodo” because the first letter has the highest Unicode value of all the values: d = 100, V = 86, etc.

MEDIAN¶

Description

The MEDIAN function returns the middle number of a field for each group of values, taken as the average of the two middle numbers when the group has an even number of values.

This function does not take into account NULL values to calculate the result.

This function ignores the modifiers ALL and DISTINCT.

Syntax

MEDIAN ( <expression> )

• expression. Required. Expression of type date (deprecated type) , time, timestamp, timestamptz or any of the numeric data types.

Examples

Consider the following view employee:

Example 1

SELECT MEDIAN(salary), MEDIAN(start_date)
FROM employee

Example 2

SELECT first_name, MEDIAN(salary), MEDIAN(start_date)
FROM employee
GROUP BY first_name

MIN¶

Description

The MIN function returns the lowest value of a field for each group of values.

This function ignores the ALL/DISTINCT modifier.

Syntax

MIN ( <expression> )

• expression. Required. Expression of type date (deprecated type), intervaldaysecond, intervalyearmonth, text, time, timestamp, timestamptz or any of the numeric data types.

When the field is of text type, the function compares the Unicode value of each character of the value.

Examples

Consider the following view V:

Example 1

SELECT MIN(b)
FROM V

Example 2

SELECT a, MIN(b)
FROM v
GROUP BY a

Example 3

SELECT MIN(c)
FROM v

In this example, the result is “Data Catalog” because the first letter has the lowest Unicode value of all the values: D = 68, d = 100, V = 86, etc.

NEST¶

Description

The NEST function returns an array with the values of the selected fields. Its result is inverse to the result of the FLATTEN views (see section FLATTEN View (Flattening Data Structures) for more information about FLATTEN views).

Syntax

NEST( <field name:identifier> [, <field name:identifier> ]*):array
NEST(*)

• field name. The name of a field. Using (*) is equivalent to pass all the fields of the view to the function.

Example

Consider the view V:

SELECT int_sample, NEST(text_sample, register_sample) AS nest_sample
FROM V
GROUP BY int_sample;

STDEV¶

Description

The STDEV function returns the sample standard deviation of each group of values.

This function does not take into account NULL values to calculate the result.

Syntax

STDEV( <expression> ) : decimal

• expression. Required. Any numeric data type.

Example

Consider the view sales_by_region:

SELECT region, STDEV(revenue)
FROM revenue
GROUP BY region

STDEVP¶

Description

The STDEVP function returns the population standard deviation of each group of values.

This function does not take into account NULL values to calculate the result.

Syntax

STDEVP( <expression> ) : decimal

• expression. Required. Any numeric data type.

Example

Consider the view sales_by_region:

SELECT region, STDEVP(revenue)
FROM revenue
GROUP BY region

SUM¶

Description

The SUM function returns the sum of all non-null values of a field for each group of values.

Syntax

SUM ( <expression> )

• expression. Required. Any numeric data type.

The return type is the type of the input expression.

Examples

Consider the following view V:

Example 1

SELECT SUM(b)
FROM v

Example 2

SELECT a, SUM(b)
FROM v
GROUP BY a

VAR¶

Description

The VAR function returns the sample variance of each group of values.

This function does not take into account NULL values to calculate the result.

Syntax

VAR( <expression> ) : decimal

• expression. Required. Any numeric data type.

Example

Consider the view sales_by_region:

SELECT region, VAR(revenue)
FROM revenue
GROUP BY region

VARP¶

Description

The VARP function returns the population variance of each group of values.

This function does not take into account NULL values to calculate the result.

Syntax

VARP( <expression> ) : double

• expression. Required. Any numeric data type.

Example

Consider the view sales_by_region:

SELECT region, VARP(revenue)
FROM revenue
GROUP BY region