MySQL データ集約とグループ化チュートリアル | インタラクティブ実験

はじめに

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

基本的な集約関数

このステップでは、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 におけるデータの集約とグループ化の重要な側面を学びました：

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

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

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