SQLite ウィンドウ分析 (SQLite Window Analytics)

はじめに (Introduction)

この実験 (lab) では、SQLite のウィンドウ分析 (window analytics) を探求し、行のランキング (ranking rows) と累計 (running totals) の計算に焦点を当てます。ウィンドウ関数 (window functions) を使用して、現在の行に関連する行のセット全体で計算を実行する方法を学びます。

具体的には、ROW_NUMBER() 関数を使用して、売上金額に基づいて各行に一意のランクを割り当てます。また、累計の計算方法や、より高度な分析のためのデータのパーティション分割 (partition) 方法も学びます。この実験 (lab) では、SQLite のウィンドウ関数 (window functions) について実践的な入門を提供します。

Skills Graph

売上データベースとテーブルの作成 (Create a Sales Database and Table)

この最初のステップでは、sales.db という名前の SQLite データベースと、売上データを格納するための sales という名前のテーブルを作成します。このテーブルには、製品 ID (product ID)、製品名 (product name)、および売上金額 (sales amount) の列が含まれます。

LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

まず、sales.db データベースを作成し、次のコマンドを実行して SQLite コマンドラインツールを開きます。

sqlite3 sales.db

このコマンドはデータベースファイルを作成し、SQL コマンドを実行できる SQLite シェルを開きます。次のようなプロンプトが表示されます。

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

次に、product_id、product_name、および sales_amount の列を持つ sales テーブルを作成します。sqlite> プロンプトで次の SQL コマンドを入力し、Enter キーを押します。

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

このコマンドは、sales テーブルを次のように設定します。

• product_id は、各製品の一意の識別子を表す整数 (INTEGER) です。
• product_name は、製品の名前を格納するテキストフィールド (TEXT) です。
• sales_amount は、製品の売上金額を表す整数 (INTEGER) です。

コマンドが正常に実行された場合、出力は表示されません。

サンプルデータを Sales テーブルに挿入 (Insert Sample Data into the Sales Table)

sales テーブルを作成したので、サンプルデータをいくつか追加しましょう。異なる製品とその売上金額を表す 6 つのレコードを挿入します。

sqlite> プロンプトで次のコマンドを 1 つずつ実行して、次のレコードを sales テーブルに挿入します。

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

これらのコマンドは、sales テーブルに 6 つの行を追加します。各行は、ID、名前、および売上金額を持つ製品を表します。

• INSERT INTO sales (product_id, product_name, sales_amount) は、sales テーブルの product_id、product_name、および sales_amount 列にデータを挿入することを指定します。
• VALUES (1, 'Laptop', 1200) は、各レコードに挿入される値を提供します。

データが正しく追加されたことを確認するには、次のコマンドを実行して、テーブル内のすべてのレコードを表示します。

SELECT * FROM sales;

期待される出力 (Expected Output):

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

この出力は、各レコードの product_id、product_name、および sales_amount を示しています。SELECT * コマンドは、指定されたテーブルからすべての列を取得します。

ROW_NUMBER() で行をランク付け (Rank Rows with ROW_NUMBER())

このステップでは、ROW_NUMBER() ウィンドウ関数を使用して、sales_amount に基づいて各行に一意のランクを割り当てる方法を学びます。これは、売れ筋商品 (top-selling products) を特定するのに役立ちます。

ROW_NUMBER() 関数は、結果セットのパーティション内の各行に一意の整数を割り当てます。ランクは、ORDER BY 句で指定された順序によって決定されます。

sqlite> プロンプトで次のクエリを実行します。

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

このクエリは、各製品のランクを sales_amount に基づいて降順で計算します。

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) は、sales_amount に基づいて各行にランクを割り当てます。最も高い売上金額がランク 1 になります。
• ORDER BY sales_amount DESC は、ランキングが sales_amount に基づいて降順で行われるように指定します。

期待される出力 (Expected Output):

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

ご覧のとおり、sales_rank 列には、各製品のランクが sales_amount に基づいて含まれており、最も高い売上金額 (Laptop) がランク 1 になっています。

累計を計算する (Compute Running Totals)

このステップでは、ウィンドウ関数を使用して累計 (累積和) を計算する方法を学びます。累計は、一定期間または一連の行にわたる値の合計を追跡するのに役立ちます。

累計を計算するには、SUM() 関数を OVER() 句および ORDER BY 句とともに使用して、合計が計算される順序を指定します。

まず、sale_date 列を sales テーブルに追加し、いくつかのサンプル日付を入力しましょう。sqlite> プロンプトで次のコマンドを実行します。

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

これらのコマンドは、sale_date 列を sales テーブルに追加し、各製品のサンプル日付でテーブルを更新します。

次に、sale_date で順序付けられた、時間の経過に伴う sales_amount の累計を計算しましょう。次のクエリを実行します。

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

このクエリは、sale_date で順序付けられた、時間の経過に伴う sales_amount の累計を計算します。

• SUM(sales_amount) OVER (ORDER BY sale_date) は、各 sale_date までの sales_amount の累積和を計算します。
• ORDER BY sale_date は、累計が sale_date に基づいて昇順で計算されるように指定します。

期待される出力 (Expected Output):

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

running_total 列は、各 sale_date までの sales_amount の累積和を示しています。たとえば、'2023-01-15' の累計は 1600 であり、これは '2023-01-01'、'2023-01-05'、'2023-01-10'、および '2023-01-15' からの売上高の合計です。

分析のためにデータをパーティション分割する (Partition Data for Analysis)

このステップでは、ウィンドウ関数内で PARTITION BY 句を使用してデータをパーティション分割する方法を学びます。パーティション分割を使用すると、データを論理グループに分割し、各グループ内で個別に計算を実行できます。

まず、product_category 列を sales テーブルに追加しましょう。sqlite> プロンプトで次のコマンドを実行します。

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

これらのコマンドは、product_category 列を sales テーブルに追加し、各製品のカテゴリでテーブルを更新します。

次に、PARTITION BY を使用して、各 product_category 内の sales_amount の累計を計算しましょう。次のクエリを実行します。

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

このクエリは、各 product_category 内の sales_amount の累計を、sale_date で順序付けして計算します。

• PARTITION BY product_category は、product_category に基づいてデータをパーティションに分割します。
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) は、各 product_category 内の sales_amount の累積和を、sale_date で順序付けして計算します。

期待される出力 (Expected Output):

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

running_total_by_category 列は、各 product_category 内の sales_amount の累積和を、sale_date で順序付けして示しています。各カテゴリで累計が再開されることに注意してください。

まとめ (Summary)

この実験 (Lab) では、SQLite でウィンドウ関数を使用して高度なデータ分析を実行する方法を学びました。まず、sales テーブルを作成し、サンプルデータを挿入しました。次に、ROW_NUMBER() 関数を使用して、売上高に基づいて製品をランク付けしました。また、SUM() 関数と OVER() 句を使用して累計を計算する方法、および PARTITION BY 句を使用してデータをパーティション分割し、論理グループ内で計算を実行する方法も学びました。これらのスキルは、SQLite でより複雑なデータ分析タスクを行うための基盤となります。