Aggregate Functions

Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group.

The CData Virtuality Server supports the following aggregate functions:

To view the full table, click the expand button in its top right corner

SELECT XMLELEMENT("Department",
   XMLAGG(XMLELEMENT("Names", 
   a.FirstName||' '|| a.LastName)
   ORDER BY LastName))
   as "Name_List"     
FROM data_source.addresses a
WHERE a.departmentID = 3;

SELECT jsonArray_Agg(col1 order by col1 nulls first)

// The return may be as follows:
[null,null,1,2,3]

string_agg(col1, ',' ORDER BY col1 ASC)

// The return may be as follows:
'a,b,c'

SELECT array_agg(a ORDER BY b DESC) FROM test_tables.test_a;;
 
SELECT array_agg("name") from "SYSADMIN.Connections" ;;

Syntax Rules

Some aggregate functions may contain a keyword 'DISTINCT' before the expression, indicating that duplicate expression values should be ignored. DISTINCT is not allowed in COUNT(*) and is not meaningful in MIN or MAX (the result would be unchanged), but it can be used in COUNT, SUM, and AVG.

• Aggregate functions cannot be used in FROM, GROUP BY, or WHERE clauses without an intervening query expression;

• Aggregate functions cannot be nested within another aggregate function without an intervening query expression;

• Aggregate functions may be nested inside other functions;

• Any aggregate function may take an optional FILTER clause of the following form:

  CODE

  FILTER ( WHERE condition )

  The condition may be any boolean value expression that does not contain a subquery or a correlated variable. The filter will logically be evaluated for each row prior to the grouping operation. If FALSE, the aggregate function will not accumulate a value for the given row;

• User-defined aggregate functions need ALL specified if no other aggregate-specific constructs are used to distinguish the function as an aggregate rather than a normal function. For more information on aggregates, see the sections on GROUP BY and HAVING.

Window Functions

The CData Virtuality Server supports ANSI SQL 2003 window functions. A window function allows an aggregate function to be applied to a subset of the result set without the need for a GROUP BY clause. A window function is similar to an aggregate function, but requires the use of an OVER clause or window specification.

Usage

aggregate|ranking OVER ([PARTITION BY ...] [ORDER BY ...] [<FRAME-CLAUSE>])

aggregate can be any aggregate function. Ranking can be one of ROW_NUMBER() , RANK(), DENSE_RANK().

Syntax Rules

• Window functions can only appear in the SELECT and ORDER BY clauses of a query expression;

• Window functions cannot be nested in one another;

• Partitioning and order by expressions cannot contain subqueries or outer references;

• The ranking (ROW_NUMBER, RANK, DENSE_RANK) functions require the use of the window specification ORDER BY clause;

• An XMLAGG ORDER BY clause cannot be used when windowed;

• The window specification ORDER BY clause cannot reference alias names or use positional ordering;

• Windowed aggregates may not use DISTINCT if the window specification is ordered;

• The Frame Clause can only be used in combination with the Partition Clause (at least one of PARTITION BY or ORDER BY should be present).

Frame Clause

The frame clause allows the user to specify a dynamic group of rows, or a range, inside the partition (analogous to a sliding frame in a window). It can be used in two ways:

• [ RANGE | ROWS ] frame_start

• [ RANGE | ROWS ] BETWEEN frame_start AND frame_end

Where frame_start and frame_end can be one of:

• UNBOUNDED PRECEDING

• value PRECEDING (ROWS only)

• CURRENT ROW

• value FOLLOWING (ROWS only)

• UNBOUNDED FOLLOWING

Restrictions are that frame_start cannot be UNBOUNDED FOLLOWING, frame_end cannot be UNBOUNDED PRECEDING, and the frame_end choice cannot appear earlier in the above list than the frame_start choice. For example, RANGE BETWEEN CURRENT ROW AND value PRECEDING is not allowed.

Examples of Usage

Let us assume we have the following table called my_table:

Examples of Frame Clause usage

SELECT x, SUM(y) OVER (PARTITION BY y ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_column 
FROM data_source.my_table;
 
-- Result:
-- |-----------|---------------|
-- |     x     | window_column |
-- |-----------|---------------|
-- |     1     |       1       |
-- |     2     |       2       |
-- |     3     |       3       |
-- |     4     |       2       |
-- |     5     |       3       |
-- |-----------|---------------|
 
 
SELECT x, COUNT(y) OVER (PARTITION BY y RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS window_column 
FROM data_source.my_table;
 
-- Result:
-- |-----------|---------------|
-- |     x     | window_column |
-- |-----------|---------------|
-- |     1     |       3       |
-- |     2     |       3       |
-- |     3     |       3       |
-- |     4     |       1       |
-- |     5     |       1       |
-- |-----------|---------------|
 
 
SELECT x, y*100/SUM(y) OVER (PARTITION BY y RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_column 
FROM data_source.my_table;
 
-- Result:
-- |-----------|---------------|
-- |     x     | window_column |
-- |-----------|---------------|
-- |     1     |       33      |
-- |     2     |       33      |
-- |     3     |       33      |
-- |     4     |       100     |
-- |     5     |       100     |
-- |-----------|---------------|

Function Definitions

Processing

Window functions are logically processed just before creating the output from the SELECT clause. Window functions can use nested aggregates if a GROUP BY clause is present. The is no guaranteed effect on the output ordering from the presence of window functions. The SELECT statement must have an ORDER BY clause to have a predictable ordering.

The CData Virtuality Server will process all window functions with the same window specification together. Generally, a full pass over the row values coming into the SELECT clause will be required for each unique window specification. For each window specification, the values will be grouped according to the PARTITION BY clause. If no PARTITION BY clause is specified, the entire input is treated as a single partition. The output value is determined based upon the current row value, its peers (that is, rows that are the same with respect to their ordering), and all prior row values based upon ordering in the partition. The ROW_NUMBER function will assign a unique value to every row regardless of the number of peers.

Example Windowed Results

SELECT name, salary, max(salary) over (partition by name) as max_sal,
          rank() over (order by salary) as rank, dense_rank() over (order by salary) as dense_rank,
          row_number() over (order by salary) as row_num 
FROM data_source.employees

See Also

Duplicate Removal with ROW_NUMBER() and PARTITION to see how window functions can be used for removing duplicates