SQLiteで売上分析をレベルアップ！月別集計で売上傾向を掴む

SQLiteで月ごとにグループ化する方法

手順

1. 必要な列を選択する
   • 集計したい列と、月ごとに区別する列を選択します。
2. 月ごとにグループ化する
   • GROUP BY句を使用して、月ごとにデータをグループ化します。
   • strftime('%Y-%m')関数を使用して、日付列を「YYYY-MM」形式の文字列に変換します。
   • 例：

     SELECT strftime('%Y-%m') AS month, category, SUM(sales) AS total_sales
     FROM sales
     GROUP BY strftime('%Y-%m'), category;
     

3. 集計関数を使用する
   • 集計したい列に対して、集計関数を使用します。
4. ORDER BY句でソートする（オプション）
   • 必要に応じて、ORDER BY句を使用して結果をソートします。

例

salesテーブルに、以下のデータがあるとします。

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

この結果、2024年1月は家具が400件、電子機器が200件、2024年2月は家具が250件、電子機器が500件販売されたことがわかります。

• 複数の列でグループ化することもできます。その場合は、,で区切って列を指定します。
• 集計関数以外にも、AVG、MIN、MAXなどの関数を使用することもできます。
• 上記の例では、strftime('%Y-%m')関数を使用して月をグループ化していますが、strftime関数で別の形式の文字列に変換することもできます。

-- サンプルデータを作成
CREATE TABLE sales (
  date TEXT,
  category TEXT,
  sales INTEGER
);

INSERT INTO sales VALUES ('2023-10-01', 'Furniture', 100);
INSERT INTO sales VALUES ('2023-10-05', 'Electronics', 200);
INSERT INTO sales VALUES ('2023-10-10', 'Furniture', 300);
INSERT INTO sales VALUES ('2023-11-01', 'Electronics', 500);
INSERT INTO sales VALUES ('2023-11-15', 'Furniture', 250);

-- 月ごとの売上合計と商品カテゴリをグループ化
SELECT
  strftime('%Y-%m') AS month,
  category,
  SUM(sales) AS total_sales
FROM sales
GROUP BY strftime('%Y-%m'), category;

1. salesテーブルを作成します。
2. サンプルデータをテーブルに挿入します。
3. strftime('%Y-%m')関数を使用して月ごとにデータをグループ化し、categoryとsalesの合計を計算します。

結果

month | category | total_sales
-------|------------|-------------
2023-10 | Furniture | 400
2023-10 | Electronics | 200
2023-11 | Furniture | 250
2023-11 | Electronics | 500

SELECT month, category, SUM(sales) AS total_sales
FROM (
  SELECT strftime('%Y-%m') AS month, category, sales
  FROM sales
) AS sales_by_month
GROUP BY month, category;

説明:

この方法は、salesテーブルから月とカテゴリごとにデータを抽出するサブクエリを作成し、その結果をメインクエリでグループ化します。

利点:

• 複雑なクエリをより読みやすく分割できる

欠点:

• サブクエリがネストされるため、クエリのパフォーマンスがわずかに低下する可能性がある

WITH句を使用する

WITH sales_by_month AS (
  SELECT strftime('%Y-%m') AS month, category, sales
  FROM sales
)
SELECT month, category, SUM(sales) AS total_sales
FROM sales_by_month
GROUP BY month, category;

この方法は、WITH句を使用して一時的な結果セット (CTE) を定義し、その結果セットをメインクエリで使用します。

• サブクエリよりも簡潔で読みやすい

• WITH句はSQLite 3.8以降でのみ使用可能

仮想テーブルを使用する

CREATE TABLE IF NOT EXISTS sales_by_month (
  month TEXT,
  category TEXT,
  sales INTEGER
);

INSERT INTO sales_by_month
SELECT strftime('%Y-%m') AS month, category, sales
FROM sales;

SELECT month, category, SUM(sales) AS total_sales
FROM sales_by_month
GROUP BY month, category;

この方法は、salesテーブルから月とカテゴリごとにデータを抽出する仮想テーブルを作成し、その仮想テーブルに対してクエリを実行します。

• 複雑なクエリを永続的なテーブルに分割できる

• 仮想テーブルを作成する必要があるため、処理が少しオーバーヘッドになる

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

どの方法を選択するかは、個々のニーズと好みによって異なります。

• 永続的なテーブルにクエリ結果を保存したい場合は、3番目の方法がおすすめです。
• 複雑なクエリをより読みやすく分割したい場合は、2番目の方法がおすすめです。
• シンプルでわかりやすい方法が必要であれば、最初の方法がおすすめです。