MySQL グループ化クエリと集計関数

MySQL グループ化クエリと集計関数

概要

私たちは、双十一に天猫で化粧品を購入する人の平均支出額を知りたい（商品の価格帯を見つけるのに役立つかもしれない）、または異なる年齢層における化粧品消費の割合を知りたい（商品の在庫を見積もるのに役立つかもしれない）といったシナリオによく遭遇すると思います。

このとき、グループクエリが必要になります。グループクエリの目的は、データを複数の論理グループ（化粧品を購入する人が 1 つのグループ、化粧品を購入するさまざまな年齢層の人もグループ）に分割し、各グループに対して集計計算を実行することです。

グループクエリの構文形式は次のとおりです。

 cname、group_fun、...をtnameから選択します[条件]
 group_expression [group_condition を持つ] でグループ化します。

説明すると:

1. group_fun は集計関数を表します。これは、グループ化されたデータに対して集計計算を実行する関数を指します。

2. group_expression はグループ化式を表します。複数の式が許可され、カンマで区切られます。

3. group_condition グループ化後、グループ化されたデータを条件付きでフィルタリングするプロセス。

4. グループ化構文では、select の後に表示されるフィールドは、group by の後のフィールドまたは集計関数の列のいずれかです。他のタイプでは例外が報告されます。これについては、次のコンテンツで詳しく説明します。

グループ化について説明する前に、グループ化クエリ構文形式の重要な部分である集計関数について見てみましょう。データを実際に取得せずに要約する必要があることがよくあるため、MySQL では特別な関数が提供されています。これらの関数を使用して、分析やレポート生成に必要なデータを計算できます。

集計関数

集計関数にはいくつかの種類があります。

関数例示する
平均()指定されたフィールドの平均値を返します
カウント（）クエリ結果の行数を返します
最大()指定されたフィールドの最大値を返します
最小値()指定されたフィールドの最小値を返します
和（）指定されたフィールドの合計を返します

AVG() 関数

AVG() は、テーブル内の行数をカウントし、その列の値を合計して、特定の列の平均値を求めます。 AVG() は、すべての列の平均を返すために使用することも、特定の列または行の平均を返すために使用することもできます。

次の例では、ユーザー テーブル内のユーザーの平均年齢を返します。

mysql> user2 から * を選択します。
+----+--------+-------+---------+-----+
| ID | 名前 | 年齢 | 住所 | 性別 |
+----+--------+-------+---------+-----+
| 1 | ブランド | 21 | 福州 | 1 |
| 2 | ヘレン | 20 | 泉州 | 0 |
| 3 | ソル | 21 | 厦門 | 0 |
| 4 | 翁 | 33 | 貴州 | 1 |
| 5 | セリーナ | 25 | NULL | 0 |
| 6 | アニー | 23 | 上海 | 0 |
| 7 | そして | 24 | 上海 | 1 |
| 8 | 晴れ | NULL | 貴州省 | 0 |
+----+--------+-------+---------+-----+
8行セット

mysql> user2 から avg(age) を選択します。
+----------+
| 平均(年齢) |
+----------+
| 23.8571 |
+----------+
セット内の1行

注記：

1. AVG() は、特定の数値列の平均を決定するためにのみ使用できます。
2. AVG() 関数は NULL 列値を持つ行を無視するため、上図の年齢値は累積後に 8 ではなく 7 で割られます。

COUNT() 関数

COUNT() 関数はカウントします。 COUNT() を使用すると、テーブル内で条件を満たす行の数を判別できます。

count を表現する方法は、count(*)、count(特定のフィールド)、count(定数) の 3 つがあります。次に、count(*) と count(cname) の使用方法を示します。

mysql> user2 から * を選択します。
+----+--------+-------+---------+-----+
| ID | 名前 | 年齢 | 住所 | 性別 |
+----+--------+-------+---------+-----+
| 1 | ブランド | 21 | 福州 | 1 |
| 2 | ヘレン | 20 | 泉州 | 0 |
| 3 | ソル | 21 | 厦門 | 0 |
| 4 | 翁 | 33 | 貴州 | 1 |
| 5 | セリーナ | 25 | NULL | 0 |
| 6 | アニー | 23 | 上海 | 0 |
| 7 | そして | 24 | 上海 | 1 |
| 8 | 晴れ | NULL | 貴州省 | 0 |
+----+--------+-------+---------+-----+
8行セット

mysql> user2 から sex=0 の count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 5 |
+----------+
セット内の1行

mysql> user2 から sex=0 の count(age) を選択します。
+------------+
| カウント(年齢) |
+------------+
| 4 |
+------------+
セット内の1行

ご覧のとおり、どちらも女性ユーザーの数を取得します。age に null 値が含まれているため、count(*) は count(age) より 1 つ多くなります。

したがって、列名を指定すると、指定された列の値が空の行は COUNT() 関数によって無視されますが、COUNT() 関数でアスタリスク (*) が使用されている場合は無視されません。

MAX() および MIN() 関数

MAX() は指定された列の最大値を返し、MIN() は指定された列の最小値を返します。

mysql> user2 から * を選択します。
+----+--------+-------+---------+-----+
| ID | 名前 | 年齢 | 住所 | 性別 |
+----+--------+-------+---------+-----+
| 1 | ブランド | 21 | 福州 | 1 |
| 2 | ヘレン | 20 | 泉州 | 0 |
| 3 | ソル | 21 | 厦門 | 0 |
| 4 | 翁 | 33 | 貴州 | 1 |
| 5 | セリーナ | 25 | NULL | 0 |
| 6 | アニー | 23 | 上海 | 0 |
| 7 | そして | 24 | 上海 | 1 |
| 8 | 晴れ | NULL | 貴州省 | 0 |
+----+--------+-------+---------+-----+
8行セット

mysql> user2 から max(age),min(age) を選択します。
+----------+-----------+
| 最大(年齢) | 最小(年齢) |
+----------+-----------+
| 33 | 20 |
+----------+-----------+
セット内の1行

注: 同様に、MAX() 関数と MIN() 関数は、列の値が NULL の行を無視します。

SUM関数

SUM() は、指定した列の値の合計 (合計) を返すために使用されます。以下は、すべての年齢の合計を返します。ここでも、null 値は無視されます。

mysql> user2 から * を選択します。
+----+--------+-------+---------+-----+
| ID | 名前 | 年齢 | 住所 | 性別 |
+----+--------+-------+---------+-----+
| 1 | ブランド | 21 | 福州 | 1 |
| 2 | ヘレン | 20 | 泉州 | 0 |
| 3 | ソル | 21 | 厦門 | 0 |
| 4 | 翁 | 33 | 貴州 | 1 |
| 5 | セリーナ | 25 | NULL | 0 |
| 6 | アニー | 23 | 上海 | 0 |
| 7 | そして | 24 | 上海 | 1 |
| 8 | 晴れ | NULL | 貴州省 | 0 |
+----+--------+-------+---------+-----+
8行セット

mysql> user2 から sum(age) を選択します。
+----------+
| 合計(年齢) |
+----------+
| 167 |
+----------+
セット内の1行

グループクエリ

データの準備。次のような注文テーブル（ユーザーの注文金額と注文時間を記録）があると仮定します。

mysql> t_order から * を選択します。
+---------+-----+--------+--------+----------------------+------+
| オーダーID | UID | uname | 金額 | 時間 | 年 |
+---------+-----+--------+--------+----------------------+------+
| 20 | 1 | ブランド | 91.23 | 2018-08-20 17:22:21 | 2018 |
| 21 | 1 | ブランド | 87.54 | 2019-07-16 09:21:30 | 2019 |
| 22 | 1 | ブランド | 166.88 | 2019-04-04 12:23:55 | 2019 |
| 23 | 2 | ヘリン | 93.73 | 2019-09-15 10:11:11 | 2019 |
| 24 | 2 | ヘリン | 102.32 | 2019-01-08 17:33:25 | 2019 |
| 25 | 2 | ヘリン | 106.06 | 2019-12-24 12:25:25 | 2019 |
| 26 | 2 | ヘリン | 73.42 | 2020-04-03 17:16:23 | 2020 |
| 27 | 3 | ソル | 55.55 | 2019-08-05 19:16:23 | 2019 |
| 28 | 3 | ソル | 69.96 | 2020-09-16 19:23:16 | 2020 |
| 29 | 4 | ウェン | 199.99 | 2020-06-08 19:55:06 | 2020 |
+---------+-----+--------+--------+----------------------+------+
10行セット

単一フィールドのグループ化

つまり、ユーザーをグループ化するなど、特定のフィールドをグループ化し、そのユーザー ID、注文数量、合計金額を出力します。

mysql> t_order から uid、count(uid)、sum(amount) を uid でグループ化して選択します。
+-----+------------+-------------+
| uid | count(uid) | sum(量) |
+-----+------------+-------------+
| 1 | 3 | 345.65 |
| 2 | 4 | 375.53 |
| 3 | 2 | 125.51 |
| 4 | 1 | 199.99 |
+-----+------------+-------------+
4行セット

複数フィールドのグループ化

つまり、ユーザーをグループ化するなど、複数のフィールドをグループ化し、異なる年からの注文データをグループ化して、注文数量と合計消費量を出力します。

mysql> t_order から uid、count(uid) を nums、sum(amount) を totalamount、year として選択します。uid、year でグループ化します。
+-----+------+-------------+------+
| uid | 数値 | 合計金額 | 年 |
+-----+------+-------------+------+
| 1 | 1 | 91.23 | 2018 |
| 1 | 2 | 254.42 | 2019 |
| 2 | 3 | 302.11 | 2019 |
| 2 | 1 | 73.42 | 2020 |
| 3 | 1 | 55.55 | 2019 |
| 3 | 1 | 69.96 | 2020 |
| 4 | 1 | 199.99 | 2020 |
+-----+------+-------------+------+
7行セット

グループ化前の条件付きフィルタリング: where

これは非常に簡単です。グループ化 (group by) する前に、where キーワードを使用して条件をフィルタリングし、必要なデータを抽出します。2019 年 8 月以降のデータのみをリストする必要があると仮定します。適格なソース データは 6 つだけであり、そのうち 2 つは同じ年にグループ化されています。

mysql> uid、count(uid) を nums、sum(amount) を totalamount、year を t_order から選択します。time > '2019-08-01' を uid、year でグループ化します。
+-----+------+-------------+------+
| uid | 数値 | 合計金額 | 年 |
+-----+------+-------------+------+
| 2 | 2 | 199.79 | 2019 |
| 2 | 1 | 73.42 | 2020 |
| 3 | 1 | 55.55 | 2019 |
| 3 | 1 | 69.96 | 2020 |
| 4 | 1 | 199.99 | 2020 |
+-----+------+-------------+------+
5行セット

グループ化後の条件付きフィルタリング：

グループ化後にデータをフィルタリングする必要がある場合があります。この場合、having キーワードを使用してデータをフィルタリングする必要があります。上記の条件下では、複数回消費されたデータを取得する必要があります。

mysql> select uid,count(uid) as nums,sum(amount) as totalamount,year from t_order where time > '2019-08-01' group by uid,year having nums>1;
+-----+------+-------------+------+
| uid | 数値 | 合計金額 | 年 |
+-----+------+-------------+------+
| 2 | 2 | 199.79 | 2019 |
+-----+------+-------------+------+
セット内の1行

ここでは、where と having を区別する必要があります。

Where はグループ化 (集計) の前にレコードをフィルタリングしますが、having はグループ化後に結果をフィルタリングし、最終的にフィルタリングされた結果を返します。

Having は 2 レベルのクエリとして理解できます。つまり、having を含むクエリ操作は、まず、having 句のない SQL クエリ結果テーブルを取得し、次にこの結果テーブルで having 条件を使用して一致するレコードをフィルター処理し、最後にこれらのレコードを返します。したがって、having の後には集計関数を続けることができ、この集計関数は select 後の集計関数と同じである必要はありません。

グループ化後の並べ替え

順序条件はグループ化条件の後に配置されます。つまり、各ユーザーの総消費量と消費頻度を計算した後、ユーザーの総消費量が降順に並べ替えられます。

mysql> t_order group by uid から uid、count(uid) を nums、sum(amount) を totalamount として選択します。
+-----+------+-------------+
| uid | 数値 | 合計金額 |
+-----+------+-------------+
| 1 | 3 | 345.65 |
| 2 | 4 | 375.53 |
| 3 | 2 | 125.51 |
| 4 | 1 | 199.99 |
+-----+------+-------------+
4行セット

mysql> t_order から uid、count(uid) を nums、sum(amount) を totalamount として選択します。group by uid order by totalamount desc;
+-----+------+-------------+
| uid | 数値 | 合計金額 |
+-----+------+-------------+
| 2 | 4 | 375.53 |
| 1 | 3 | 345.65 |
| 4 | 1 | 199.99 |
| 3 | 2 | 125.51 |
+-----+------+-------------+
4行セット

グループ化後の制限

limit キーワードは通常、ステートメントの最後に配置されます。たとえば、上記の検索に基づいて、消費量が最も高いアイテムのみを取得し、その他をスキップするために limit 1 を設定します。

mysql> t_order から uid、count(uid) を nums、sum(amount) を totalamount として選択します。group by uid order by totalamount desc limit 1;
+-----+------+-------------+
| uid | 数値 | 合計金額 |
+-----+------+-------------+
| 2 | 4 | 375.53 |
+-----+------+-------------+
セット内の1行

キーワードが実行される順序

上記では、where、group by、having、order by、limit というキーワードを使用していることがわかります。これらを一緒に使用する場合は、特定の順序があります。順序が間違っていると、例外が発生します。構文の形式は次のとおりです。

 tnameからcnameを選択
 where [元のテーブルクエリ条件]
 group by [グループ化式]
 [グループフィルター条件] を持つ
 [並べ替え条件]で並べ替え
 制限[オフセット、]カウント;
mysql> select uid,count(uid) as nums,sum(amount) as totalamount from t_order where time > '2019-08-01' group by uid having totalamount>100 order by totalamount desc limit 1;
+-----+------+-------------+
| uid | 数値 | 合計金額 |
+-----+------+-------------+
| 2 | 3 | 273.21 |
+-----+------+-------------+
セット内の1行

要約する

1. グループ化構文では、select の後に表示されるフィールドは、group by の後のフィールドまたは集計関数の列のいずれかです。他のタイプでは例外が報告されます。自分で試してみることができます。

2. グループ化キーワードの実行順序: where、group by、having、order by、limit。順序を変更することはできません。変更すると例外が報告されます。自分で試すことができます。

上記はMySQLのグループ化クエリと集計関数の詳細です。MySQLのグループ化クエリと集計関数の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL の単一テーブル クエリ操作例の詳細な説明 [構文、制約、グループ化、集計、フィルタリング、並べ替えなど]
  • MySQLグループクエリGroup Byの実装原理の詳細な説明
  • MySQL データベース グループ クエリの group by ステートメントの詳細な説明
  • MySQL初心者はグループ化や集計クエリの煩わしさから解放されます

<<:  時間のかかるDockerエラーのトラブルシューティングプロセス記録

>>:  スキン効果を実現するJavaScript（背景の変更）

推薦する

MySQL の WriteSet 並列レプリケーションの簡単な分析

【歴史的背景】私は 3 年間 MySQL-DBA として働いてきましたが、MySQL が「基本的に利...

JavaScriptプロトタイプチェーンの詳細な説明

目次1. コンストラクタとインスタンス2. プロパティプロトタイプ3. プロパティ __proto_...

MySQLデータベースは何をするのか

MySQL は、スウェーデンの会社 MySQL AB によって開発されたリレーショナル データベース...

一般的な docker コマンドの概要 (推奨)

1. 要約:一般的に、次のカテゴリに分類できます。 Docker 環境情報 — docker [i...

CSSは背景画像の画面適応を実現する

ログインページなどのホームページを作成する場合、大きな背景画像を配置する必要があり、さまざまな画面の...

Bootstrap5 ブレークポイントとコンテナの具体的な使用法

目次1. Bootstrap5 ブレークポイント1.1 モバイルファースト1.2 ブートストラップブ...

Node.js とブラウザのグローバル オブジェクトの違いの概要

Node.js では、.js ファイルは完全なスコープ (モジュール) です。したがって、var に...

CSS3 タイムラインアニメーション

成果を達成する html <h2>CSS3 タイムライン</h2> <...

オンラインMySQL自動増分IDが使い果たされた場合の対処方法

目次テーブル定義の自動増分 ID InnoDBシステムはrow_idを自動的に増加させるシドInno...

HTML テーブル境界コントロールの詳細な説明

上の境界線のみを表示する <table frame=above>下の境界線のみを表示する...

CSS トップに戻る コード例

最近のウェブサイトのほとんどはページが長く、4 画面または 5 画面の長さのものもあれば、2 画面ま...

CSS マルチレベルメニュー実装コード

これは、Web ページを Windows のスタート メニューなどのデスクトップ プログラムのように...

メタタグを簡単に説明すると

META タグは、一般的に タグと呼ばれ、HTML Web ページのソース コード内の重要な HTM...

Docker Compose で利用可能な環境変数の詳細な説明

Compose のいくつかの部分は、何らかの方法で環境変数を扱います。このチュートリアルは、必要な情...

Mysql のフィールドのデータの一部をバッチ置換する (推奨)

MYSQL のフィールドのデータの一部をバッチで置き換えます。具体的な導入は次のとおりです。 1....