条件によるMysqlカウントの複数の実装方法を詳細に解説

最近、あるウェブサイトのバックエンドに一連の統計機能を追加していたのですが、条件によるカウントが必要な状況に何度も遭遇しました。いくつかの方法を試してみましたので、参考までに以下に簡単に記録しておきます。

問題の説明

議論を理解しやすくするために、問題を少し単純化し、背景の多くを削除しました。

昔、50人の側室を持つ皇帝がいました。これらの側室は、不当にも10万人の息子を皇帝のために産みました。皇帝は非常に悩みました。これほど多くの息子を管理するのは困難でした。さらに、皇帝は、側室がそれぞれ何人の息子を産んだかを知り、功績に応じて褒美を与えたいと考えました。これは非常に困難なことでした。そこで皇帝は、データベースを使用して息子全員の情報を保存するプログラムの作成を手伝ってくれるプログラマーを雇い、そのプログラムを使用して息子全員を数え、管理できるようにしました。

データベースの構造は次のとおりです。

皇帝は側室を天宮の側室（25人未満）と地下宮の側室（25人以上）の2つの階級に分けました。皇帝は天宮の側室と地下宮の側室のどちらがより繁殖力があるかを知りたかったのです。そこで、プログラマーは SQL クエリを書き始めました。

方法1: GROUP BYを使用する

SQLクエリ

SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;

実行結果

カウント（*）
50029
49971

100,000行の実行時間: 0.0335秒

分析する

この GROUP BY アプローチの最大の問題は、得られた結果を区別できないことです。次の 2 つの数字のうち、どちらが天宮の女性から生まれた王子の数であり、どちらが地底宮殿の女性から生まれた王子の数でしょうか。全く分かりません。したがって、合計が示されても意味がありません。

したがって、統計結果を区別するためには、条件 mother > 24 も結果セットのフィールドとして反映される必要があります。変更された SQL は次のようになります。

SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;

実行結果

数値型
50029 0
49971 1

条件式をフィールドとして使用する場合、フィールドの値は条件式の値になります。したがって、この例では、type = 1 は mother > 24 の値が 1 であることを意味します。したがって、2 行目の数字は、地下宮殿の女性に生まれた王子の数を表します。

修正後、天宮の女神たちが少し良くなったことがわかります。

長所と短所

欠点は明らかです。条件式をグループ化の基準として使用するため、バイナリ分割しかできず、統計目的で複数のカテゴリを分割する必要がある状況には適していません。例えば、1～10番、11～24番、25～50番の側室がそれぞれ産んだ子供の数を数えることは不可能である。

また、GROUP BY が使用されるため、ソートが伴い、実行時間が長くなります。

このアプローチの利点はまだ見つかっていません。

方法2: ネストされたSELECTを使用する

この目標は、ネストされた SELECT を使用して、各 SELECT 句の条件に従ってデータをカウントし、これらの統計データをメインの SELECT と統合することによっても達成できます。

SQLクエリ

選択 
  ( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`, 
  ( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`

実行結果

ディゴン ティアンゴン
49971 50029

100,000行の実行時間: 0.0216秒

分析する

このネストされたSELECTメソッドは非常に直感的です。各条件の値を個別にカウントし、最後にそれらをまとめます。理解しやすく、自然言語と変わりません。

長所と短所

利点は、GROUP BY よりも直感的で高速であることです。 SELECT ステートメントは 3 つあり、GROUP BY ソリューションよりも 2 つ多いように見えますが、ソートは行われないため、時間を大幅に節約できます。

デメリットとしては、文章が多すぎるため、文章の数にこだわる生徒は不快感を覚えるかもしれないということが挙げられます。

方法3: CASE WHENを使用する

CASE WHEN ステートメントは非常に強力で、柔軟なクエリ条件を定義できるため、分類統計に非常に適しています。

SQLクエリ

選択 
  COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`, 
  COUNT( CASE WHEN `mother` <= 24 THEN 1 ELSE NULL END ) AS `tiangong`
王子より

実行結果

ディゴン ティアンゴン
49971 50029

100,000 行の実行時間: 0.02365825 秒

分析する

この方法の鍵は

COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END )

ここでは、カテゴリカウントを実現するために、COUNT と CASE WHEN が一緒に使用されています。まず、CASE WHEN を使用します。条件が満たされると、フィールド値が 1 に設定されます。条件が満たされない場合は、フィールド値が NULL に設定されます。次に、COUNT 関数は NULL 以外のフィールドのみをカウントします。これで問題は解決します。

長所と短所

利点としては、ソート処理が不要なため、実行時間は方法 2 と同程度で、SELECT 文の数は 1 に減ります。

欠点は、文章が比較的長いため、文章の長さにこだわる生徒は不快感を覚える可能性があることです。

要約する

特定のカテゴリの条件付きカウントの場合は、並べ替えを回避してクエリの実行を高速化するために、GROUP BY を使用しないようにしてください。

フィールドの値に基づいて分類する必要があり、フィールドの値が可変である場合、たとえば、皇帝は各側室が産んだ子供の数を数えたいと考えており、多くの側室と結婚し続ける可能性があります。この場合、方法 2 と 3 を使用することはあまり効果的ではなく、GROUP BY を使用する方が簡単で便利です。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。