MySQLで集計レポートを作成するためのチュートリアル：SUM IFとCOUNT IFをステップバイステップで解説
2024-07-27
MySQLにおける条件付き集計：SUM IFとCOUNT IF
SUM IF
SUM IF関数は、指定された条件を満たす行の特定の列の値を合計します。構文は以下の通りです。
SUM(IF(条件, 値_1, 値_2))
- 条件: 評価される論理式
- 値_1: 条件が真の場合に返される値
例: 特定のカテゴリの製品の売上合計を求める
SELECT category, SUM(IF(category = '電子機器', amount, 0)) AS electronics_sales
FROM orders;
このクエリは、ordersテーブルからすべてのカテゴリの製品の売上を抽出し、category列の値が'電子機器'である行のamount列の値のみを合計します。
COUNT IF
COUNT(IF(条件, 1, 0))
例: 特定のステータスを持つ注文数をカウントする
SELECT status, COUNT(IF(status = '完了', 1, 0)) AS completed_orders
FROM orders;
このクエリは、ordersテーブルからすべての注文のステータスを抽出し、status列の値が'完了'である行数をカウントします。
利点
- 柔軟性: 特定の条件に基づいて集計をカスタマイズできます。
- 詳細な分析: WHERE句だけでは得られない詳細な洞察を得ることができます。
- データの理解: データをより深く理解し、意思決定を改善するのに役立ちます。
注意点
- パフォーマンス: 複雑な条件を使用すると、パフォーマンスが低下する可能性があります。
- 可読性: 複雑な条件式は、コードの可読性を低くする可能性があります。
- 代替手段: 場合によっては、WHERE句とサブクエリを使用して同様の結果を得ることができます。
SUM IFとCOUNT IFは、MySQLで強力な条件付き集計を行うための貴重なツールです。これらの関数を適切に使用することで、データからより深い洞察を得ることができ、意思決定を改善することができます。
- 上記の例はほんの一例です。これらの関数は、さまざまな目的に使用できます。
問題
売上データテーブルがあり、以下の列が含まれています。
- 注文 ID: 各注文の一意の識別子
- 顧客 ID: 注文を顧客に関連付ける
- 商品 ID: 注文された商品を識別する
- 単価: 商品の価格
- 数量: 注文された商品の個数
- 注文日: 注文日
- ステータス: 注文のステータス（例：処理中、完了、キャンセル済み）
このテーブルを使用して、以下のタスクを実行する必要があります。
- 2023年12月に完了した注文の総売上を計算する
- 各顧客が注文した商品の平均数量を計算する
解決策
SELECT
SUM(IF(status = '完了' AND MONTH(order_date) = 12 AND YEAR(order_date) = 2023, price * quantity, 0)) AS total_sales_december_2023
FROM orders;
説明:
- このクエリは、
ordersテーブルからすべての注文を抽出します。 IF条件を使用して、以下の条件を満たす行のみを処理します。status列の値が'完了'であるorder_date列の月の値が12である
IF条件が真の場合、price列とquantity列の値を乗算して、その行の売上を計算します。IF条件が偽の場合、0を返します。SUM関数を使用して、すべての行の売上を合計します。- 結果は、
total_sales_december_2023という名前の列に格納されます。
SELECT
customer_id,
AVG(quantity) AS average_quantity_per_customer
FROM orders
GROUP BY customer_id;
GROUP BY句を使用して、customer_id列の値ごとにデータをグループ化します。AVG関数を使用して、各グループ内のquantity列の値の平均を計算します。- 結果は、
customer_idとaverage_quantity_per_customerという2つの列を含むテーブルになります。
実行
これらのクエリをMySQLデータベースに対して実行すると、以下の結果が得られます。
total_sales_december_2023
--------------------------
10000.00
customer_id | average_quantity_per_customer
------------+------------------------------
1 | 2.5
2 | 3.0
3 | 1.0
MySQLにおける条件付き集計：代替方法
サブクエリ
サブクエリを使用して、条件付き集計を実行することができます。構文は以下の通りです。
SELECT column_name, aggregate_function(column_value)
FROM table_name
WHERE condition;
- column_name: 集計する列名
- aggregate_function: 使用する集計関数（例：SUM、COUNT、AVG）
- table_name: 集計対象のテーブル名
- condition: 集計対象となる行を指定する条件
SELECT category, SUM(amount) AS electronics_sales
FROM orders
WHERE category = '電子機器';
利点:
- 柔軟性：複雑な条件を処理することができます。
- 可読性：コードがわかりやすい場合がある。
欠点:
- パフォーマンス：サブクエリは、SUM IFやCOUNT IF関数よりも非効率的な場合があります。
CASE式
CASE式を使用して、条件に応じてさまざまな値を返すことができます。構文は以下の通りです。
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE valueN
END
- condition1: 評価される最初の論理式
- value1: condition1が真の場合に返される値
- condition2: condition1が偽の場合に評価される次の論理式
- valueN: すべての条件が偽の場合に返される値
SELECT status, COUNT(*) AS completed_orders
FROM orders
GROUP BY status
HAVING status = '完了';
- シンプル：簡単な条件付き集計に使用する場合に適しています。
- 読みやすい：コードがわかりやすい。
- 複雑な条件：複雑な条件を処理する場合には、CASE式は非効率的になる可能性があります。
ウィンドウ関数
MySQL 8以降では、ウィンドウ関数を使用して、条件付き集計を実行することができます。ウィンドウ関数は、現在の行だけでなく、行のグループ全体にわたって集計を実行することができます。
SELECT category, SUM(amount) OVER (PARTITION BY category) AS electronics_sales
FROM orders;
- パフォーマンス：大きなデータセットに対して効率的です。
- 複雑性：ウィンドウ関数は、習得するのが難しい場合があります。
- 互換性：MySQL 8以降でのみ使用可能です。
mysql
