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

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

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

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

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

1. 集計関数
   SUM(), AVG(), MAX(), MIN(), COUNT() など。
2. ランキング関数
   ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()。
3. オフセット関数
   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,
    RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS sales_rank
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;

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

まとめ

ウインドウ関数は、データ分析に便利なツールです。集計、ランキング、差分計算など、様々な操作を行うことができ、複雑なデータ処理を簡潔に表現することができます。