SQLで年ごとに集計するクエリとデータベースの活用方法

SQLで年ごとに集計する基本と応用テクニック

年次データ集計の重要ポイント

📊

基本的な集計手法

DATE_FORMAT関数とGROUP BY句を組み合わせた年次データの抽出方法

🔄

高度な集計テクニック

ROLLUP句を使用した小計・合計の自動計算

📈

パフォーマンス最適化

インデックス活用とクエリ実行計画の改善方法

このページの目次

SQLで年ごとに集計する基本と応用テクニック

SQLで年次データを抽出する基本的な方法

年次データを抽出する最も基本的な方法は、DATE_FORMAT関数とGROUP BY句を組み合わせることです。以下のクエリは、最もシンプルな年次集計の例です：


SELECT 
    DATE_FORMAT(date_column, '%Y') as year,
    COUNT(*) as count,
    SUM(amount) as total_amount
FROM 
    sales_table
GROUP BY 
    DATE_FORMAT(date_column, '%Y')
ORDER BY 
    year

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

DATE_FORMAT関数で年のみを抽出
GROUP BY句で年単位でグループ化
集計関数（COUNT、SUM）で必要な統計を算出

ROLLUP句を使用した年次データの階層的集計

より詳細な分析が必要な場合、ROLLUP句を使用することで、年次データの小計と総計を同時に取得できます：


SELECT 
    COALESCE(DATE_FORMAT(date_column, '%Y'), '合計') as year,
    department,
    COUNT(*) as count,
    SUM(amount) as total_amount
FROM 
    sales_table
GROUP BY 
    DATE_FORMAT(date_column, '%Y'),
    department WITH ROLLUP
ORDER BY 
    year, department

このクエリの特徴：

WITH ROLLUP句で自動的に小計・合計を生成
COALESCEで NULL値を意味のある文字列に置換
部門別の年次集計と総計を一度に取得

年次データのパフォーマンス最適化テクニック

大規模なデータベースで年次集計を行う場合、以下のような最適化が重要です：


-- インデックスの作成
CREATE INDEX idx_date_dept ON sales_table(date_column, department)
-- パーティショニングの活用
CREATE TABLE sales_table (
    id INT,
    date_column DATE,
    department VARCHAR(50),
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(date_column)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
)

年次データの前年比較と成長率分析

LAG関数を使用して、前年比較や成長率を計算する方法を紹介します：


SELECT 
    current_year.year,
    current_year.total_amount,
    ROUND((current_year.total_amount - prev_year.total_amount) 
        / prev_year.total_amount * 100, 2) as growth_rate
FROM (
    SELECT 
        DATE_FORMAT(date_column, '%Y') as year,
        SUM(amount) as total_amount
    FROM 
        sales_table
    GROUP BY 
        DATE_FORMAT(date_column, '%Y')
) current_year
LEFT JOIN (
    SELECT 
        DATE_FORMAT(date_column, '%Y') as year,
        SUM(amount) as total_amount
    FROM 
        sales_table
    GROUP BY 
        DATE_FORMAT(date_column, '%Y')
) prev_year ON current_year.year = prev_year.year + 1
ORDER BY 
    current_year.year

年次データのビジュアライゼーションのためのSQL活用

データ可視化ツールと組み合わせるための効果的なクエリ設計について説明します：


WITH monthly_data AS (
    SELECT 
        DATE_FORMAT(date_column, '%Y') as year,
        DATE_FORMAT(date_column, '%m') as month,
        SUM(amount) as monthly_amount
    FROM 
        sales_table
    GROUP BY 
        DATE_FORMAT(date_column, '%Y'),
        DATE_FORMAT(date_column, '%m')
)
SELECT 
    year,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'month', month,
            'amount', monthly_amount
        )
    ) as monthly_trend
FROM 
    monthly_data
GROUP BY 
    year
ORDER BY 
    year