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（背景の変更）

推薦する

Linux ファイルを分割するための split コマンドの詳細な説明

いくつかの簡単な Linux コマンドを使用すると、ストレージまたは電子メールの添付ファイルのサイズ...

CSSペイントAPIを使用して、解像度に依存しない可変背景効果を動的に作成します。

出典: https://medium.com/better-programming、著者: Fere...

MacにMySQLをインストールするときに忘れたパスワードを変更する方法

1. MacにMySQLデータベースをインストールする1. MySQLデータベースをダウンロードする...

MySQLは文字列の連結、インターセプション、置換、位置検索操作を実装しています

MySQL 文字列の連結、インターセプト、置換、および検索位置。よく使用される文字列関数:関数例示す...

JQueryセレクターの詳細な説明

目次基本的なセレクター:レベルセレクター:属性セレクター:フィルターセレクター:フォーム属性セレクタ...

VueとReactの詳細

目次1. パノラマII. 背景1. 反応: プロフェッショナル2. ビュー: 凡例3. 技術的な思考...

Docker - コンテナマウントディレクトリを変更する3つの方法のまとめ

方法 1: 設定ファイルを変更する (docker サービスを停止する必要があります) 1. doc...

MySQL のロックに関する問題

ロックの分類:データ操作の粒度から:テーブルロック：操作時にテーブル全体がロックされます。行ロック:...

Zen Coding 簡単で素早いHTMLの書き方

禅コーディングテキストエディタプラグインです。 Zen Coding を使用するテキスト エディター...

Dockerはプライベートライブラリイメージを完全に削除します

まず、インターネット上の一般的な慣行を見てみましょうデフォルトでは、プライベート ライブラリはイメー...

Linux で CPU 使用率が高くなる原因をトラブルシューティングするプロセスの詳細な説明

目次序文始めるステップトラブルシューティング序文CPU 使用率が高くなるのは、オンラインでよくある問...

Nginx 静的サービス設定の詳細な説明 (ルートとエイリアスの指示)

静的ファイルNginx は高いパフォーマンスで知られており、フロントエンドのリバース プロキシ サー...

Linux で履歴レコードを表示し、タイムスタンプを追加するためのヒント

Linux で履歴レコードを表示し、タイムスタンプを追加するためのヒントbashに詳しい人なら、hi...

VMware および CentOS システムのインストール方法 - ルート パスワードをリセットする

今日のタスク1. Linuxディストリビューションの選択2.vmwareが仮想マシン（centos）...

MySQLでヘッダー付きのCSVファイルをエクスポートする方法

公式ドキュメント http://dev.mysql.com/doc/refman/5.7/en/se...