集計 (GROUP BY) 関数
このドキュメントでは、TiDB でサポートされている集計関数の詳細について説明します。
サポートされている集計関数
このセクションでは、TiDB でサポートされている MySQL GROUP BY集計関数について説明します。
| 名前 | 説明 |
|---|---|
COUNT() | 返された行の数を返す |
COUNT(DISTINCT) | 異なる値の数を返す |
SUM() | 合計を返す |
AVG() | 引数の平均値を返す |
MAX() | 最大値を返す |
MIN() | 最小値を返す |
GROUP_CONCAT() | 連結された文字列を返す |
VARIANCE() 、 VAR_POP() | 母集団標準分散を返す |
STD() 、 STDDEV() 、 STDDEV_POP | 母標準偏差を返す |
VAR_SAMP() | 標本分散を返す |
STDDEV_SAMP() | サンプル標準偏差を返す |
JSON_ARRAYAGG() | 結果セットを単一のJSON配列として返します |
JSON_OBJECTAGG() | 結果セットをキーと値のペアを含む単一のJSONオブジェクトとして返します。 |
- 特に明記しない限り、グループ関数は
NULL値を無視します。 GROUP BY句を含まないステートメントでグループ関数を使用すると、すべての行をグループ化するのと同じになります。
さらに、TiDB は次の集計関数も提供します。
APPROX_PERCENTILE(expr, constant_integer_expr)この関数は
exprのパーセンタイルを返します。引数constant_integer_exprは、 5 から[1,100]までの範囲の定数整数であるパーセンタイル値を示します。パーセンタイル P k (kパーセンタイルを表す) は、データ セット内に P k以下の値が少なくともk%あることを示します。この関数は、
exprの戻り値の型として数値型と日付と時刻の種類をサポートします。その他の戻り値の型については、APPROX_PERCENTILENULLを返します。次の例は、
INT列の 50 パーセンタイルを計算する方法を示しています。DROP TABLE IF EXISTS t; CREATE TABLE t(a INT); INSERT INTO t VALUES(1), (2), (3);SELECT APPROX_PERCENTILE(a, 50) FROM t;+--------------------------+ | APPROX_PERCENTILE(a, 50) | +--------------------------+ | 2 | +--------------------------+ 1 row in set (0.00 sec)
GROUP_CONCAT()とAPPROX_PERCENTILE()関数を除き、前述のすべての関数はウィンドウ関数として機能します。
GROUP BY 修飾子
v7.4.0 以降、TiDB のGROUP BY句はWITH ROLLUP修飾子をサポートします。詳細については、 GROUP BY 修飾子参照してください。
SQL モードのサポート
TiDB は SQL モードONLY_FULL_GROUP_BYをサポートしており、有効にすると、あいまいな非集計列を含むクエリが拒否されます。たとえば、次のクエリは、 SELECTリストの非集計列「b」がGROUP BYステートメントに表示されないため、 ONLY_FULL_GROUP_BY有効になっている場合は無効です。
drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);
mysql> select a, b, sum(c) from t group by a;
+------+------+--------+
| a | b | sum(c) |
+------+------+--------+
| 1 | 2 | 3 |
| 2 | 2 | 3 |
| 3 | 2 | 3 |
+------+------+--------+
3 rows in set (0.01 sec)
mysql> set sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> select a, b, sum(c) from t group by a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
TiDB は現在、デフォルトでONLY_FULL_GROUP_BYモードを有効にしています。
MySQLとの違い
ONLY_FULL_GROUP_BYの現在の実装は、 MySQL 5.7の実装よりも厳密ではありません。たとえば、結果が "c" で順序付けられることを期待して次のクエリを実行するとします。
drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 1), (1, 2, 2), (1, 3, 1), (1, 3, 2);
select distinct a, b from t order by c;
結果を順序付けるには、まず重複を排除する必要があります。しかし、そのためにはどの行を保持する必要がありますか? この選択は「c」の保持値に影響し、それが順序付けに影響して、順序付けも任意になります。
MySQL では、 DISTINCTとORDER BY含むクエリは、 ORDER BY式のいずれかが以下の条件の少なくとも 1 つを満たさない場合、無効として拒否されます。
- 式は
SELECTリストの1に等しい - 式によって参照され、クエリの選択されたテーブルに属するすべての列は、
SELECTリストの要素です。
しかし、TiDB では上記のクエリは有効です。詳細については＃4254参照してください。
標準 SQL に対する別の TiDB 拡張機能では、 HAVING句でSELECTリスト内のエイリアス式を参照できます。たとえば、次のクエリは、テーブル "orders" に 1 回だけ出現する "name" 値を返します。
select name, count(name) from orders
group by name
having count(name) = 1;
TiDB 拡張機能では、集計列のHAVING句でエイリアスを使用できます。
select name, count(name) as c from orders
group by name
having c = 1;
標準 SQL では、 GROUP BY句で列式のみが許可されるため、次のようなステートメントは無効です。これは、「FLOOR(value/100)」が非列式であるためです。
select id, floor(value/100)
from tbl_name
group by id, floor(value/100);
TiDB は標準 SQL を拡張してGROUP BY句で非列式を許可し、前述のステートメントを有効と見なします。
標準 SQL では、 GROUP BY句でエイリアスを使用することはできません。TiDB は標準 SQL を拡張してエイリアスを許可するため、クエリを記述する別の方法は次のとおりです。
select id, floor(value/100) as val
from tbl_name
group by id, val;
関連するシステム変数
group_concat_max_len変数は、 GROUP_CONCAT()関数の項目の最大数を設定します。