SQLで日付列を使って前年の売上を計算し、売上達成率を算出しよう - BigQueryでの実践

はじめに

この記事では、BigQueryを使用してデータが作成された日付を記録し、売上データを追加して前年の売上を計算し、売上達成率を算出する方法について説明します。さらに、計算結果を元のテーブルに追加する方法と別のテーブルとして保存する方法も紹介します。

サンプルデータセットの作成

まず、サンプルデータセットを作成します。データセットのパスは data-sandbox-421715.db_test_001 を使用します。

以下のクエリを実行して、サンプルデータセットを作成します。

-- データセットの作成
CREATE SCHEMA IF NOT EXISTS `data-sandbox-421715.db_test_001`;

-- テーブルの作成
CREATE TABLE `data-sandbox-421715.db_test_001.sales_table` (
    id INT64,
    product_name STRING,
    created_date DATE,
    sales FLOAT64
);

データの挿入

次に、サンプルデータを挿入します。

-- サンプルデータの挿入
INSERT INTO `data-sandbox-421715.db_test_001.sales_table` (id, product_name, created_date, sales)
VALUES 
    (1, 'Product A', DATE '2023-07-01', 1000.0),
    (2, 'Product B', DATE '2023-07-01', 2000.0),
    (3, 'Product C', DATE '2023-07-01', 1500.0),
    (4, 'Product A', DATE '2022-07-01', 1200.0),
    (5, 'Product B', DATE '2022-07-01', 2200.0),
    (6, 'Product C', DATE '2022-07-01', 1400.0);

年を抽出し、前年の売上を計算し、売上達成率を算出する

次に、各レコードの created_date から年を抽出し、前年の売上を計算して新しい列に追加し、売上達成率を算出し、パーセント記号を付けます。以下のクエリを実行します。

-- 前年の売上を計算して追加
-- WITH句はサブクエリのように扱われ、一時的な結果セットを生成します。

WITH sales_with_year AS (
    -- sales_with_year は最初のサブクエリで、各レコードの created_date から年を抽出し、year 列を追加します。
    SELECT 
        id,
        product_name,
        created_date,
        sales,
        EXTRACT(YEAR FROM created_date) AS year
    FROM 
        `data-sandbox-421715.db_test_001.sales_table`
),
sales_with_last_year AS (
    -- sales_with_last_year は2つ目のサブクエリで、sales_with_yearを自己結合し、前年の売上を取得します。
    SELECT 
        curr.id,
        curr.product_name,
        curr.created_date,
        curr.sales AS current_sales,
        curr.year,
        prev.sales AS last_year_sales
    FROM 
        sales_with_year AS curr
    LEFT JOIN 
        sales_with_year AS prev
    ON 
        curr.product_name = prev.product_name AND curr.year = prev.year + 1
)
-- 最後に、sales_with_last_year から必要な列を選択し、売上達成率を計算して追加します。
SELECT 
    id,
    product_name,
    created_date,
    current_sales AS sales_2023,
    last_year_sales AS sales_2022,
    IFNULL(last_year_sales, 0) AS sales_2022_filled, -- NULLの場合は0にする
    IFNULL(CONCAT(FLOOR(current_sales / last_year_sales * 100), '%'), '0%') AS sales_achievement_rate -- 売上達成率を計算して小数点以下を切り捨て、パーセント記号を追加
FROM 
    sales_with_last_year;

結果を元のテーブルに格納する

計算結果を元のテーブルに格納するには、元のテーブルに新しい列を追加します。

-- 元のテーブルに新しい列を追加
ALTER TABLE `data-sandbox-421715.db_test_001.sales_table`
ADD COLUMN sales_achievement_rate STRING;

その後、計算結果を元のテーブルに更新します。

-- 計算結果を元のテーブルに更新
UPDATE `data-sandbox-421715.db_test_001.sales_table` AS t
SET t.sales_achievement_rate = s.sales_achievement_rate
FROM (
    WITH sales_with_year AS (
        SELECT 
            id,
            product_name,
            created_date,
            sales,
            EXTRACT(YEAR FROM created_date) AS year
        FROM 
            `data-sandbox-421715.db_test_001.sales_table`
    ),
    sales_with_last_year AS (
        SELECT 
            curr.id,
            curr.product_name,
            curr.created_date,
            curr.sales AS current_sales,
            curr.year,
            prev.sales AS last_year_sales
        FROM 
            sales_with_year AS curr
        LEFT JOIN 
            sales_with_year AS prev
        ON 
            curr.product_name = prev.product_name AND curr.year = prev.year + 1
    )
    SELECT 
        id,
        product_name,
        created_date,
        IFNULL(CONCAT(FLOOR(current_sales / last_year_sales * 100), '%'), '0%') AS sales_achievement_rate
    FROM 
        sales_with_last_year
) AS s
WHERE t.id = s.id;

結果を別のテーブルに保存する

結果を別のテーブルに保存するには、以下のクエリを実行します。

-- 結果を別のテーブルに保存
CREATE TABLE `data-sandbox-421715.db_test_001.sales_with_achievement` AS
SELECT 
    id,
    product_name,
    created_date,
    sales_2023,
    sales_2022,
    sales_achievement_rate
FROM (
    WITH sales_with_year AS (
        SELECT 
            id,
            product_name,
            created_date,
            sales,
            EXTRACT(YEAR FROM created_date) AS year
        FROM 
            `data-sandbox-421715.db_test_001.sales_table`
    ),
    sales_with_last_year AS (
        SELECT 
            curr.id,
            curr.product_name,
            curr.created_date,
            curr.sales AS current_sales,
            curr.year,
            prev.sales AS last_year_sales
        FROM 
            sales_with_year AS curr
        LEFT JOIN 
            sales_with_year AS prev
        ON 
            curr.product_name = prev.product_name AND curr.year = prev.year + 1
    )
    SELECT 
        id,
        product_name,
        created_date,
        current_sales AS sales_2023,
        last_year_sales AS sales_2022,
        IFNULL(CONCAT(FLOOR(current_sales / last_year_sales * 100), '%'), '0%') AS sales_achievement_rate
    FROM 
        sales_with_last_year
);

このクエリでは、計算結果を新しいテーブル sales_with_achievement に保存しています。

まとめ

この記事では、BigQueryを使用してデータが作成された日付を記録し、売上データを追加して前年の売上を計算し、売上達成率を算出してパーセント表示にする方法を説明しました。また、計算結果を元のテーブルに格納する方法と別のテーブルとして保存する方法も紹介しました。これにより、データが作成された日付や前年の売上、売上達成率を簡単に追跡できるようになります。