MySQL のデータ集約とグループ化

はじめに

この実験では、MySQLにおけるデータの集約とグループ化を学びます。これは、データベース内のデータを分析および要約するための必須スキルです。行全体にわたって計算を行うための集約関数の使い方、列値に基づいてデータをグループ化し、グループ化された結果をフィルタリングする方法を学びます。これらの技術は、レポートの作成、トレンドの分析、およびデータから意味のある洞察を抽出するための基礎となっています。実践的な演習を通じて、これらの重要なデータベース操作に関する実際の経験を得るとともに、MySQLでデータを効果的に分析する方法を理解します。

Skills Graph

基本的な集約関数

このステップでは、MySQLにおける基本的な集約関数を学びます。これらの関数は、複数の行にわたって計算を行い、単一の値を返すため、データ分析に欠かせないものです。

まず、MySQLに接続してデータベースを選択しましょう：

sudo mysql -u root

接続後：

USE sales_db;

COUNT関数

まずはCOUNT関数から始めましょう。この関数は、結果セットの行数をカウントします：

-- 販売の合計数をカウント
SELECT COUNT(*) as total_sales
FROM sales;

以下のような出力が表示されるはずです：

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT関数はさまざまな使い方ができます：

-- 販売されたユニークな商品をカウント
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- カテゴリごとの販売数をカウント
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

解説：

• COUNT(*)は、NULL値を含む表のすべての行をカウントします
• COUNT(DISTINCT column)は、指定された列のユニークな値のみをカウントします
• GROUP BYとともに使用すると、COUNTはそれぞれのグループの合計を個別に計算します
• asキーワードは、結果列にエイリアスを作成し、出力を読みやすくします

SUM関数

SUM関数は、数値列の合計を計算します：

-- 販売された商品の合計数量を計算
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- 合計収益を計算
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

解説：

• SUMは数値列のみで使用できます
• quantity * unit_priceのような計算は、集約の前に行われます
• ROUND(x, 2)は、数値を2桁の小数に丸めます
• 収益の計算では、精度を保つために乗算が合計の前に行われます

AVG関数

AVG関数は、平均値を計算します：

-- 平均単価を計算
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- 1回の販売あたりの平均数量を計算
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

解説：

• AVGは計算時に自動的にNULL値を無視します
• ROUNDの2番目のパラメータは、小数桁数を指定します
• 結果は精度のため自動的にDECIMAL型に変換されます
• AVGは、データの典型的な値を見つけるために一般的に使用されます

MAXおよびMIN関数

これらの関数は、最大値と最小値を見つけます：

-- 商品の価格範囲を見つける
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- 最初の販売日と最後の販売日を見つける
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

解説：

• MIN/MAXは、数値、文字列、日付に対して機能します
• 日付の場合、MINは最も早い日付を見つけ、MAXは最も遅い日付を見つけます
• 複数の集約関数を1つのSELECT文で組み合わせることができます
• 他の集約関数と同様に、NULL値を自動的に無視します
• これらの関数は、データの値の範囲や境界を見つけるのに役立ちます

GROUP BYを使ったデータのグループ化

このステップでは、GROUP BY句を使ってデータをグループ化する方法を学びます。グループ化により、特定の列値に基づいてデータのサブセットに対して集約計算を行うことができます。

基本的なグループ化

まずは簡単なグループ化操作から始めましょう：

-- カテゴリごとの販売数と合計数量
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

このクエリは、各カテゴリでの販売数と商品の合計数量を示します。以下のような出力が表示されるはずです：

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

解説：

• GROUP BYは、同じカテゴリの行を1行にまとめます
• ORDER BY total_quantity DESCは、数量が多い順に結果をソートします
• 各集約関数（COUNT、SUM）は、それぞれのグループ内で独立して動作します
• SELECT句の非集約列は、GROUP BY句に含まれている必要があります

複数列によるグループ化

より詳細な洞察を得るために、複数の列によるグループ化ができます：

-- カテゴリと地域別の販売分析
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

解説：

• 複数の列によるグループ化は、各ユニークな組み合わせに対してサブグループを作成します
• GROUP BY句における列の順序は、データのグループ化方法に影響します
• 結果はまずカテゴリでソートされ、その後各カテゴリ内でtotal_revenueでソートされます
• このアプローチは、各カテゴリ内で最も売上の良い地域を特定するのに役立ちます

日付に基づくグループ化

MySQLには、日付の一部を抽出する関数があり、時間ベースのグループ化に便利です：

-- 日次販売集計
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

解説：

• 同じ日に発生したすべての取引をグループ化します
• ORDER BY sale_dateは、結果を日付順に並べます
• 日次の取引数をカウントし、日次の合計を計算します
• 日次の販売パターンやトレンドを特定するのに役立ちます
• DATE関数を使って、月や年によるグループ化に変更することができます

HAVINGを使ったグループ化されたデータのフィルタリング

このステップでは、グループ化後の結果をフィルタリングするためのHAVING句について学びます。WHEREはグループ化前に個々の行をフィルタリングするのに対し、HAVINGはグループ自体をフィルタリングします。

基本的なHAVINGの使い方

合計15個を超える商品が販売されたカテゴリを見つけましょう：

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

これは、合計数量が15個を超えるカテゴリのみを表示します：

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

解説：

• HAVINGは、GROUP BYが適用された後にグループをフィルタリングします
• HAVINGでは集約関数の結果を参照できます
• フィルタ条件はエイリアス'total_quantity'を使用しています
• 15個以下の商品が販売されたカテゴリは結果から除外されます

WHEREとHAVINGの組み合わせ

WHEREとHAVINGを一緒に使うことができます。WHEREはグループ化前に行をフィルタリングし、HAVINGはグループ化後にフィルタリングします：

-- 北部地域で売上の多いカテゴリを見つける
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

解説：

• WHEREはグループ化前に個々の行をフィルタリングします（region = 'North'）
• 次にデータはカテゴリでグループ化されます
• 最後に、HAVINGはグループ化された結果をフィルタリングします（total_quantity > 5）
• 順序は重要です：FROM → WHERE → GROUP BY → HAVING → SELECT

複雑なHAVING条件

HAVINGでは複数の条件を使うことができます：

-- 売上高と収益が高いカテゴリを見つける
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

解説：

• 複数の条件はAND/ORを使って組み合わせることができます
• すべての集約計算はHAVING条件で利用できます
• HAVING条件で算術演算を使うことができます
• ORDER BYはHAVINGフィルタが評価された後に適用されます
• この種のクエリは、最も売上の良いカテゴリを特定するのに役立ちます

高度な集約技術

この最後のステップでは、これまでに学んだことをすべて組み合わせた、より高度な集約技術を学びます。これらのツールの力を示す包括的な販売レポートを作成します。

販売実績ダッシュボード

包括的な販売分析を作成しましょう：

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

解説：

• 完全なカテゴリのパフォーマンスの概要を作成します
• 各カテゴリ内のユニークな商品をカウントするためにDISTINCTを使用します
• 包括的な分析のために複数の集約関数を組み合わせます
• MINとMAXを使って価格範囲を表示します
• 平均数量と合計収益を計算します
• 結果は収益順にソートされ、最も売上の良いものが強調されます

地域別のパフォーマンス分析

地域別の販売実績を分析しましょう：

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

解説：

• 販売データを地理的な地域でグループ化します
• DISTINCTカウントを使って商品の多様性を示します
• 合計収益と数量の指標を計算します
• avg_price_per_unitは収益を数量で割って計算されます
• HAVINGは収益の少ない地域をフィルタリングします
• 最も強い地域と最も弱い地域を特定するのに役立ちます

日次トレンド分析

日次の販売トレンドレポートを作成しましょう：

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

解説：

• 日次の販売実績指標を追跡します
• 毎日の商品とカテゴリの多様性を示します
• 日次の取引と数量をカウントします
• 日次の収益と平均取引価値を計算します
• 日付順のソートはトレンドを特定するのに役立ちます
• パターンや季節的な影響を見つけるのに役立ちます
• 在庫計画や人員配置の決定に役立つ可能性があります

まとめ

この実験では、MySQLにおけるデータの集約とグループ化の重要な側面を学びました：

1. データを要約するための基本的な集約関数（COUNT、SUM、AVG、MAX、MIN）の使用
2. パターンとトレンドを分析するためにGROUP BYを使ってデータをグループ化する
3. HAVING句を使ってグループ化された結果をフィルタリングする
4. 包括的なデータ分析を作成するために複数の技術を組み合わせる

これらのスキルは、MySQLにおけるデータ分析とレポート作成にとって基本的なものです。データを効果的に集約してグループ化する方法を理解することで、データベースから意味のある洞察を抽出し、価値のあるビジネスレポートを作成することができます。