便利！SQLのウインドウ関数について

SQLのウインドウ関数（Window Function）は、行の集合に対して計算を行い、結果を同じ集合の各行に返す関数です。

これは集約関数に似ていますが、集約関数がグループ全体の結果を一つの行に対して返すのに対し、ウインドウ関数は各行ごとに結果を返す点が異なります。

ウインドウ関数の主な利点は、集約計算やランキング、移動平均などの操作をより柔軟に行えることです。以下に、ウインドウ関数の詳細とサンプルSQLを示します。

Contents

主なウインドウ関数の種類
基本的な構文
サンプルデータ
集計関数の使用例
ランキング関数の使用例
オフセット関数の使用例
- まとめ

主なウインドウ関数の種類

集計関数
SUM(), AVG(), MAX(), MIN(), COUNT() など。
ランキング関数
ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()。
オフセット関数
LAG(), LEAD()。

基本的な構文

ウインドウ関数は、以下の構文で使用します。

<ウインドウ関数> OVER (
  [PARTITION BY <partition_clause>]
  [ORDER BY <order_clause>]
  [<window_frame>]
)

PARTITION BY
データをグループ化する列を指定します。
ORDER BY
並び順を指定します。
window_frame
ウインドウフレームを指定します（省略可能）。

サンプルデータ

以下のサンプルテーブル sales を使用します。

CREATE TABLE sales (
    salesperson VARCHAR(50),
    sales_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (salesperson, sales_date, amount) VALUES
('Alice', '2023-01-01', 100.00),
('Alice', '2023-01-02', 150.00),
('Bob', '2023-01-01', 200.00),
('Bob', '2023-01-03', 300.00),
('Charlie', '2023-01-01', 50.00),
('Charlie', '2023-01-02', 75.00);

集計関数の使用例

各営業担当者ごとの累積売上を計算します。

SELECT
    salesperson,
    sales_date,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sales_date) AS cumulative_sales
FROM
    sales;

このクエリは、各営業担当者ごとに売上の累積を計算し、結果を各行に返します。

オフセット関数の使用例

前の売上を参照して差分を計算します。

SELECT
    salesperson,
    sales_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY salesperson ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (PARTITION BY salesperson ORDER BY sales_date) AS amount_diff
FROM
    sales;

このクエリは、前の売上を参照し、現在の売上との差分を計算します。