Ccmmutty logo
Commutty IT
・0 pv・4 min read

SQL Serverのウィンドウ関数の基本と使用例

SQLServer
https://cdn.magicode.io/media/notebox/blob_nSGcCo0

超概要

SQL Serverのウィンドウ関数は、特定のデータセット内で集計やランキングを計算し、結果を行ごとに出力する機能。
ウィンドウ関数の基本的な使い方と、簡単な検証を行えるSQLを記載。

ウィンドウ関数とは？

ウィンドウ関数は、OVER 句を使用して、行ごとに計算結果を返す関数です。通常の集計関数（SUM、AVG など）とは異なり、結果をグループ化せずに行ごとに保持することができます。 まずないと思うが、サブクエリやビューで使用されている場合やそういったクエリをリファクタリングするときは ソートや集計でインデックスが機能しなくなってしまったり遅くなったりするうえ実行計画上見つけにくいので パフォーマンスが求められる時に使用する際はちゃんと実行計画を確認し集計タイミングをミスんないこと。

代表的なウィンドウ関数

他にもいくつかあるが 思い出すときにイメージしやすいよう簡単なやつでまとめ。

ランキング関数

RANK()
同じ値がある場合、同順位となり、次の順位はスキップされます。
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
下記テストデータを使った時に上記クエリ結果
DENSE_RANK()
RANK()と似ていますが、同順位の後の順位がスキップされません。
SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;
下記テストデータを使った時に上記クエリ結果
ROW_NUMBER()
順位を一意に振りたい場合に使用します。
SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
下記テストデータを使った時に上記クエリ結果

集計関数とウィンドウ関数の組み合わせ

ウィンドウ関数は、集計関数と組み合わせることで、グループ化せずに集計結果を行ごとに保持できます。
SUM() OVER()
SELECT Name, Department, Salary,
       SUM(Salary) OVER (PARTITION BY Department) AS TotalSalary
FROM Employees;
下記テストデータを使った時に上記クエリ結果
このクエリは、部門ごとの給与合計を計算し、各行に追加する。
AVG() OVER()
SELECT Name, Department, Salary,
       AVG(Salary) OVER (PARTITION BY Department) AS AvgSalary
FROM Employees;
下記テストデータを使った時に上記クエリ結果

サンプルテーブルの作成

ウィンドウ関数を使用するためのサンプルテーブルを作成します。

Employees テーブルの作成

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Department NVARCHAR(50),
    Salary INT
);

Departments テーブルの作成

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50)
);

サンプルデータの挿入

INSERT INTO Employees VALUES
(1, 'Alice', 'Sales', 5000),
(2, 'Bob', 'Sales', 7000),
(3, 'Charlie', 'HR', 6000),
(4, 'David', 'HR', 6000),
(5, 'Eve', 'IT', 8000);

INSERT INTO Departments VALUES
(1, 'Sales'),
(2, 'HR'),
(3, 'IT');

検証手順

1. テストデータの作成

上記のテーブルとデータを作成します。

2. クエリの実行

上記の各SQLを実行し、結果を確認します。

3. 実行計画の確認

USE Test_DB; -- 自分のDBの名前に変換
GO

-- 統計情報の取得
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- 実行計画の確認 (クエリは実行されない)
SET SHOWPLAN_ALL ON;
GO
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;
GO
SET SHOWPLAN_ALL OFF;
GO
SQL Server の実行計画を確認できます。 ⇒クエリ実行時にSSMSからオプションつけたら実行計画はGUIで見れる。

Discussion

コメントにはログインが必要です。