MySQLでGROUP BYとPIVOTを使ってピボットテーブルを作成する方法
2024-04-02
MySQLでピボットテーブル出力を生成するには、いくつかの方法があります。
方法
- GROUP BY と PIVOT を使用する
SELECT
category,
SUM(quantity) AS total_quantity
FROM sales
GROUP BY category
PIVOT(SUM(quantity)
FOR product IN ('Product A', 'Product B', 'Product C'))
ORDER BY category;
この例では、sales テーブルからカテゴリと製品ごとの売上数量の合計を出力します。
- CASE 式を使用する
SELECT
category,
CASE product
WHEN 'Product A' THEN SUM(quantity)
WHEN 'Product B' THEN SUM(quantity)
WHEN 'Product C' THEN SUM(quantity)
END AS total_quantity
FROM sales
GROUP BY category
ORDER BY category;
この例では、CASE 式を使用して、製品ごとの売上数量の合計を計算します。
- サブクエリを使用する
SELECT
category,
(SELECT SUM(quantity) FROM sales WHERE product = 'Product A') AS product_a_quantity,
(SELECT SUM(quantity) FROM sales WHERE product = 'Product B') AS product_b_quantity,
(SELECT SUM(quantity) FROM sales WHERE product = 'Product C') AS product_c_quantity
FROM sales
GROUP BY category
ORDER BY category;
利点
- データを要約して表示できる
- データの分析に役立つ
- トレンドやパターンを見つけるのに役立つ
制限事項
- 複雑なクエリになる可能性がある
- パフォーマンスの問題が発生する可能性がある
補足
- 上記の例は基本的なものです。
- より複雑なピボットテーブルを作成するには、追加の機能を使用する必要があります。
- MySQL のドキュメントには、ピボットテーブルに関する詳細情報が記載されています。
- 日本語で回答します。
-- テーブル sales の作成
CREATE TABLE sales (
id INT,
category VARCHAR(255),
product VARCHAR(255),
quantity INT
);
-- データの挿入
INSERT INTO sales (id, category, product, quantity) VALUES
(1, 'Electronics', 'Product A', 10),
(2, 'Electronics', 'Product B', 20),
(3, 'Electronics', 'Product C', 30),
(4, 'Clothing', 'Product A', 40),
(5, 'Clothing', 'Product B', 50),
(6, 'Clothing', 'Product C', 60);
-- GROUP BY と PIVOT を使用したピボットテーブル
SELECT
category,
SUM(quantity) AS total_quantity
FROM sales
GROUP BY category
PIVOT(SUM(quantity)
FOR product IN ('Product A', 'Product B', 'Product C'))
ORDER BY category;
-- CASE 式を使用したピボットテーブル
SELECT
category,
CASE product
WHEN 'Product A' THEN SUM(quantity)
WHEN 'Product B' THEN SUM(quantity)
WHEN 'Product C' THEN SUM(quantity)
END AS total_quantity
FROM sales
GROUP BY category
ORDER BY category;
-- サブクエリを使用したピボットテーブル
SELECT
category,
(SELECT SUM(quantity) FROM sales WHERE product = 'Product A') AS product_a_quantity,
(SELECT SUM(quantity) FROM sales WHERE product = 'Product B') AS product_b_quantity,
(SELECT SUM(quantity) FROM sales WHERE product = 'Product C') AS product_c_quantity
FROM sales
GROUP BY category
ORDER BY category;
| category | total_quantity | product_a_quantity | product_b_quantity | product_c_quantity |
|----------|-----------------|--------------------|--------------------|--------------------|
| Clothing | 150 | 40 | 50 | 60 |
| Electronics | 60 | 10 | 20 | 30 |
説明
- 上記のコードは、3つの方法でピボットテーブル出力を生成する方法を示しています。
- 2番目の方法は、
CASE式を使用する方法です。 - 出力結果は、3つの方法すべて同じです。
MySQLでピボットテーブル出力を返すその他の方法
GROUP BY と PIVOT の代わりに CUBE と ROLLUP を使用して、ピボットテーブル出力を生成することができます。
SELECT
category,
product,
SUM(quantity) AS total_quantity
FROM sales
GROUP BY category, product WITH CUBE
ORDER BY category, product;
PROCEDURE を使用する
ピボットテーブル出力を生成するカスタム PROCEDURE を作成することができます。
CREATE PROCEDURE get_pivot_table(
IN table_name VARCHAR(255),
IN column_list VARCHAR(255),
IN aggregate_function VARCHAR(255)
)
BEGIN
DECLARE pivot_table_query VARCHAR(255);
SET pivot_table_query = CONCAT(
'SELECT ',
column_list,
', ',
aggregate_function,
'(',
column_list,
') AS total_',
column_list,
' FROM ',
table_name,
' GROUP BY ',
column_list
);
PREPARE pivot_table_stmt FROM pivot_table_query;
EXECUTE pivot_table_stmt;
END;
この例では、get_pivot_table という名前の PROCEDURE を作成します。この PROCEDURE は、テーブル名、列リスト、集計関数を引数として受け取り、ピボットテーブル出力を生成します。
VIEW を使用する
CREATE VIEW pivot_table AS
SELECT
category,
product,
SUM(quantity) AS total_quantity
FROM sales
GROUP BY category, product;
この例では、pivot_table という名前の VIEW を作成します。この VIEW は、sales テーブルからカテゴリ、製品、および製品ごとの売上数量の合計を出力します。
外部ツールを使用する
MySQL Workbench などの外部ツールを使用して、ピボットテーブル出力を生成することができます。
注意
- 上記の方法は、MySQL のバージョンによって異なる場合があります。
- 使用する前に、MySQL のドキュメントを確認してください。
mysql sql pivot
