集計 (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_OBJECTAGG(key, value)結果セットをキーと値のペアを含む単一の 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)

GROUP_CONCAT()APPROX_PERCENTILE()関数を除き、前述のすべての関数がウィンドウ関数として機能します。

GROUP BY 修飾子

v7.4.0 以降、TiDB のGROUP BY句はWITH ROLLUP修飾子をサポートします。詳細については、 GROUP BY 修飾子を参照してください。

SQLモードのサポート

TiDB は SQL モードONLY_FULL_GROUP_BYをサポートしており、有効にすると、TiDB はあいまいな非集計列を含むクエリを拒否します。たとえば、リスト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 では、 ORDER BYの式が次の条件の少なくとも 1 つを満たさない場合、 DISTINCTORDER BYを含むクエリは無効として拒否されます。

  • 式はリスト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()関数の最大項目数を設定します。

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