MariaDBでgaps-and-islandsパターンを持つデータセットから連続するIDを持つ行を凝縮する

SQL - 連続するIDを持つ行を凝縮する

概要

使用例

このチュートリアルで使用するデータセットは以下の通りです。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  product_id INT
);

INSERT INTO orders VALUES
  (1, 1, '2024-01-01', 10),
  (2, 1, '2024-01-02', 20),
  (3, 1, '2024-01-04', 30),
  (4, 2, '2024-01-05', 40),
  (5, 2, '2024-01-06', 50),
  (6, 3, '2024-01-08', 60);

このデータセットには、3人の顧客の注文情報が含まれています。各注文には、注文ID、顧客ID、注文日、商品IDが含まれています。

このデータセットから、以下のクエリを使用して連続するIDを持つ行を凝縮することができます。

SELECT
  customer_id,
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS last_order_date,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

このクエリは、以下の結果を返します。

customer_id | first_order_date | last_order_date | order_count
------------+------------------+------------------+------------
1           | 2024-01-01      | 2024-01-04      | 3
2           | 2024-01-05      | 2024-01-06      | 2
3           | 2024-01-08      | 2024-01-08      | 1

この結果は、各顧客の最初の注文日、最後の注文日、注文数を示しています。

解説

1. GROUP BY 句を使用して、顧客IDごとにデータをグループ化します。
2. MIN(order_date) 関数を使用して、各グループの最初の注文日を計算します。
3. COUNT(*) 関数を使用して、各グループの注文数を計算します。
4. ORDER BY 句を使用して、結果を顧客IDで昇順にソートします。

応用例

このテクニックは、以下の場合に役立ちます。

• 顧客の注文履歴を分析する
• 製品売上の傾向を追跡する
• ユーザーアクティビティを分析する

このチュートリアルでは、MariaDBで gaps-and-islands パターンを持つデータセットから連続するIDを持つ行を凝縮する方法について説明しました。このテクニックは、様々なデータ分析タスクに役立ちます。

補足

• このチュートリアルでは、MariaDBを使用していますが、他のSQLデータベースでも同様の手法を使用することができます。
• gaps-and-islands パターン以外にも、様々なデータパターンを処理するためにこのテクニックを応用することができます。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  product_id INT
);

INSERT INTO orders VALUES
  (1, 1, '2024-01-01', 10),
  (2, 1, '2024-01-02', 20),
  (3, 1, '2024-01-04', 30),
  (4, 2, '2024-01-05', 40),
  (5, 2, '2024-01-06', 50),
  (6, 3, '2024-01-08', 60);

SELECT
  customer_id,
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS last_order_date,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

customer_id | first_order_date | last_order_date | order_count
------------+------------------+------------------+------------
1           | 2024-01-01      | 2024-01-04      | 3
2           | 2024-01-05      | 2024-01-06      | 2
3           | 2024-01-08      | 2024-01-08      | 1

コード解説

このコードは以下の部分で構成されています。

テーブルの作成

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  product_id INT
);

この部分は、orders という名前のテーブルを作成します。このテーブルには、以下の列が含まれています。

• order_id: 注文ID (主キー)
• customer_id: 顧客ID
• order_date: 注文日
• product_id: 商品ID

データの挿入

INSERT INTO orders VALUES
  (1, 1, '2024-01-01', 10),
  (2, 1, '2024-01-02', 20),
  (3, 1, '2024-01-04', 30),
  (4, 2, '2024-01-05', 40),
  (5, 2, '2024-01-06', 50),
  (6, 3, '2024-01-08', 60);

この部分は、orders テーブルにデータを追加します。

連続するIDを持つ行を凝縮するクエリ

SELECT
  customer_id,
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS last_order_date,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

このクエリは、上記のサンプルデータセットに対して、以下の結果を返します。

customer_id | first_order_date | last_order_date | order_count
------------+------------------+------------------+------------
1           | 2024-01-01      | 2024-01-04      | 3
2           | 2024-01-05      |

SQL - 連続するIDを持つ行を凝縮する： 他の方法

概要

WITH 句を使用した方法

WITH customer_orders AS (
  SELECT
    customer_id,
    order_id,
    order_date
  FROM orders
  ORDER BY customer_id, order_id
)
SELECT
  customer_id,
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS last_order_date,
  COUNT(*) AS order_count
FROM customer_orders
GROUP BY customer_id;

この方法は、WITH 句を使用して中間テーブルを作成し、そのテーブルに対して集計処理を実行します。

窓関数を使用した方法

SELECT
  customer_id,
  MIN(order_date) OVER (PARTITION BY customer_id ORDER BY order_id) AS first_order_date,
  MAX(order_date) OVER (PARTITION BY customer_id ORDER BY order_id) AS last_order_date,
  COUNT(*) OVER (PARTITION BY customer_id) AS order_count
FROM orders;

この方法は、窓関数を使用して、各行の相対的な位置に基づいて集計処理を実行します。

3つの方法の比較