集計(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
は、[1,100]
の範囲の定数整数であるパーセンタイル値を示します。パーセンタイル P k (k
パーセンタイルを表します)は、データセット内に P k以下の値が少なくともk%
あることを示します。この関数は、
expr
の戻り値の型として数値型と日付と時刻の種類のみをサポートします。その他の戻り値の型については、APPROX_PERCENTILE
NULL
のみを返します。次の例は、
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)APPROX_COUNT_DISTINCT(expr, [expr...])
この関数は、
COUNT(DISTINCT)
と同様に異なる値の数を数えますが、近似値を返します。3BJKST
アルゴリズムを使用することで、べき乗分布を持つ大規模なデータセットを処理する際のメモリ消費量を大幅に削減します。さらに、カーディナリティの低いデータの場合、この関数はCPU使用率を効率的に維持しながら高い精度を実現します。次の例は、この関数の使用方法を示しています。
DROP TABLE IF EXISTS t; CREATE TABLE t(a INT, b INT, c INT); INSERT INTO t VALUES(1, 1, 1), (2, 1, 1), (2, 2, 1), (3, 1, 1), (5, 1, 2), (5, 1, 2), (6, 1, 2), (7, 1, 2);SELECT APPROX_COUNT_DISTINCT(a, b) FROM t GROUP BY c;+-----------------------------+ | approx_count_distinct(a, b) | +-----------------------------+ | 3 | | 4 | +-----------------------------+ 2 rows in set (0.00 sec)
GROUP_CONCAT()
、およびAPPROX_COUNT_DISTINCT
APPROX_PERCENTILE()
を除き、前述のすべての関数はウィンドウ関数として機能します。
GROUP BY修飾子
TiDB v7.4.0以降、 GROUP BY
句はWITH ROLLUP
修飾子をサポートします。詳細については、 GROUP BY修飾子参照してください。
SQLモードのサポート
TiDBはSQLモードONLY_FULL_GROUP_BY
をサポートしており、有効にすると、曖昧な非集計列を含むクエリを拒否します。例えば、次のクエリはONLY_FULL_GROUP_BY
が有効になっている場合、 SELECT
のリストにある非集計列「b」が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」に一度だけ出現する「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()
関数の項目の最大数を設定します。