Aggregate 関数は、明示的または暗黙的なGROUP BYによって生成されたグループから一連の値を受け取り、グループから計算された単一のスカラー値を返します。

CData Virtuality Server は以下の集約機能をサポートしています：

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


Function

Description

COUNT(*)

Counts the number of values (including nulls and duplicates) in a group

COUNT(x)

Counts number of values (excluding nulls) in a group

SUM(x)

Sum of values (excluding nulls) in a group

AVG(x)

Average of values (excluding nulls) in a group

MIN(x)

Minimum value in a group (excluding null)

MAX(x)

Maximum value in a group (excluding null)

ANY(x)/SOME(x)

Returns TRUE if any value in the group is TRUE (excluding null)

EVERY(x)

Returns TRUE if every value in the group is TRUE (excluding null)

VAR_POP(x)

Biased variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/count(x); returns a double; null if count = 0

VAR_SAMP(x)

Sample variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/(count(x) - 1); returns a double; null if count < 2

STDDEV_POP(x)

Standard deviation (excluding null) logically equals SQRT(VAR_POP(x))

STDDEV_SAMP(x)

Sample standard deviation (excluding null) logically equals SQRT(VAR_SAMP(x))

TEXTAGG(FOR (expression [as name], ... [DELIMITER char

[QUOTE char] [HEADER] [ENCODING id] [ ORDER BY ... ])

CSV text aggregation of all expressions in each row of a group.

  • When DELIMITER is not specified, by default comma(,) is used as delimiter;
  • Double quotes(") are the default quote character;
  • Use QUOTE to specify a different value;
  • All non-null values will be quoted;
  • If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group;

  • This aggregation returns a blob

    SELECT
    TEXTAGG( a.FirstName AS FirstName, a.LastName AS LastName
    DELIMITER ',' HEADER ORDER BY a.LastName )
    AS "Name_List"
    FROM data_source.addresses a
    WHERE a.departmentID = 3;

XMLAGG(xml_expr [ ORDER BY ... ])

XML concatenation of all XML expressions in a group (excluding null). The ORDER BY clause cannot reference alias names or use positional ordering
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;

JSONARRAY_AGG(x [ORDER BY …])

Creates a JSON array result as a Clob including null value. The ORDER BY clause cannot reference alias names or use positional ordering. See also the JSONArray function.
SELECT jsonArray_Agg(col1 order by col1 nulls first)
 
// The return may be as follows:
[null,null,1,2,3]

STRING_AGG(x, delim)

Creates a lob result from the concatenation of x using the delimiter delim.

  • If either argument is null, no value is concatenated;
  • Both arguments are expected to be characters (string/clob) or binary (varbinary, blob), and the result will be clob or blob, respectively;
  • Expressions are allowed as delimiters only for DBMSs which have such capability (PostgreSQL and CData Virtuality Server);
  • DISTINCT and ORDER BY are allowed in STRING_AGG;
  • Whether DISTINCT is pushed down to underlying DBMS or not depends on its capabilities (Oracle and Redshift do not allow it).
string_agg(col1, ',' ORDER BY col1 ASC)
 
// The return may be as follows:
'a,b,c'

ARRAY_AGG(x [ORDER BY ...])

Creates an array with a base type matching the expression x.

  • Produces results that depend on the ordering of the input rows;
  • When using such an aggregate, the optional ORDER BY clause can be used to specify the desired ordering;

  • The ORDER BY clause cannot reference alias names or use positional ordering.

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

AGG(DISTINCT arg ... [ ORDER BY ... ])

User-defined aggregate function 

Syntax Rules

一部の集約関数には、式の前にキーワード ' DISTINCT ' が含まれている場合があります。これは、重複する式の値は無視されるべきであることを示します。DISTINCTはCOUNT(*)では使用できず、MINMAXでは意味を持ちません（結果は変更されません）が、COUNT、SUM、AVGでは使用できます。

  • 集約関数は、FROM、GROUP BY、WHERE、クエリ式が介在しない句では使用できません；
  • 集約関数は、クエリ式を挟まずに別の集約関数の中に入れ子にすることはできません；
  • 集約関数は、他の関数の中に入れ子にすることができます；

  • 任意の集約関数は、オプションで次の形式のFILTER句を取ることができます：

    FILTER ( WHERE condition )

    条件は、サブクエリや相関変数を含まないブール値式であれば何でもかまいません。フィルタは、グループ化操作の前に、各行に対して論理的に評価されます。FALSEの場合、集約関数は指定された行の値を累積しません；

  • ユーザー定義の集約関数は、通常の関数ではなくAggregate であることを区別するために、他のAggregate 固有の構成要素が使用されていない場合、ALLを指定する必要があります。アグリゲートの詳細については、GROUP BY、HAVING セクションを参照してください。

Window Functions

CData Virtuality Server は ANSI SQL 2003 Window 関数をサポートしています。Window 関数は、GROUP BY句を必要とせずに、結果セットのサブセットに集約関数を適用することを可能にします。Window 関数はAggregate関数と似ていますが、OVER句またはWindow指定を使用する必要があります。

Usage

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

aggregateは任意の集約関数です。順位は ROW_NUMBER() , RANK() , DENSE_RANK()のいずれかになります。

Syntax Rules

  • Window 関数は、クエリ式のSELECTおよびORDER BY句でのみ使用できます；
  • Window 関数を入れ子にすることはできません；
  • パーティショニングとorder by 式にサブクエリまたは外部参照を含めることはできません；
  • ランキング（ROW_NUMBER, RANK, DENSE_RANK）関数は、ウィンドウ指定ORDER BY句の使用を必要とします；
  • XMLAGG ORDER BY句は、ウィンドウの場合は使用できません；
  • ウィンドウ仕様ORDER BY句は、エイリアス名を参照したり、位置順序を使用したりすることはできません；
  • WINDOW 仕様が注文されている場合、WINDOW 集合体はDISTINCTを使用することはできません；
  • Frame句は、PARTITION句 （PARTITION BY、ORDER BYのうち少なくとも1つが存在すること）と組み合わせてのみ使用できます。

Frame Clause

Frame句によって、ユーザはパーティション内の行の動的なグループ、もしくは範囲を指定することができます（ウィンドウのスライドフレームに似ています）。使い方は2通り：

  • [ RANGE | ROWS ] frame_start
  • [ RANGE | ROWS ] BETWEEN frame_start AND frame_end

 frame_startおよび frame_endのいずれかを指定します：

  • UNBOUNDED PRECEDING
  • value PRECEDING (ROWS only)
  • CURRENT ROW
  • value FOLLOWING (ROWS only)

  • UNBOUNDED FOLLOWING

frame_startを UNBOUNDED FOLLOWINGにすることはできず、frame_end を UNBOUNDED PRECEDINGにすることはできず、frame_end  をframe_start よりも前にすることはできません。例えば、RANGE BETWEEN CURRENT ROW AND value PRECEDING は許されません。

Examples of Usage

my_tableというテーブルがあるとします：

x

y

1

1

2

1

3

1

4

2

5

3

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

Function

Description

ROW_NUMBER()

Functionally same as COUNT(*) with the same window specification. Assigns a number to each row in a partition starting at 1

RANK()

Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is equal to the count of prior rows

DENSE_RANK()

Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential

LEAD(scalar_expression [, offset [, default]])

Returns scalar_expression evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default (which must be of the same type as scalar_expression). Both offset and default are evaluated for the current row. If omitted, offset defaults to 1 and default to null

LAG(scalar_expression [, offset [, default]])

Returns scalar_expression evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of the same type as scalar_expression). Both offset and default are evaluated for the current row. If omitted, offset defaults to 1 and default to null

FIRST_VALUE(scalar_expression)

Returns scalar_expression evaluated at the row that is the first row of the window frame

LAST_VALUE(scalar_expression) 

Returns scalar_expression evaluated at the row that is the last row of the window frame


Processing

Window 関数は、SELECT句からの出力を作成する直前に論理的に処理されます。GROUP BY句がある場合、Window 関数は入れ子になった集約を使用することができます。Window関数の存在による出力順序への影響は保証されていません。SELECTステートメントには、ORDER BY句がないと、順序が予測できません。

CData Virtuality Server は同じWindow 指定を持つすべての Window 関数を一緒に処理します。一般的に、SELECT句に入力される行値に対する完全なパスは、各ユニークなウィンドウ指定に対して必要となります。各Window指定に対して、PARTITION BY句に従って値がグループ化されます。PARTITION BY句が指定されない場合、入力全体が1つのパーティションとして扱われます。出力値は、現在の行値、そのピア（つまり、順序に関して同じ行）、および、パーティション内の順序に基づくすべての前の行値に基づいて決定されます。ROW_NUMBER関数は、ピアの数に関係なく、すべての行に一意の値を割り当てます。

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

name

salary

max_sal

rank

dense_rank

row_num

John

100000

100000

2

2

2

Henry

50000

50000

5

4

5

John

60000

100000

3

3

3

Suzie

60000

150000

3

3

4

Suzie

150000

150000

1

1

1

See Also

Duplicate Removal with ROW_NUMBER() and PARTITION 重複を削除するためにWindow 関数がどのように使用できるかについては、こちらをご覧ください。