PR

【SQL】window関数の基本/意味や使い方を3分で解説

Database

window関数は、データベースのテーブル内の行に関連する情報を取得するための特別な種類のSQL関数です。

参考 SQLとは?(初心者向け解説)

名前に「window」と付いているのは、この関数が「視点(ウインドウ)」を移動させながら、その「ウインドウ」内のデータに基づいて計算を行うからです。

具体的には、ある行を中心に、その前後の行を「window」として捉え、その範囲内のデータを使って計算を行うイメージ。例えば、4日間の平均売上を求めたいとき、1日目、2日目、3日目、4日目の売上をウインドウ(=計算範囲)として捉え、その平均を計算する、というようなことがwindow関数で可能です。

window関数
図1:window関数のイメージ

このページではwindow関数の基本を1からわかりやすく初心者向けに解説します。

このページで学べる内容
  • window関数の基本概念
    • window関数と通常の集計関数の違い
  • 基本的なwindow関数の概要
    • OVER句とその構成要素
    • window関数の実際の例

プログラマーやシステムエンジニアを目指す方であれば知らないと恥ずかしい基本知識です。是非最後までご覧ください。

参考 【初心者向け】SQLの基本構文ルール

スポンサーリンク

window関数とは?

window関数は、SQLの強力な機能の1つで、各行に対して、ある「window」と呼ばれる範囲内の行を基に計算を行う関数です。これにより、各行の相対的な情報、たとえば「この行の前の行の値は何か?」や「このグループ内でのこの行のランクは?」などを求めることができます。

window関数は通常の集計関数と異なり、結果セットの行数を変えずに各行に対して情報を提供します。

window関数の構文ルール

window関数を使用する際の基本的な構文は以下のようになります。

<関数名>(<式>) OVER (
    PARTITION BY <分割列>
    ORDER BY <並び替え列>
    <フレーム範囲指定>
)
  1. <関数名>(<式>):
    • 使用するwindow関数の名前と、その関数に渡す引数または式。
    • OVER はwindow関数を利用しますよ!という宣言・印だと理解すればOK。
  2. PARTITION BY <分割列>:
    • PARTITION BYオプションを使って、データを特定の列の値に基づいてグループに分けることができる。→これにより、各グループ内でwindow関数が適用される。
    • GROUP BY と同じようなイメージでOK。
  3. ORDER BY <並び替え列>:
    • ウインドウ内のデータの順序を定義します。この順序は、ランキング関数や前後の行との比較を行う関数など、関数の動作に影響を与えます。
  4. <フレーム範囲指定>:
    • window関数がどの範囲のデータを考慮して計算を行うかを指定します。例えば「現在の行から2行前まで」や「現在の行から次の行まで」といった具体的な範囲を定義できます。指定しない場合は、PARTITION全体が考慮されるのがデフォルトです。

と、文字だけではイメージしづらいかと思いますので、具体例を用いながらご説明します。

サンプル studentsテーブルの例

| id | name    | score |
|----|---------|-------|
| 1  | Alice   | 85    |
| 2  | Bob     | 90    |
| 3  | Charlie | 85    |
| 4  | David   | 92    |
| 5  | Eve     | 88    |
|----|---------|-------|

↑のテーブルを例に説明します。このテーブルは5人の生徒のテストのスコアが記録されています。

ここでは代表的なwindow関数の1つ、ROW_NUMBER()を使用してスコアの高い順にランキングをつけたいと考えてみます。その例が↓。

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM
  students;

/* 結果
|---------|-------|------|
| name    | score | rank |
|---------|-------|------|
| David   | 92    | 1    |
| Bob     | 90    | 2    |
| Eve     | 88    | 3    |
| Alice   | 85    | 4    |
| Charlie | 85    | 5    |
|---------|-------|------|
*/

参考 SELECT文の基本

  • ROW_NUMBER()関数は、指定されたORDER BYの順序に従って、各行にユニークな連番を割り当てる。
  • OVER (ORDER BY score DESC)部分は、スコアを降順に並べ替えて、その順番に従って連番を付けることを指示。
  • 結果、最もスコアが高いDavidが1位、次に高いBobが2位というようにランキングが付けられる。AliceとCharlieは同じスコアですが、ROW_NUMBER()はユニークな値を割り当てるため、異なるランクが付けられています。
window関数の基本
図2:window関数の基本

この基本的な動作を理解することで、他のウインドウ関数もより理解しやすくなるでしょう。

【補足】PARTITION

PARTITION BY を使うことで、window関数を特定のカテゴリやグループごとに適用することができます。先ほどの、students テーブルに class 列を追加し、その上で ROW_NUMBER() を使用してクラスごとに順番をつけてみましょう。

↓のテーブルを利用して、クラスごとにスコアの高い順にランキングをつけてみます。

/* 利用するテーブル
|----|---------|-------|-------|
| id | name    | score | class |
|----|---------|-------|-------|
| 1  | Alice   | 85    | A     |
| 2  | Bob     | 90    | B     |
| 3  | Charlie | 85    | A     |
| 4  | David   | 92    | B     |
| 5  | Eve     | 88    | A     |
|----|---------|-------|-------|
*/

-- クエリ
SELECT
  name,
  score,
  class,
  ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class
FROM
  students
ORDER BY
  class, rank_in_class;

/* 結果
| name    | score | class | rank_in_class |
|---------|-------|-------|---------------|
| Eve     | 88    | A     | 1             |
| Alice   | 85    | A     | 2             |
| Charlie | 85    | A     | 3             |
| David   | 92    | B     | 1             |
| Bob     | 90    | B     | 2             |
|---------|-------|-------|---------------|
*/

解説

window関数/PARTITION
図3:window関数/PARTITION
  • PARTITION BY class によって、データはクラスごとに分けられる→結果、各クラス内で独立したランキングが生成される。
  • ORDER BY score DESC により、各クラス内でのランキングはスコアの降順にソートされる。
    →クラスAではEveが1位、クラスBではDavidが1位に。

このように、PARTITION BY を使うことで、ウインドウ関数を特定のカテゴリやグループごとに適用することができます。

フレーム範囲指定

フレーム範囲指定は、window関数を適用する際の対象範囲をより細かく制御するためのものです。通常、window関数は特定の行に基づいて、その行を中心に前後の範囲で計算を行いますが、フレーム範囲指定を使うことでその計算範囲を具体的に定義することができます。

<関数名>(<式>) OVER (
    PARTITION BY <分割列>
    ORDER BY <並び替え列>
    ROWS <範囲指定>
)

ROWS の代わりに RANGE も利用できますが、その動作は異なります。ここでは、まず ROWS を用いたフレーム範囲指定について詳しく説明します。

以下は、フレーム範囲指定の一般的な使用方法の例です。

  1. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING:
    • 現在の行を中心に、1行前と1行後を含む範囲で関数を適用します。
  2. ROWS UNBOUNDED PRECEDING:
    • 現在の行から、最初の行までの範囲で関数を適用します。
  3. ROWS 2 FOLLOWING:
    • 現在の行から、2行後までの範囲で関数を適用します。

サンプル 3日移動平均の計算

/* 利用するsalesテーブル
|------------|--------------|
| date       | sales_amount |
|------------|--------------|
| 2023-01-01 | 100          |
| 2023-01-02 | 110          |
| 2023-01-03 | 90           |
| 2023-01-04 | 95           |
| 2023-01-05 | 105          |
|------------|--------------|
*/

-- 各日の売上の3日移動平均を計算するためのSQLクエリ
SELECT
  date,
  sales_amount,
  AVG(sales_amount) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg_3days
FROM
  sales;

/* 結果
|------------|--------------|------------------|
| date       | sales_amount | moving_avg_3days |
|------------|--------------|------------------|
| 2023-01-01 | 100          | 105              |
| 2023-01-02 | 110          | 100              |
| 2023-01-03 | 90           | 98.33            |
| 2023-01-04 | 95           | 96.67            |
| 2023-01-05 | 105          | 100              |
|------------|--------------|------------------|
*/

解説

  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING によって、各日の売上の1日前、当日、1日後の3日間のデータを対象として平均を計算。

フレーム範囲指定を利用することで、累積計算や移動平均、前後のデータとの差分計算など、さまざまな集計処理を行うことができます。

よく使われるwindow関数の紹介

window関数は非常に多岐にわたるため、ここでは特によく使われるwindow関数をいくつか使い方を簡潔に紹介します。

1. ROW_NUMBER()

  • 説明: 各行に一意の連続する番号を割り当てます。主にランキングや行番号付けに使用されます。
  • : 各商品の売上順にランキングをつける場合
ROW_NUMBER() OVER (ORDER BY sales_amount DESC)

さらに詳しく→ ROW_NUMBER関数 

2. RANK()

  • 説明: 各行にランキングを割り当てますが、同じ値を持つ行には同じランクを割り当て、次のランクはスキップされます。
  • : 各学生のテストスコアのランキングを付ける場合
RANK() OVER (ORDER BY score DESC)

3. DENSE_RANK()

  • 説明: RANK()と同様にランキングを割り当てますが、同じ値を持つ行には同じランクを割り当て、次のランクはスキップされません。
  • : スポーツのポイントランキングで、同点者がいても次の順位をスキップせずに付ける場合
DENSE_RANK() OVER (ORDER BY points DESC)

4. NTILE(n)

  • 説明: データを指定したnの数だけの等しいサイズのバケットに分割します。
  • : 学生を4つのグループに分ける場合
NTILE(4) OVER (ORDER BY score)

5. LAG()

  • 説明: 指定したカラムの前の行の値を取得します。
  • : 各月の売上と前月の売上の差分を計算する場合
sales_amount - LAG(sales_amount) OVER (ORDER BY month)

6. LEAD()

  • 説明: 指定したカラムの次の行の値を取得します。
  • : 各月の売上と次月の売上の差分を計算する場合
sales_amount - LEAD(sales_amount) OVER (ORDER BY month)

7. FIRST_VALUE(), LAST_VALUE()

  • 説明: ウィンドウの最初の値や最後の値を取得します。
  • : 各月の売上とその四半期の最初の月の売上を比較する場合
sales_amount - FIRST_VALUE(sales_amount) OVER (ORDER BY month)

これらの関数は、ビジネスやデータ分析の現場で頻繁に使われます。適切な関数を選ぶことで、複雑な集計や解析を効率的に行うことができます。

まとめ:window関数とは?

  • 定義: window関数は、結果セットのサブセット(ウィンドウ)上での計算を行うためのSQL関数。
  • 特性: 通常の集計関数とは異なり、各行の詳細を保持しつつ集計を行うことが可能。
    • PARTITION BY: PARTITION BY句を使用して、結果セットを特定のカテゴリや部分ごとに分けて関数を適用することが可能。
    • ORDER BY: ウィンドウ内の行の順序を定義するために使用される。
    • フレーム範囲指定: ROWSRANGEを用いて、計算の対象となる行の範囲を具体的に定義することが可能。
  • 主な用途: ランキング、移動平均、累積合計など、特定のウィンドウ内での計算を行う場合に使用されます。
/* 利用するテーブル
|------------|------------|--------------|
| date       | product_id | sales_amount |
|------------|------------|--------------|
| 2023-01-01 | A          | 100          |
| 2023-01-02 | A          | 110          |
| 2023-01-03 | A          | 90           |
| 2023-01-01 | B          | 95           |
| 2023-01-02 | B          | 105          |
| 2023-01-03 | B          | 105          |
|------------|------------|--------------|
*/

-- 各製品ごとに日付を基にした売上の3日移動平均を計算するSQLクエリ
SELECT
  date,
  product_id,
  sales_amount,
  AVG(sales_amount) OVER (
    PARTITION BY product_id
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg_3days
FROM
  sales;

/* 結果
|------------|------------|--------------|------------------|
| date       | product_id | sales_amount | moving_avg_3days |
|------------|------------|--------------|------------------|
| 2023-01-01 | A          | 100          | 105              |
| 2023-01-02 | A          | 110          | 100              |
| 2023-01-03 | A          | 90           | 100              |
| 2023-01-01 | B          | 95           | 100              |
| 2023-01-02 | B          | 105          | 101.67           |
| 2023-01-03 | B          | 105          | 105              |
|------------|------------|--------------|------------------|
*/

-- PARTITION BY product_id により、各製品ごとにウィンドウ関数を適用。
-- ORDER BY date で、日付の昇順にデータを整列しています。
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING で、各日の売上の1日前、当日、1日後の3日間のデータを対象として平均を計算。

現在のスキルに今一つ満足できていないシステムエンジニア/プログラマーの方へ。

SQLやデータベースの仕組みを1から学習したい方(学び直したい方)向けに、現役エンジニア達のスキルを結集して 完全無料 のSQL教材を作成しました。是非この機会にブックマークを!

読者料典 【完全無料】SQL:初心者向け学習ロードマップ ←こちらから!

このWebサイトは現役のエンジニアが以下3点を目的として運営しています。

  1. 勉強:一度理解した内容を忘れないように。
    → アウトプットは「最強のインプット」である! 
  2. 備忘:忘れたとしても後から見返せるように。
    → 未来の自分への「お手紙」を書いています。 
  3. 共有:〇〇ってこうだったんだ!の感動をシェアできるように。
    → あなたの知識は誰かにとっての「価値ある情報」です。 

副業ブログの始め方はこちらから

スポンサーリンク
DatabaseIT-Skills
シェアする
ビズドットオンラインをフォローする
タイトルとURLをコピーしました