MariaDB の Window 関数：データ分析の可能性を広げる強力なツール

MariaDB の Window 関数：SQL ステートメントと構造

基本的な構文

SELECT
    expression,
    window_function(expression) OVER (
        [PARTITION BY partition_expression]
        ORDER BY order_expression [ASC | DESC]
        [window_frame]
    ) AS alias
FROM table_name;

各要素の解説

• expression: 集計対象となる列
• window_function: 使用する Window 関数
• OVER: Window 関数を適用する範囲を指定
• PARTITION BY: データをグループ化する列
• ORDER BY: データを並べ替える列
• ASC: 昇順
• DESC: 降順
• window_frame: 結果の範囲を指定 (省略可能)
• alias: 結果列のエイリアス

代表的な Window 関数

具体的な例

例1：各部門の売上合計と平均売上

SELECT
    department_name,
    SUM(sales) OVER (PARTITION BY department_name) AS total_sales,
    AVG(sales) OVER (PARTITION BY department_name) AS avg_sales
FROM sales_table;

例2：各商品の過去3ヶ月間の売上推移

SELECT
    product_name,
    sales_date,
    SUM(sales) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS total_sales_3m
FROM sales_table;

まとめ

MariaDB の Window 関数：サンプルコード集

SELECT
    department_name,
    employee_name,
    sales,
    RANK() OVER (PARTITION BY department_name ORDER BY sales DESC) AS sales_rank
FROM sales_table;

各商品の過去1年間の売上推移と前年比

SELECT
    product_name,
    sales_date,
    sales,
    SUM(sales) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 12 PRECEDING AND CURRENT ROW
    ) AS total_sales_1y,
    (
        SUM(sales) OVER (
            ORDER BY sales_date
            ROWS BETWEEN 12 PRECEDING AND CURRENT ROW
        )
        - SUM(sales) OVER (
            ORDER BY sales_date
            ROWS BETWEEN 24 PRECEDING AND CURRENT ROW
        )
    ) / SUM(sales) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 24 PRECEDING AND CURRENT ROW
    ) AS yoy_growth
FROM sales_table;

顧客の累計購入金額と平均購入金額

SELECT
    customer_name,
    SUM(sales) OVER (ORDER BY sales_date) AS total_sales,
    AVG(sales) OVER (ORDER BY sales_date) AS avg_sales
FROM sales_table;

各商品の在庫数と発注点

SELECT
    product_name,
    stock_quantity,
    SUM(stock_quantity) OVER (ORDER BY product_name) AS total_stock,
    (
        SUM(stock_quantity) OVER (ORDER BY product_name)
        - AVG(sales) OVER (ORDER BY product_name, sales_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) * 7
    ) AS reorder_point
FROM inventory_table;

ユーザーのセッション数と平均滞在時間

SELECT
    user_id,
    session_id,
    session_start_time,
    session_end_time,
    COUNT(*) OVER (PARTITION BY user_id) AS session_count,
    AVG(session_end_time - session_start_time) OVER (PARTITION BY user_id) AS avg_session_duration
FROM session_table;

テスト結果の移動平均と標準偏差

SELECT
    test_name,
    score,
    AVG(score) OVER (
        ORDER BY test_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_average,
    STDDEV(score) OVER (
        ORDER BY test_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS standard_deviation
FROM test_results_table;

株価の移動平均とボリンジャーバンド

SELECT
    stock_name,
    stock_date,
    stock_price,
    AVG(stock_price) OVER (
        ORDER BY stock_date
        ROWS BETWEEN 20 PRECEDING AND CURRENT ROW
    ) AS moving_average,
    STDDEV(stock_price) OVER (
        ORDER BY stock_date
        ROWS BETWEEN 20 PRECEDING AND CURRENT ROW
    ) AS standard_deviation,
    (
        moving_average + 2 * standard_deviation
    ) AS upper_band,
    (
        moving_average - 2 * standard_deviation
    ) AS lower_band
FROM stock_prices_table;

Web サイトのページビュー数と直帰率

SELECT
    page_name,
    page_views,
    SUM(page_views) OVER (ORDER BY page_name) AS total_page_views,
    (
        1 - COUNT(DISTINCT

MariaDB の Window 関数：その他の方法

Window 関数では、ROWS BETWEEN だけでなく、以下のフレーム指定も可能です。

• RANGE BETWEEN: 指定された範囲の行を対象
• GROUPS BETWEEN: 指定されたグループの行を対象
• PRECEDING: 現在の行から過去方向に指定された行を対象
• FOLLOWING: 現在の行から未来方向に指定された行を対象

これらのフレーム指定を組み合わせることで、より柔軟な集計処理を実現できます。

例：過去3ヶ月間、かつ前年同月比10%以上の売上成長の商品

SELECT
    product_name,
    sales_date,
    sales,
    SUM(sales) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS total_sales_3m,
    (
        SUM(sales) OVER (
            ORDER BY sales_date
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        )
        - SUM(sales) OVER (
            ORDER BY sales_date
            ROWS BETWEEN 15 PRECEDING AND 12 PRECEDING