📣

TiDB Cloud Serverless が
Starter
に変わりました!このページは自動翻訳されたものです。
原文はこちらからご覧ください。

集計(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 kkパーセンタイルを表します)は、データセット内に 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)と同様に異なる値の数を数えますが、近似値を返します。3 BJKSTアルゴリズムを使用することで、べき乗分布を持つ大規模なデータセットを処理する際のメモリ消費量を大幅に削減します。さらに、カーディナリティの低いデータの場合、この関数は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 では、 DISTINCTORDER 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()関数の項目の最大数を設定します。

このページは役に立ちましたか?