集計 (GROUP BY) 関数

このドキュメントでは、TiDB でサポートされている集計関数について詳しく説明します。

サポートされている集計関数

このセクションでは、TiDB でサポートされている MySQL GROUP BY集計関数について説明します。

• 特に明記しない限り、グループ関数は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 修飾子

TiDB は現在、 WITH ROLLUPなどのGROUP BY修飾子をサポートしていません。将来的にサポートを追加する予定です。 TiDB #4250を参照してください。

SQL モードのサポート

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

• 式はSELECTリストの 1 つと等しい
• 式によって参照され、クエリの選択されたテーブルに属するすべての列は、 SELECTリストの要素です

しかし、TiDB では、上記のクエリは合法です。詳細については、 #4254を参照してください。

標準 SQL に対する TiDB のもう 1 つの拡張機能では、 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;

サポートされていない集計関数

次の集計関数は現在、TiDB ではサポートされていません。 TiDB #7623で進行状況を追跡できます。

• JSON_ARRAYAGG

関連するシステム変数

group_concat_max_len変数は、 GROUP_CONCAT()関数の項目の最大数を設定します。