SQL Academy · レッスン

分析クエリの作成

Q: 「分析クエリの作成」で何を学びますか？

メトリクスをスライス、ダイス、ロールアップします ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

メトリクスをスライス、ダイス、ロールアップします

「分析クエリの作成」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

分析クエリとは

分析クエリは、単純な行の検索にとどまりません。顧客42はどの注文を行ったかを尋ねる代わりに、地域と四半期ごとの総収益はいくらかや、今月は先月と比べてどうかといったことを尋ねます。

スタースキーマを基盤とするデータウェアハウスでは、分析クエリによってファクトをスライス（1つのディメンションでフィルタリング）、ダイス（複数のディメンションでフィルタリング）、ロールアップ（より粗い粒度に集約）し、ビジネス上の洞察を引き出します。

スタースキーマのおさらい

スタースキーマには、中央に1つのファクトテーブル（例：fact_sales）があり、その周囲をディメンションテーブル（例：dim_date、dim_product、dim_store）が取り囲みます。分析クエリでは、現在の分析に必要なディメンションをファクトテーブルにJOINします。

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

スライス：1つのディメンションでフィルタリング

スライスとは、1つのディメンションの単一の値に結果セットを限定することです。たとえば、2024年のデータだけを調べる場合が該当します。WHERE句がスライスに使用するツールです。

早い段階でスライスすると、データベースが集約する行数を減らせるため、大規模なファクトテーブルでもクエリを高速に保てます。

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

ダイス：複数のディメンションでフィルタリング

ダイスとは、2つ以上のディメンションに同時にフィルターを適用することです。たとえば、第1四半期の北部地域における電子機器の売上を調べる場合が該当します。WHERE条件を追加するたびに、より小さなデータキューブが切り出されます。

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

ロールアップ：より高い粒度への集約

ロールアップとは、詳細な粒度（店舗ごとの日次売上）から、より粗い粒度（地域ごとの月次売上）へ移ることです。下位レベルのGROUP BY列を削除し、再集約することで実行できます。

ROLLUP修飾子を使うと、複数のUNION ALLブロックを記述せずに、1つのクエリで小計と総計を生成できます。

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

LAGによる期間比較

最も一般的な分析パターンの1つは、ある指標を前の期間の同じ指標と比較することです。ウィンドウ関数LAG()を使うと、自己結合を行わずに前の行の値を現在の行へ直接取り込めます。

ここでは、前月比の収益成長率をパーセントで計算します。

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

SUM OVERによる累計

累計（累積合計）とは、定義した順序に従って、各行の値をそれ以前のすべての行の合計に加えることです。1年間の累積収益を追跡したり、予算の消化状況を確認したりするのに適しています。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWフレーム句を使うと、ウィンドウを明示的かつ曖昧さなく指定できます。

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

DENSE_RANKによるディメンションの順位付け

順位付けを使うと、グループ内で成績が上位または下位の対象を見つけられます。DENSE_RANK()は同順位がある場合でも順位の間に欠番を作らず、連続した順位を割り当てるため、BIレポートのランキングには推奨される選択肢です。

順位付けした結果をCTEでラップし、順位でフィルタリングすると、トップNパターンをすっきりと読みやすく記述できます。

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

ウィンドウSUMによる構成比

商品の絶対的な収益を知ることは有用ですが、それがカテゴリ収益の38 %を占めると分かれば、より実用的な判断につながります。パーティション全体に対するウィンドウSUM()を使うと、サブクエリとのJOINなしで分母を取得できます。

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

トレンドを平滑化する移動平均

日次や週次の売上値にはノイズが含まれます。移動平均は短期的な変動を平滑化し、根底にあるトレンドを見やすくします。ここでは、スライディングウィンドウフレームを使って3か月の移動平均を計算します。

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

すべてのディメンションの組み合わせに対するCUBE

CUBEはROLLUPを拡張したもので、階層的なロールアップの経路だけでなく、指定したディメンションのあらゆる組み合わせについて小計を計算します。これにより、すべてのディメンションを横断した概要を1回の処理で生成できます。ユーザーが自由に軸を切り替えられる多次元ダッシュボードに便利です。

グループ化列のNULLは、そのディメンションのすべての値を意味します。GROUPING()を使うと、データ内に意図的に存在するNULLとロールアップによるNULLを区別できます。

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

結果を1つのディメンション値に限定する操作はどれですか

データウェアハウスで使われる分析クエリの用語について、理解度を確認しましょう。

まとめ：分析クエリの記述

このレッスンでは、スタースキーマに対して分析クエリを記述するための主要なパターンについて学びました。

スライス — WHEREで1つのディメンションをフィルタリングし、特定のセグメントに焦点を当てます。
ダイス — 複数のディメンションを同時にフィルタリングし、正確なデータキューブを切り出します。
ロールアップ — より粗い粒度に集約します。複数レベルの小計にはROLLUPまたはCUBEを使用します。
LAG / LEAD — 自己結合なしで期間ごとの比較を行います。
累計 & 移動平均 — ウィンドウフレームによって、累積した指標や平滑化した指標を計算します。
DENSE_RANK — パーティション内で、すっきりとトップNの順位付けを行います。
構成比 % — 構成比の計算で、ウィンドウSUMを分母として使用します。

これらのパターンを組み合わせることで、本番のデータウェアハウスで必要となるBIおよびレポート要件の大部分に対応できます。

よくある質問