SQLで月毎に集計するための基礎から実践的なクエリ活用術

SQLで月毎に集計する方法と実践テクニック

月次データ集計の基本知識

📊

集計の基本

GROUP BY句とDATE_FORMAT関数を使用した月次データの集計方法

📈

データ分析活用

売上データや会員登録数など、実務での活用例

⚡

パフォーマンス最適化

大規模データの効率的な集計方法とインデックス活用

このページの目次

SQLで月毎に集計する方法と実践テクニック

SQLで月毎に集計する基本的なクエリ構文

月次データを集計する最も基本的な方法は、DATE_FORMAT関数とGROUP BY句を組み合わせることです。以下のクエリは、売上データを月毎に集計する例です。


SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) as count,
    SUM(amount) as total_amount
FROM 
    sales_table
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 
    month

このクエリでは、以下の要素が重要です：

DATE_FORMAT関数で日付を年月形式に変換
GROUP BY句で月毎にデータをグループ化
集計関数（COUNT、SUM）で必要な統計を算出

SQLで月毎の集計でゼロ件の月も表示する方法

実務では、データが存在しない月も「0」として表示したいケースが多くあります。これには、generate_series関数やCTE（Common Table Expression）を使用します。


WITH RECURSIVE date_series AS (
    SELECT DATE_TRUNC('month', '2024-01-01'::date) AS month
    UNION ALL
    SELECT month + interval '1 month'
    FROM date_series
    WHERE month < '2024-12-31'::date
)
SELECT 
    TO_CHAR(d.month, 'YYYY-MM') AS month,
    COALESCE(COUNT(s.id), 0) as count
FROM 
    date_series d
    LEFT JOIN sales_table s ON DATE_TRUNC('month', s.order_date) = d.month
GROUP BY 
    d.month
ORDER BY 
    d.month

SQLで月毎に集計する際のパフォーマンス最適化

大規模データベースでの月次集計では、パフォーマンスが重要な課題となります。以下の方法で最適化が可能です：

インデックスの活用


CREATE INDEX idx_order_date ON sales_table (order_date)

パーティショニングの利用


CREATE TABLE sales_table (
    id INT,
    order_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (order_date)

中間集計テーブルの作成


CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) as total_amount
FROM 
    sales_table
GROUP BY 
    DATE_TRUNC('month', order_date)

SQLで月毎に集計する高度な分析テクニック

より詳細な分析のために、以下のような高度なテクニックも活用できます：

前年同月比の計算


SELECT 
    current_month.month,
    current_month.sales,
    previous_year.sales as prev_year_sales,
    (current_month.sales - previous_year.sales) / previous_year.sales * 100 as growth_rate
FROM 
    (SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
     SUM(amount) as sales
     FROM sales_table
     GROUP BY DATE_FORMAT(order_date, '%Y-%m')) current_month
LEFT JOIN 
    (SELECT DATE_FORMAT(DATE_ADD(order_date, INTERVAL 1 YEAR), '%Y-%m') AS month,
     SUM(amount) as sales
     FROM sales_table
     GROUP BY DATE_FORMAT(DATE_ADD(order_date, INTERVAL 1 YEAR), '%Y-%m')) previous_year
ON current_month.month = previous_year.month

SQLで月毎に集計する際のエラー対処法と注意点

月次集計時によく発生する問題とその解決方法をまとめます：

タイムゾーンの考慮


SET time_zone = '+09:00'  -- 日本時間に設定

NULL値の処理


SELECT 
    DATE_FORMAT(COALESCE(order_date, CURRENT_DATE), '%Y-%m') AS month,
    COUNT(*) as count
FROM 
    sales_table
GROUP BY 
    DATE_FORMAT(COALESCE(order_date, CURRENT_DATE), '%Y-%m')

データ型の統一


-- 日付形式の統一
SELECT 
    DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%Y-%m') AS month,
    COUNT(*) as count
FROM 
    sales_table
GROUP BY 
    DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%Y-%m')

これらのテクニックを組み合わせることで、より正確で効率的な月次データの集計が可能になります。