MySQL 集計関数: SUM、COUNT、 AVG ＆マックス

集計関数とは何ですか MySQL?

An 集計関数 単一列の複数の行を読み込み、それらを1つの値に集約します。集計関数のすべては次のとおりです。

• 複数行に対して計算を実行する
• テーブルの単一列の
• そして単一の値を返します。

ISO規格では、以下の5つの集約関数が定義されています。

1. COUNT
2. 和
3. AVG
4. MIN
5. MAX

5人全員に共通するルールが1つあります。 集計関数はNULL値を無視しますCOUNT(*) は唯一の例外であり、その理由については以下で説明します。

集計関数を使用する理由

組織の階層によって、必要な情報は異なります。トップレベルの管理者は通常、個々の詳細ではなく、全体の数値に関心があります。

集計関数を使用すると、データベースから要約データを簡単に生成できます。

例えば、当社のmyflixデータベースから、経営陣は以下のようなレポートを必要とする場合があります。

• 最もレンタルされていない映画。
• 最も多くレンタルされた映画。
• 各映画が1ヶ月間にレンタルされる平均回数。

上記のレポートはすべて集計関数から得られたものです。それぞれを詳しく見ていきましょう。

COUNT関数

COUNT関数は、数値データ型と非数値データ型の両方において、指定されたフィールド内の値の総数を返します。 他の集計関数と同様に、COUNT(column) は NULL 値を除外します。

COUNT(*) は、テーブル内のすべての行の数を返す特別な形式です。また、 NULL また、重複もカウントされます。なぜなら、値ではなく行数をカウントするからです。

movierentalsテーブルには以下のデータが含まれています。

IDが2の映画が何回レンタルされたかを取得したいとしましょう。

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

これを実行すると MySQL ワークベンチ myflixdb に対しては 3 が返されます。これは、3 つの行に movie_id 2 が含まれているためです。

DISTINCT キーワード

COUNTは「いくつ」に答えます。次の質問は通常「いくつ」です。 今とは異なる 「それぞれ」という意味で、それがDISTINCTの目的です。

DISTINCTキーワードは、グループごとに結果から重複を除外します。ping 上の図が示すとおり、全く同じ値が一緒に並んでいます。

まず、簡単なクエリを実行してみましょう。

SELECT `movie_id` FROM `movierentals`;

では、同じクエリにDISTINCTキーワードを追加してみましょう。

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT は重複レコードを除外します。

COUNT、COUNT(*)、COUNT(DISTINCT)：どれを使うべきか？

DISTINCT も配置できます 内部 集計関数、そしてここでほとんどの初心者がつまずく。 trac実際にカウントされる行数はkです。以下の4つのフォームはすべて、先に示した5行の映画レンタルテーブルに対して実行されますが、すべて同じ数を返すわけではありません。違いは、フォームが行数をカウントするのか値数をカウントするのか、そして重複を保持するのかという2つの点に起因します。

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡ヒント: 行数を数えるには COUNT(*)、NULL が「該当しない」ことを意味する場合は COUNT(column)、一意の値を取得するには COUNT(DISTINCT column) を使用します。DISTINCT の反対は ALL で、これはデフォルトであるため、明示的に記述されることはほとんどありません。

MIN機能

MIN関数 指定されたテーブルフィールドの最小値を返します.

例えば、私たちのライブラリにある最も古い映画が公開された年を知りたいとします。 MySQLの MIN 関数がそれを与えてくれます。

SELECT MIN(`year_released`) FROM `movies`;

結果：

マックス機能

名前が示すように、MAX 関数は MIN 関数の逆です。 それ 指定されたテーブルフィールドから最大値を返します.

データベース内の最新映画の公開年を知りたいとします。以下の例はそれを返します。

SELECT MAX(`year_released`) FROM `movies`;

結果：

SUM機能

MIN と MAX は列から既存の値を選択します。SUM と AVG 列全体から新しい数値を計算します。

これまでに支払われた合計金額を知りたいとしましょう。 MySQL 和 function 指定された列のすべての値の合計を返します. SUM は数値フィールドでのみ機能します, NULL値は結果から除外されます.

以下の表は、支払いテーブルのデータを示しています。

以下に示すクエリは、行われたすべての支払いを取得し、それらを合計して単一の結果にします。2500 + 2000 + 6000 = 10500。

SELECT SUM(`amount_paid`) FROM `payments`;

結果：

AVG function

その MySQL AVG function 指定された列の値の平均を返します。 SUM関数と同じように、 数値データ型でのみ機能します.

平均支払額を求めたいとします。その場合、合計10500をNULL以外の3つの支払行で割る以下のクエリを使用できます。

SELECT AVG(`amount_paid`) FROM `payments`;

結果：

⚠️ 警告: AVG テーブルの行数ではなく、NULL以外の行数で割ります。NULL値はゼロとしてカウントされるのではなくスキップされるため、平均値が密かに上昇します。 AVG(IFNULL(`amount_paid`, 0)) 欠損値がゼロを意味する場合。

実例：集計関数とGROUP BYの組み合わせ

上記の各関数は、テーブル全体に対して 1 つの数値を返しました。 グループ化 この句は1つの数値を返します グループごと そうではなく、それが真のレポートの作成方法なのです。

以下の例では、メンバーを名前でグループ化し、各メンバーの支払い総数、平均支払い金額、および支払い金額の合計をカウントします。

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

上記の例を実行すると、 MySQL Workbenchは、以下の結果を表示します。

このクエリは、WHERE句で2つのテーブルを結合します（古いカンマ結合スタイル）。最新のコードでは、同じロジックを明示的に記述します。 内部結合…オンまた、SELECT リスト内の集計されていない列はすべて GROUP BY に表示する必要があることに注意してください。 MySQL 5.7以降では、ONLY_FULL_GROUP_BYの下でクエリが拒否されます。 公式 MySQL 集計関数の参照.

よくあるご質問