SQL 分析でサポートされる集計関数 - IoT Platform - Alibaba Cloud ドキュメントセンター

集計関数は、複数の入力レコードを処理し、単一の出力値を返します。通常、group by 文と組み合わせて使用されます。

SQL 分析では、次の集計関数がサポートされています。

関数	機能
ANY_VALUE	指定された範囲からランダムな値を返します。
APPROX_DISTINCT	一意の入力値の概数を返します。
ARG_MAX	指定された列の最大値を含む行から、別の列の値を返します。
ARG_MIN	指定された列の最小値を含む行から、別の列の値を返します。
AVG	平均値を計算します。
BITWISE_AND_AGG	入力値のビット単位の AND 集計値を計算します。
BITWISE_OR_AGG	入力値のビット単位の OR 集計値を計算します。
COUNT	レコード数をカウントします。
COUNT_IF	指定された式が true であるレコードの数をカウントします。
MAX	最大値を計算します。
MAX_BY	指定された列の最大値を含む行から、別の列の値を返します。
MEDIAN	中央値を計算します。
MIN	最小値を計算します。
MIN_BY	指定された列の最小値を含む行から、別の列の値を返します。
STDDEV	母集団標準偏差を計算します。
STDDEV_SAMP	標本標準偏差を計算します。
SUM	合計を計算します。
WM_CONCAT	指定された区切り文字を使用して文字列を連結します。

ANY_VALUE

構文
```
any_value(<colname>)
```
説明

指定された範囲からランダムな値を返します。
説明

colname：必須。列名。任意のデータ型を指定できます。
戻り値

戻り値のデータ型は、colname の値と同じデータ型になります。colname の値が NULL の行は無視されます。
例
- 例 1：すべての従業員からランダムな従業員名を選択します。コマンドは次のとおりです。
```
select any_value(ename) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| SMITH      |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各グループからランダムな従業員名を選択します。コマンドは次のとおりです。
```
select deptno, any_value(ename) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK      |
| 20         | SMITH      |
| 30         | ALLEN      |
+------------+------------+
```

APPROX_DISTINCT

構文
```
approx_distinct(<colname>)
```
説明

指定された列内の一意の値の概数を計算します。
パラメーター

colname：必須。一意の値をカウントする列。
戻り値

BIGINT 型の値を返します。この関数の標準誤差は 5% です。colname の値が NULL の行は無視されます。

例

例 1：給与 (sal) 列の一意の値の概数を計算します。コマンドは次のとおりです。

select approx_distinct(sal) from emp;

結果は次のとおりです。

+-------------------+
| numdistinctvalues |
+-------------------+
| 12                |
+-------------------+

例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、給与 (sal) 列の一意の値の概数を計算します。コマンドは次のとおりです。

select deptno, approx_distinct(sal) from emp group by deptno;

結果は次のとおりです。

+------------+-------------------+
| deptno     | numdistinctvalues |
+------------+-------------------+
| 10         | 3                 |
| 20         | 4                 |
| 30         | 5                 |
+------------+-------------------+

ARG_MAX

構文

arg_max(<valueToMaximize>, <valueToReturn>)

説明

valueToMaximize が最大となる行の valueToReturn を返します。
パラメーター
- valueToMaximize：必須。任意のデータ型を指定できます。
- valueToReturn：必須。任意のデータ型を指定できます。
戻り値

戻り値のデータ型は valueToReturn と同じです。複数の行が最大値を持つ場合、それらの行の中からランダムに 1 つの値が返されます。valueToMaximize の値が NULL の行は無視されます。
例
- 例 1：給与が最も高い従業員の名前を返します。コマンドは次のとおりです。
```
select arg_max(sal, ename) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| KING       |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各グループで給与が最も高い従業員の名前を返します。コマンドは次のとおりです。
```
select deptno, arg_max(sal, ename) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+
```

ARG_MIN

構文

arg_min(<valueToMinimize>, <valueToReturn>)

説明

valueToMinimize が最小となる行の valueToReturn を返します。
パラメーター
- valueToMinimize：必須。任意のデータ型を指定できます。
- valueToReturn：必須。任意のデータ型を指定できます。
戻り値

戻り値のデータ型は valueToReturn と同じです。複数の行が最小値を持つ場合、それらの行の中からランダムに 1 つの値が返されます。valueToMinimize の値が NULL の行は無視されます。
例
- 例 1：給与が最も低い従業員の名前を返します。コマンドは次のとおりです。
```
select arg_min(sal, ename) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| SMITH      |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各グループで給与が最も低い従業員の名前を返します。コマンドは次のとおりです。
```
select deptno, arg_min(sal, ename) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+
```

AVG

構文
```
DECIMAL | DOUBLE  avg(<colname>)
```
説明

平均値を計算します。
パラメーター

colname：必須。列名。列の値は、計算のために DOUBLE に変換できる任意のデータ型にすることができます。
戻り値

colname の値が NULL の場合、その行は無視されます。戻り値の型は、次のように入力型によって決まります。

入力型

戻り値の型

TINYINT

DOUBLE

SMALLINT

DOUBLE

INT

DOUBLE

BIGINT

DOUBLE

FLOAT

DOUBLE

DOUBLE

DOUBLE

DECIMAL

DECIMAL
例
- 例 1：すべての従業員の平均給与 (sal) を計算します。コマンドは次のとおりです。
```
select avg(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 2222.0588235294117 |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の平均給与 (sal) を計算します。コマンドは次のとおりです。
```
select deptno, avg(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2916.6666666666665 |
| 20         | 2175.0     |
| 30         | 1566.6666666666667 |
+------------+------------+
```

BITWISE_AND_AGG

関数宣言
```
bigint bitwise_and_agg(bigint value)
```
説明

すべての入力値に対してビット単位の AND 演算を実行し、集計値を返します。
パラメーターの説明

value：必須。BIGINT 型の値。NULL 値は無視されます。
戻り値

BIGINT 型の値を返します。

例

select id, bitwise_and_agg(v) from
    values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

結果は次のとおりです。

+------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | 0          |
| 2          | NULL       |
+------------+------------+

BITWISE_OR_AGG

関数宣言
```
bigint bitwise_or_agg(bigint value)
```
説明

すべての入力値に対してビット単位の OR 演算を実行し、集計値を返します。
パラメーターの説明

value：必須。BIGINT 型の値。NULL 値は無視されます。
戻り値

BIGINT 型の値を返します。

例

select id, bitwise_or_agg(v) from
    values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

結果は次のとおりです。

+------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | 3          |
| 2          | NULL       |
+------------+------------+

COUNT

構文
```
bigint count([distinct|all] <colname>)
```
説明

レコード数をカウントします。
パラメーター
- distinct|all：任意。一意のレコードのみをカウントするかどうかを指定します。デフォルトは all で、すべてのレコードをカウントします。distinct が指定されている場合、一意の値のみがカウントされます。
- colname：必須。列名。任意のデータ型を指定できます。colname には * を指定でき、count(*) のように使用すると行の総数を返します。
戻り値

BIGINT 型の値を返します。colname の値が NULL の行は無視されます。
例
- 例 1：すべての部署の従業員の総数をカウントします。コマンドは次のとおりです。
```
select count(*) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 17         |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署 (deptno) の従業員数をカウントします。コマンドは次のとおりです。
```
select deptno, count(*) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 6          |
| 20         | 5          |
| 30         | 6          |
+------------+------------+
```
- 例 3：distinct を使用して、一意の部署の数をカウントします。コマンドは次のとおりです。
```
select count(distinct deptno) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 3          |
+------------+
```

COUNT_IF

構文
```
bigint count_if(boolean <expr>)
```
説明

expr の値が True であるレコードの数をカウントします。
説明

expr：必須。BOOLEAN 型の式。
戻り値

BIGINT 型の値を返します。expr が False であるか、expr で指定された列が NULL である行は無視されます。

例

select count_if(sal > 1000), count_if(sal <=1000) from emp;

結果は次のとおりです。

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 15         | 2          |
+------------+------------+

MAX

構文
```
max(<colname>)
```
説明

最大値を計算します。
パラメーターの説明

colname：必須。列は BOOLEAN を除く任意のデータ型にすることができます。
戻り値
戻り値のデータ型は colname と同じです。次のルールが適用されます。
- colname の値が NULL の行は無視されます。
- colname が BOOLEAN 型の場合、計算には使用できません。
例
- 例 1：すべての従業員の最高給与 (sal) を計算します。コマンドは次のとおりです。
```
select max(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 5000       |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の最高給与 (sal) を計算します。コマンドは次のとおりです。
```
select deptno, max(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 5000       |
| 20         | 3000       |
| 30         | 2850       |
+------------+------------+
```

MAX_BY

構文

max_by(<valueToReturn>,<valueToMaximize>)

説明

説明
MAX_BY 関数は ARG_MAX 関数と同じですが、パラメーターの順序が異なります。MAX_BY 関数は、オープンソースの構文との互換性のために追加されています。

valueToMaximize が最大となる行の valueToReturn を返します。
パラメーターの説明
- valueToMaximize：必須。任意のデータ型を指定できます。
- valueToReturn：必須。任意のデータ型を指定できます。
戻り値

戻り値のデータ型は valueToReturn と同じです。複数の行が最大値を持つ場合、それらの行の中からランダムに 1 つの値が返されます。valueToMaximize の値が NULL の行は無視されます。
例
- 例 1：給与が最も高い従業員の名前を返します。コマンドは次のとおりです。
```
select max_by(ename,sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| KING       |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各グループで給与が最も高い従業員の名前を返します。コマンドは次のとおりです。
```
select deptno, max_by(ename,sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+
```

MEDIAN

構文

double median(double <colname>)
decimal median(decimal <colname>)

説明

中央値を計算します。
説明

colname：必須。列名。列の値は DOUBLE 型または DECIMAL 型にすることができます。入力が STRING 型または BIGINT 型の場合、計算のために暗黙的に DOUBLE 型に変換されます。
戻り値

colname の値が NULL の場合、その行は無視されます。戻り値の型は、次のように入力型によって決まります。

入力型

戻り値の型

TINYINT

DOUBLE

SMALLINT

DOUBLE

INT

DOUBLE

BIGINT

DOUBLE

FLOAT

DOUBLE

DOUBLE

DOUBLE

DECIMAL

DECIMAL
例
- 例 1：すべての従業員の給与 (sal) の中央値を計算します。コマンドは次のとおりです。
```
select median(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 1600.0     |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の給与 (sal) の中央値を計算します。コマンドは次のとおりです。
```
select deptno, median(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2450.0     |
| 20         | 2975.0     |
| 30         | 1375.0     |
+------------+------------+
```

MIN

構文
```
min(<colname>)
```
説明

最小値を計算します。
説明。

colname：必須。列の値。値は BOOLEAN を除く任意のデータ型にすることができます。
戻り値
戻り値のデータ型は colname と同じです。次のルールが適用されます。
- colname の値が NULL の行は無視されます。
- colname が BOOLEAN 型の場合、計算には使用できません。
例
- 例 1：すべての従業員の最低給与 (sal) を計算します。コマンドは次のとおりです。
```
select min(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 800        |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の最低給与 (sal) を計算します。コマンドは次のとおりです。
```
select deptno, min(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300       |
| 20         | 800        |
| 30         | 950        |
+------------+------------+
```

MIN_BY

構文

min_by(<valueToReturn>,<valueToMinimize>)

説明

説明
MIN_BY 関数は ARG_MIN 関数と同じですが、パラメーターの順序が異なります。MIN_BY 関数は、オープンソースの構文との互換性のために追加されています。

valueToMinimize が最小となる行の valueToReturn を返します。
パラメーターの説明
- valueToMinimize：必須。任意のデータ型を指定できます。
- valueToReturn：必須。任意のデータ型を指定できます。
戻り値

戻り値のデータ型は valueToReturn と同じです。複数の行が最小値を持つ場合、それらの行の中からランダムに 1 つの値が返されます。valueToMinimize の値が NULL の行は無視されます。
例
- 例 1：給与が最も低い従業員の名前を返します。コマンドは次のとおりです。
```
 select min_by(ename,sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| SMITH      |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各グループで給与が最も低い従業員の名前を返します。コマンドは次のとおりです。
```
select deptno, min_by(ename,sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+
```

STDDEV

構文

double stddev(double <colname>)
decimal stddev(decimal <colname>)

説明

母集団標準偏差を計算します。
説明。

colname：必須。DOUBLE 型または DECIMAL 型。入力が STRING 型または BIGINT 型の場合、計算のために暗黙的に DOUBLE 型に変換されます。
戻り値

colname の値が NULL の場合、その行は無視されます。戻り値の型は、次のように入力型によって決まります。

入力型

戻り値の型

TINYINT

DOUBLE

SMALLINT

DOUBLE

INT

DOUBLE

BIGINT

DOUBLE

FLOAT

DOUBLE

DOUBLE

DOUBLE

DECIMAL

DECIMAL
例
- 例 1：すべての従業員の給与 (sal) の母集団標準偏差を計算します。コマンドは次のとおりです。
```
select stddev(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 1262.7549932628976 |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の給与 (sal) の母集団標準偏差を計算します。コマンドは次のとおりです。
```
select deptno, stddev(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1546.1421524412158 |
| 20         | 1004.7387720198718 |
| 30         | 610.1001739241043 |
+------------+------------+
```

STDDEV_SAMP

構文

double stddev_samp(double <colname>)
decimal stddev_samp(decimal <colname>)

説明

標本標準偏差を計算します。
パラメーター

colname：必須。列の値は DOUBLE 型または DECIMAL 型にすることができます。入力が STRING 型または BIGINT 型の場合、計算のために暗黙的に DOUBLE 型に変換されます。
戻り値

colname の値が NULL の場合、その行は無視されます。戻り値の型は、次のように入力型によって決まります。

入力型

戻り値の型

TINYINT

DOUBLE

SMALLINT

DOUBLE

INT

DOUBLE

BIGINT

DOUBLE

FLOAT

DOUBLE

DOUBLE

DOUBLE

DECIMAL

DECIMAL
例
- 例 1：すべての従業員の給与 (sal) の標本標準偏差を計算します。コマンドは次のとおりです。
```
select stddev_samp(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 1301.6180541247609 |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の給与 (sal) の標本標準偏差を計算します。コマンドは次のとおりです。
```
select deptno, stddev_samp(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1693.7138680032901 |
| 20         | 1123.3320969330487 |
| 30         | 668.3312551921141 |
+------------+------------+
```

SUM

構文

DECIMAL | DOUBLE | BIGINT  sum(<colname>)

説明

合計を計算します。
説明

colname：必須。列の値は DOUBLE、DECIMAL、または BIGINT 型にすることができます。入力が STRING 型の場合、計算のために暗黙的に DOUBLE 型に変換されます。
戻り値

colname の値が NULL の場合、その行は無視されます。戻り値の型は、次のように入力型によって決まります。

入力型

戻り値の型

TINYINT

BIGINT

SMALLINT

BIGINT

INT

BIGINT

BIGINT

BIGINT

FLOAT

DOUBLE

DOUBLE

DOUBLE

DECIMAL

DECIMAL
例
- 例 1：すべての従業員の給与 (sal) の合計を計算します。コマンドは次のとおりです。
```
select sum(sal) from emp;
```
  結果は次のとおりです。
```
+------------+
| _c0        |
+------------+
| 37775      |
+------------+
```
- 例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の給与 (sal) の合計を計算します。コマンドは次のとおりです。
```
select deptno, sum(sal) from emp group by deptno;
```
  結果は次のとおりです。
```
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 17500      |
| 20         | 10875      |
| 30         | 9400       |
+------------+------------+
```

WM_CONCAT

構文

string wm_concat(string <separator>, string <colname>)

説明

指定された separator を使用して colname の値を連結します。
パラメーター
- separator：必須。区切り文字として機能する STRING 型の定数。
- colname：必須。STRING 型。入力が BIGINT、DOUBLE、または DATETIME 型の場合、計算のために暗黙的に STRING 型に変換されます。
戻り値 (group by でグループ化した場合、グループ内で返される値はソートされません。)
STRING 型の値を返します。次のルールが適用されます。
- separator が STRING 定数でない場合、エラーが返されます。
- colname が STRING、BIGINT、DOUBLE、または DATETIME 型でない場合、エラーが返されます。
- colname の値が NULL の行は無視されます。
説明
select wm_concat(',', name) from table_name; 文において、table_name が空のコレクションである場合、この文は NULL を返します。

例

例 1：すべての従業員の名前 (ename) を連結します。コマンドは次のとおりです。

select wm_concat(',', ename) from emp;

結果は次のとおりです。

+------------+
| _c0        |
+------------+
| SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER,JACCKA,WELAN,TEBAGE |
+------------+

例 2：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、同じグループ内の従業員の名前 (ename) を連結します。コマンドは次のとおりです。

select deptno, wm_concat(',', ename) from emp group by deptno order by deptno;

結果は次のとおりです。

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK,KING,MILLER,JACCKA,WELAN,TEBAGE |
| 20         | SMITH,JONES,SCOTT,ADAMS,FORD |
| 30         | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+------------+------------+

例 3：group by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、同じグループ内の給与 (sal) を重複排除して連結します。コマンドは次のとおりです。

select deptno, wm_concat(distinct ',', sal) from emp group by deptno order by deptno;

結果は次のとおりです。

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300,2450,5000 |
| 20         | 1100,2975,3000,800 |
| 30         | 1250,1500,1600,2850,950 |
+------------+------------+

例 4： group by および order by と組み合わせて使用し、すべての従業員を部署 (deptno) ごとにグループ化し、同じグループ内の給与 (sal) を連結してソートします。コマンドは次のとおりです。

select deptno, wm_concat(',',sal) within group(order by sal) from emp group by deptno order by deptno;

結果は次のとおりです。

+------------+------------+
|deptno|_c1|
+------------+------------+
|10|1300,1300,2450,2450,5000,5000|
|20|800,1100,2975,3000,3000|
|30|950,1250,1250,1500,1600,2850|
+------------+------------+

入力型	戻り値の型
TINYINT	DOUBLE
SMALLINT	DOUBLE
INT	DOUBLE
BIGINT	DOUBLE
FLOAT	DOUBLE
DOUBLE	DOUBLE
DECIMAL	DECIMAL