【データベース】「SQLの実践」簡単速習‼【⑨ウィンドウ関数/MySQL】

SQL

こんにちはヤク学長です。
データサイエンティスト兼ファーマシストで、アルゴリズムやBI開発を行っています。

本記事の目的は、「SQLの基本操作を知る」ことを目的としています。

【本記事のもくじ】

まず、「SQL」に真剣に取り組むための概要を解説します。
下記の方法で、簡単に概要を抑えることができます。

  • １２．ウィンドウ関数

それでは、上から順番に見ていきます。
なお、本上記の方法を順番に抑えれば成果が出ます。

記事の内容は「転載 & 引用OK」問題ありません。

目次

１２. ウィンドウ関数

ウィンドウ関数について

ウィンドウ関数は、データベースのテーブルに対して集約関数を適用する際に、行ごとに異なる集計結果を計算するために使用される機能です。つまり、ウィンドウ関数は、単一のSQLクエリーでグループ化、集計、並べ替え、およびフィルタリングを行うことができます。

ウィンドウ関数は、以下のような形式で使用されます。

<関数> OVER (
[PARTITION BY <パーティションキー>]
[ORDER BY <ソートキー> [ASC | DESC] [, <ソートキー> [ASC | DESC] ... ]]
)

この構文では、ウィンドウ関数を適用する対象の関数を指定します。その後、OVER句を使用して、関数が適用される行の範囲を指定します。PARTITION BY句を使用すると、関数が適用される行をグループ化するためのカラムを指定することができます。ORDER BY句を使用すると、グループ化された行をソートするためのカラムを指定することができます。

ウィンドウ関数によく使用される関数には、以下のものがあります。

  • ROW_NUMBER: 行番号を返します。
  • RANK: 行のランクを返します。
  • DENSE_RANK: 行の密度ランクを返します。
  • SUM: 指定された列の合計値を返します。
  • AVG: 指定された列の平均値を返します。
  • MIN: 指定された列の最小値を返します。
  • MAX: 指定された列の最大値を返します。

例えば、以下のクエリーは、employeesテーブルのsalaryカラムの平均値を、部署ごとに計算し、salaryカラムが平均値よりも高い社員の数を計算します。

SELECT department, COUNT(*)
OVER (
PARTITION BY department
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rank
FROM employees
WHERE salary > AVG(salary)
OVER (
PARTITION BY department
)
ORDER BY department, salary DESC;

このクエリーでは、PARTITION BY句を使用してdepartmentカラムをグループ化し、ORDER BY句を使用してsalaryカラムを降順にソートします。ROWS BETWEEN句を使用して、現在の行から上限なしの範囲にある行について計算するように指定しています。

ウィンドウ関数は、データの集計や分析に非常に便利です。特に、部分的な合計やランク、密度ランクなどの情報を取得することができます。また、通常の集約関数では取得できないような、グループ化した範囲内での特定の行の値を取得することもできます。

例えば、以下のクエリーは、salesテーブルから、売上額の高い順にランク付けされた情報を取得する例です。

SELECT
customer_name,
sale_date,
sale_amount,
RANK() OVER (
ORDER BY sale_amount DESC
) AS sales_rank
FROM
sales

このクエリーでは、RANK()関数を使用して、sale_amountカラムの値に基づいてランクを計算し、sales_rankカラムに結果を格納しています。また、ORDER BY句を使用して、sale_amountカラムを降順でソートしています。

ウィンドウ関数は、分析や報告のための高度なクエリーを作成するために不可欠な機能です。しかし、複雑なクエリーを作成する場合は、パフォーマンスの問題やデータの不整合のリスクを避けるために、十分なテストとチューニングが必要です。

フレームの範囲を変更する

ウィンドウ関数におけるフレームの範囲は、ROWS BETWEEN句やRANGE BETWEEN句を使用して指定することができます。

ROWS BETWEEN句では、フレームの範囲を行単位で指定することができます。以下は、フレームの開始行と終了行を明示的に指定する例です。

SELECT
customer_id,
sale_date,
sale_amount,
AVG(sale_amount) OVER (
PARTITION BY customer_id
ORDER BY sale_date
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS moving_average
FROM
sales

このクエリーでは、ROWS BETWEEN句で、集計対象となる行の範囲を「3 PRECEDING」から「CURRENT ROW」に指定しています。つまり、現在の行を含め、直近の3つの行の平均値を計算しています。

RANGE BETWEEN句では、フレームの範囲を値単位で指定することができます。以下は、フレームの範囲を値の大小で指定する例です。

SELECT
customer_id,
sale_date,
sale_amount,
SUM(sale_amount) OVER (
PARTITION BY customer_id
ORDER BY sale_date
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
) AS running_total
FROM
sales

このクエリーでは、RANGE BETWEEN句で、集計対象となる値の範囲を「7日前から現在の行まで」としています。つまり、現在の行と直近の7日間の売上合計を計算しています。

フレームの範囲を適切に指定することで、ウィンドウ関数を使った高度な分析や集計が可能になります。

ウィンドウ関数一覧

以下は代表的なウィンドウ関数の一覧です。

  • ROW_NUMBER(): 行番号を付与する関数。
  • RANK(): 同じ値を持つ行を同じ順位としてランク付けする関数。
  • DENSE_RANK(): 同じ値を持つ行を同じ順位としてランク付けする関数。RANK()と違い、同じ順位が複数ある場合でも、順位を飛ばさずに振り分けます。
  • NTILE(): 行を等分割する関数。引数に割り分けるグループ数を指定します。
  • LAG(): 前の行の値を参照する関数。引数にオフセットを指定することで、複数の行前の値を参照することもできます。
  • LEAD(): 次の行の値を参照する関数。LAG()と同様に、引数にオフセットを指定することで、複数の行後の値を参照することもできます。
  • FIRST_VALUE(): グループの最初の行の値を取得する関数。
  • LAST_VALUE(): グループの最後の行の値を取得する関数。
  • SUM(): 合計を計算する関数。
  • AVG(): 平均を計算する関数。
  • MAX(): 最大値を取得する関数。
  • MIN(): 最小値を取得する関数。

これらのウィンドウ関数を組み合わせることで、複雑なデータ分析を行うことができます。

「FIRST_VALUE」と「LAST_VALUE」

例1: FIRST_VALUE

以下のテーブル「sales」において、各店舗の初回購入日を取得する例を示します。

| id | store_id | customer_id | purchase_date |
|----|----------|-------------|---------------|
| 1 | 1 | 100 | 2022-01-01 |
| 2 | 2 | 200 | 2022-01-02 |
| 3 | 3 | 300 | 2022-01-03 |
| 4 | 1 | 400 | 2022-01-04 |
| 5 | 2 | 500 | 2022-01-05 |

以下に、MySQLでの「FIRST_VALUE」と「LAST_VALUE」の具体例と解説を示します。

例1: FIRST_VALUE

以下のテーブル「sales」において、各店舗の初回購入日を取得する例を示します。

SELECT DISTINCT store_id, FIRST_VALUE(purchase_date) OVER (PARTITION BY store_id ORDER BY purchase_date) AS first_purchase_date
FROM sales;

上記クエリにおいて、以下の点に注目します。

  • PARTITION BY句で「store_id」を指定し、店舗ごとに処理をグループ化します。
  • ORDER BY句で「purchase_date」を指定し、購入日の昇順で並べ替えます。
  • ウィンドウ関数「FIRST_VALUE」を使用して、グループ内の最初の行の値を取得します。

結果は以下の通りです。

| store_id | first_purchase_date |
|----------|-----------------------|
| 1 | 2022-01-01 |
| 2 | 2022-01-02 |
| 3 | 2022-01-03 |

この結果から、各店舗の初回購入日が取得できていることが分かります。

例2: LAST_VALUE

以下のテーブル「employees」において、各部署の最新の入社日を取得する例を示します。

| id | name | department | hire_date |
|----|------|------------|------------|
| 1 | John | Sales | 2021-01-01 |
| 2 | Jane | Marketing | 2021-01-02 |
| 3 | Tom | Sales | 2022-01-01 |
| 4 | Mike | Marketing | 2022-01-02 |

以下のクエリを使用して、各部署の最新の入社日を取得します。

SELECT DISTINCT department, LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date) AS latest_hire_date
FROM employees;

以下のクエリにおいて、以下の点に注目します。

  • PARTITION BY句で「department」を指定し、部署ごとに処理をグループ化します。
  • ORDER BY句で「hire_date」を指定し、入社日の昇順で並べ替えます。

訂正後のクエリは以下の通りです。

SELECT DISTINCT department, LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date) AS latest_hire_date
FROM employees;

というわけで、今回は以上です。大変大変お疲れ様でした。
引き続きで、徐々に発信していきます。

コメントや感想を受け付けています。ちょっとした感想でもいいので嬉しいです。

それでは、以上です。

最新情報をチェックしよう！

SQLの最新記事4件