売上データを顧客ごとに集計し、降順ソートする方法：MariaDBでGROUP BY、SUM()、WITH ROLLUP、およびORDER BYを使用する

MariaDBでGROUP BYとSUM() DESC、およびWITH ROLLUPを使用した集計結果の降順ソート方法

このチュートリアルでは、MariaDBでGROUP BY句とSUM()集計関数、そしてWITH ROLLUP句を組み合わせて集計結果を取得し、その結果を降順 (DESC) にソートする方法について説明します。

シナリオ

売上データを顧客ごとにグループ化し、各顧客の売上合計を降順にソートしたい状況を想定します。

使用するテーブル

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2)
);

クエリ

WITH order_summary AS (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id
  WITH ROLLUP
)
SELECT *
FROM order_summary
ORDER BY total_sales DESC;

解説

1. WITH句による中間テーブルの作成: WITH句を使用すると、order_summaryという名前の中間テーブルを作成できます。このテーブルには、顧客IDと各顧客の売上合計が含まれます。
   • SELECT: 集計したい列を指定します。この例では、customer_idとSUM(amount)を選択します。
   • FROM: 集計対象のテーブルを指定します。この例では、ordersテーブルを指定します。
   • GROUP BY: グループ化の基準となる列を指定します。この例では、customer_id列でグループ化します。
   • WITH ROLLUP: サブトータル行を追加します。この例では、各顧客の売上合計に加えて、全体の売上合計も表示されます。
2. ORDER BY句による降順ソート: ORDER BY句を使用して、total_sales列（売上合計）に基づいて結果を降順にソートします。

結果

上記のクエリを実行すると、以下の結果が得られます。

customer_id | total_sales
-----------+------------
NULL       | 10000.00
1          | 5000.00
2          | 3000.00
3          | 2000.00

• ORDER BY句を使用して、集計結果を任意の列に基づいてソートできます。
• WITH ROLLUP句を使用すると、各グループの集計結果に加えて、全体の集計結果も表示できます。
• GROUP BY句とSUM()関数を使用すると、列の値に基づいてデータをグループ化し、各グループの合計値を計算できます。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2)
);

INSERT INTO orders (order_id, customer_id, amount)
VALUES
  (1, 1, 100.00),
  (2, 1, 200.00),
  (3, 2, 300.00),
  (4, 2, 400.00),
  (5, 3, 500.00),
  (6, 3, 600.00);

WITH order_summary AS (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id
  WITH ROLLUP
)
SELECT *
FROM order_summary
ORDER BY total_sales DESC;

1. ordersテーブルの作成: ordersという名前のテーブルを作成し、order_id、customer_id、amountという3つの列を定義します。
2. データの挿入: ordersテーブルに6つのレコードを挿入します。各レコードは、注文ID、顧客ID、注文金額を表します。

customer_id | total_sales
-----------+------------
NULL       | 2100.00
1          | 300.00
2          | 700.00
3          | 1100.00

SELECT customer_id, total_sales
FROM (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id
  WITH ROLLUP
) AS order_summary
ORDER BY total_sales DESC;

方法2：ウィンドウ関数

SELECT customer_id, total_sales
FROM orders
GROUP BY customer_id
WITH ROLLUP
ORDER BY SUM(amount) OVER (PARTITION BY customer_id ORDER BY customer_id DESC);

方法3：集計結果を一時テーブルに格納

CREATE TEMPORARY TABLE order_summary (
  customer_id INT,
  total_sales DECIMAL(10,2)
);

INSERT INTO order_summary (customer_id, total_sales)
SELECT customer_id, SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id
WITH ROLLUP;

SELECT *
FROM order_summary
ORDER BY total_sales DESC;

方法4：CASE式

SELECT
  customer_id,
  total_sales,
  CASE WHEN customer_id IS NULL THEN 0 ELSE total_sales END AS order_by_column
FROM orders
GROUP BY customer_id
WITH ROLLUP
ORDER BY order_by_column DESC;

各方法の説明

• 方法4：CASE式: CASE式を使用して、customer_idがNULLの場合に0を代入し、その列でソートします。
• 方法3：集計結果を一時テーブルに格納: 集計結果を一時テーブルに格納し、そのテーブルに対してORDER BY句を実行します。
• 方法2：ウィンドウ関数: SUM()関数とOVER句を使用して、ウィンドウ内の売上合計を計算し、降順にソートします。
• 方法1：サブクエリ: WITH句の代わりにサブクエリを使用して、中間テーブルを作成します。

どの方法を選択するべきか

どの方法を選択するかは、個々の状況によって異なります。

• 可読性: 方法4は、CASE式を使用してロジックを明確にすることができます。
• 柔軟性: 方法3は、集計結果をさらに処理する必要がある場合に柔軟性を提供します。
• パフォーマンス: 方法2は最もパフォーマンスが優れていますが、複雑な構文を使用しています。
• シンプルさ: 方法1は最もシンプルですが、パフォーマンスが劣る場合があります。