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

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

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

問題の説明

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

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

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

id王子の固有番号
母親王子の母親のユニークな番号

皇帝は側室を天宮の側室(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 を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQLのグループカウントと範囲集計を実装する2つの方法
  • 一定期間の日ごと、時間ごとの統計データを取得するMySQLの詳しい説明
  • MySQL 時間統計方法の概要
  • mysqlは指定された期間内の統計データを取得します
  • MySQL で高性能かつ高同時実行のカウンター ソリューションを実装する (記事のクリック数など)
  • Mysql auto_increment recount (ID を 1 から開始する)
  • PHPとMYSQLを使用してカウンターを作成するプロセスの詳細な説明

<<:  MySQL で distinct メソッドを使用する詳細な例

>>:  Linux サーバー上で nvidia-docker 環境を設定するプロセスの詳細な説明

推薦する

Node.js のイベント モジュールに関する知識ポイントのまとめ

Node の研究と応用を通じて、NodeJS はシングルスレッド、イベント駆動型、非ブロッキング I...

MySQL データをエクスポートする際の secure-file-priv 問題の解決方法

エラー 1290 (HY000) : MySQL サーバーは –secure-file-priv オ...

MySQL 5.7.18 zip バージョンのインストールと設定方法のグラフィック チュートリアル (win7)

Windows に mysql5.7.18zip バージョンをインストールするには、使用前に解凍し...

ウェブサイトのハイパーリンクを開く方法に関する議論

新しいウィンドウが開きます。 利点: ユーザーがリンクをクリックしても、現在閲覧しているコンテンツは...

MySQL実践スキル: 2つのテーブルに異なるデータがあるかどうかを比較する方法の分析

この記事では、MySQL が 2 つのテーブルを比較して、異なるデータがあるかどうかを確認する方法を...

Centos7 で yum を使用して Ceph 分散ストレージをインストールするチュートリアル

目次序文yumソース、epelソースを設定するCephソースの設定Cephとそのコンポーネントをイン...

スケルトンスクリーン効果を実現する CSS

ネットワーク データを読み込むときは、ユーザー エクスペリエンスを向上させるために、通常は円形の読み...

MySQLクエリ条件のnot inとinの違いと理由

まずSQLを書く SELECT DISTINCT from_id タラから cod.from_id ...

Linuxの相対パスと絶対パスの使用

01. 概要絶対パスと相対パスはシェル環境でよく使用され、それぞれに独自の用途があります。相対パスの...

MySQLの7種類のログの概要

MySQL には次のログ ファイルがあります。 1: 再実行ログ2: ロールバックログ(元に戻すログ...

Windows 10 での MySQL 8.0.16 のインストールと設定のチュートリアル

この記事では、参考までにMySQL 8.0.16のインストールと設定方法のグラフィックチュートリアル...

Vue はアップロードされた画像に透かしを追加する機能を実装します

この記事では、Vueでアップロードされた画像に透かしを追加する具体的な実装コードを参考までに共有しま...

HTMLテーブルではテーブルの外側の境界線のみが表示されます

質問があります。Dreamweaver で、3 行 1 列のログイン フォーム (ログイン、登録、パ...

Docker を使用して MySQL 5.7 および 8.0 マスター スレーブ クラスターをデプロイする方法

> MySQL 5.7 クラスタ マスターとスレーブをデプロイする (テストのみ)イメージバー...