InterSystems IRISのウィンドウ関数を使用すると、累積合計、ランキング、移動平均など、強力な分析を直接SQLで実行できます。
ウィンドウ関数は、「GROUP BY」のように結果をまとめることなく、関連行の「ウィンドウ」（グループ上）で動作します。
つまり、ループも結合も一時テーブルも使わずに、より簡潔で高速、しかも保守しやすいクエリを書くことができます。

この記事では、よくあるデータ分析タスクに取り組むことで、ウィンドウ関数の仕組みを理解していきましょう。

---

InterSystems IRISでのSQLウィンドウ関数入門

SQLウィンドウ関数は、データ分析のための強力なツールです。
各行をそのまま表示したまま、複数行の集計とランキングを計算することができます。
ダッシュボード、レポート、または複雑な分析を構築しているかどうかに関係なく、ウィンドウ関数はロジックを簡素化し、パフォーマンスを大幅に向上させます。

注意：私はウィンドウ関数の専門家ではありませんが、私がウィンドウ関数を理解するうえで役立ったインサイトやリソースを共有したいと思います。 ご提案や訂正があれば、ぜひお知らせください！

---

🚀 ウィンドウ関数が重要な理由

累積合計やランキング、行間の差分を計算するために、複数のSQLクエリや手続き型のループを書いたことはありませんか？

ウィンドウ関数なら、それらすべてを単一のSQLクエリで実行できます。

追加の結合、一時テーブル、手続き型ループを使うことなく、強力な分析機能をSQLに直接組み込むことができます。

---

🧠 ウィンドウ関数とは？

ウィンドウ関数は、現在の行に関連する複数の行に対して計算を行います。この一連の関連する行をウィンドウと呼びます。

図1に示しているように、「GROUP BY」とは異なり、ウィンドウ関数は行をまとめません。 各行をそのまま表示したまま、集計を計算できます。

図1：集計とウィンドウ関数との違い

一般的な構文は次のようになります。

window_function_name(...) OVER (
  PARTITION BY column_name
  ORDER BY column_name
  ROWS BETWEEN ...
)

構文の説明：

• PARTITION BY行のグループを定義します（「顧客別」や「部門別」など）。
• ORDER BYは各パーティション内の行の順序を定義します。
• ROWS BETWEEN ...行のどのサブセットを関数が参照できるのかを定義します（ウィンドウフレーム）。

---

⚙️ ウィンドウ関数を使用する理由

ウィンドウ関数が登場する前は、多くの場合において開発者は次のことを行う必要がありました。

• 複数のクエリを実行して、 中間結果を取得する。
• 一時テーブルまたはサブクエリを使用して、部分的な集計をマージする
• ObjectScriptで手続き型コードを書いてランキングと累積合計をシミュレーションする。

ウィンドウ関数を使えば、これをスマートに解決できます。1つのクエリで、ループも不要、追加の状態管理もありません。

---

🧩 例1：顧客ごとの累積合計

まずは簡単な例から始めましょう。各顧客の注文の累積合計を時間の経過に沿って計算します。

🛠️ 例のためのテーブル作成とデータ挿入を行う

CREATE TABLE Orders (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE,
  OrderAmount DECIMAL(10,2)
)

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount)
SELECT 1, 101, '2023-01-01', 100.00 UNION
SELECT 2, 101, '2023-01-05', 150.00 UNION
SELECT 3, 102, '2023-01-02', 200.00 UNION
SELECT 4, 101, '2023-01-10', 50.00 UNION
SELECT 5, 102, '2023-01-07', 100.00 

❌ ウィンドウ関数なし（複数のクエリ）

SELECT
  o1.CustomerID,
  o1.OrderDate,
  SUM(o2.OrderAmount) AS RunningTotal
FROM Orders o1
JOIN Orders o2
  ON o1.CustomerID = o2.CustomerID
  AND o2.OrderDate <= o1.OrderDate
GROUP BY o1.CustomerID, o1.OrderDate
ORDER BY o1.CustomerID, o1.OrderDate

結果：

CustomerID	OrderDate	RunningTotal
101	2023-01-01	100
101	2023-01-05	250
101	2023-01-10	300
102	2023-01-02	200
102	2023-01-07	300

---

これは動作しますが、自己結合やGROUP BYが必要になり、大規模データセットではコストが高くなります。
ウィンドウ関数を使えば、はるかにすっきりして読みやすいSQLクエリを書くことができます。

✅ ウィンドウ関数あり（1つのクエリ）

SELECT
  CustomerID,
  OrderDate,
  SUM(OrderAmount) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
  ) AS RunningTotal
FROM Orders
ORDER BY CustomerID, OrderDate

結果：

CustomerID	OrderDate	RunningTotal
101	2023-01-01	100
101	2023-01-05	250
101	2023-01-10	300
102	2023-01-02	200
102	2023-01-07	300

---

ウィンドウ関数構文内の各ステートメントを詳しく見てみましょう。

• PARTITION BY CustomerID
  各顧客ごとに累積合計が別々に計算されるようにします。 これがないと、合計がすべての顧客にまたがって計算されてしまいます。

• ORDER BY OrderDate
  各顧客の注文の順序を定義し、累積合計が時間順に計算されるようにします。

• SUM(OrderAmount) OVER (...)
  これは、各パーティションに対して適用されるウィンドウ関数です。 この場合、各行のOrderAmountの合計を計算し、同じパーティション（顧客）内のそれまでのすべての行を含めて累積します。

図2 - 例1に対するウィンドウ関数の評価

💡 例2 — 給与での従業員ランク付け

🛠️ 例のためのテーブル作成とデータ挿入を行う

CREATE TABLE Employees (
  EmployeeID INT,
  Department VARCHAR(50),
  Name VARCHAR(100),
  Salary DECIMAL(10,2)
)

INSERT INTO Employees (EmployeeID, Department, Name, Salary)
SELECT 1, 'Sales', 'Alice', 70000 UNION
SELECT 2, 'Sales', 'Bob', 65000 UNION
SELECT 3, 'HR', 'Carol', 60000 UNION
SELECT 4, 'HR', 'Dave', 62000 UNION
SELECT 5, 'Sales', 'Eve', 72000

❌ ウィンドウ関数なし（動的SQLとObjectScriptループ）

ClassMethod RankEmployeesBySalary()
{
    Set tSQL = "SELECT Department, EmployeeID, Salary " _
              "FROM Employees ORDER BY Department, Salary DESC"
    Set tRS = ##class(%SQL.Statement).%ExecDirect(, tSQL)

    Set prevDept = ""
    Set rank = 0

    While tRS.%Next() {
        Set dept = tRS.%Get("Department")
        Set emp = tRS.%Get("EmployeeID")
        Set sal = tRS.%Get("Salary")

        If dept '= prevDept {
            Set rank = 1
        } Else {
            Set rank = rank + 1
        }

        Write "Dept: ", dept, " | Emp: ", emp, " | Rank: ", rank, " | Salary: ", sal, !
        Set prevDept = dept
    }
}

結果：

USER>Do ##class(tmp.Teste1).RankEmployeesBySalary()
Dept: HR | Emp: 4 | Rank: 1 | Salary: 62000
Dept: HR | Emp: 3 | Rank: 2 | Salary: 60000
Dept: Sales | Emp: 5 | Rank: 1 | Salary: 72000
Dept: Sales | Emp: 1 | Rank: 2 | Salary: 70000
Dept: Sales | Emp: 2 | Rank: 3 | Salary: 65000

---

✅ ウィンドウ関数あり（1つの宣言型SQL）

SELECT
  Department,
  EmployeeID,
  Salary,
  RANK() OVER (
    PARTITION BY Department
    ORDER BY Salary DESC
  ) AS SalaryRank
FROM Employees
ORDER BY Department, SalaryRank

結果：

Department	EmployeeID	Salary	SalaryRank
HR	4	62000	1
HR	3	60000	2
Sales	5	72000	1
Sales	1	70000	2
Sales	2	65000	3

---

ウィンドウ関数構文内の各ステートメントを詳しく見てみましょう。

• PARTITION BY Department
  各部門ごとにランキングが個別に計算されるようにします。 この句なしでは、従業員は部門の境界を無視して会社全体でランキングされることになります。

• ORDER BY Salary DESC
  各部門内の従業員を、給与の高い順に並べ替えます。 これによりランキングの順序が決まり、高い給与ほど順位番号が小さくなります。

• RANK() OVER (...)
  各部門の並べ替えられたリストに対してランキング関数を適用します。 給与に基づいて各従業員に順位を割り当て、同点の場合は同じ順位が与えられ、順位の間にギャップが生じます。

図3 - 例2に対するウィンドウ関数の評価

---

🧩 例3 — 1日ごとの売上の移動平均

ROWS BETWEEN が移動平均でどのように機能するかを例で示しましょう

🛠️ 例のためのテーブル作成とデータ挿入を行う

CREATE TABLE DailySales (
  SaleDate DATE,
  Amount DECIMAL(10,2)
)

INSERT INTO DailySales (SaleDate, Amount)
SELECT '2023-01-01', 100 UNION 
SELECT '2023-01-02', 150 UNION
SELECT '2023-01-03', 200 UNION
SELECT '2023-01-04', 250 UNION
SELECT '2023-01-05', 300

❌ ウィンドウ関数なし（複数のクエリとObjectScriptループ）

ClassMethod MovingAverageWithoutWindow()
{
    // Query all sales ordered by date
    Set sql = "SELECT SaleDate, Amount FROM DailySales ORDER BY SaleDate"
    Set stmt = ##class(%SQL.Statement).%New()
    Set status = stmt.%Prepare(sql)
    If $$$ISERR(status) {
        Write "SQL Prepare failed: ", status, !
        Quit
    }

    Set rset = stmt.%Execute()

    // Store rows in memory for lookback
    Set rowCount = 0
    While rset.%Next() {
        Set rowCount = rowCount + 1
        Set sales(rowCount, "Date") = rset.%Get("SaleDate")
        Set sales(rowCount, "Amount") = rset.%Get("Amount")
    }

    // Loop through and calculate 3-day moving average
    For i = 1:1:rowCount {
        Set total = 0
        Set count = 0

        For j = i-2:1:i {
            If j >= 1 {
                Set total = total + sales(j, "Amount")
                Set count = count + 1
            }
        }

        Set movingAvg = total / count
        Write "Date: ", sales(i, "Date"), " | Amount: ", sales(i, "Amount"), " | MovingAvg: ", $FN(movingAvg, "", 2), !
    }
}

結果：

USER>Do ##class(tmp.Teste1).MovingAverageWithoutWindow()
Date: 66475 | Amount: 100 | MovingAvg: 100.00
Date: 66476 | Amount: 150 | MovingAvg: 125.00
Date: 66477 | Amount: 200 | MovingAvg: 150.00
Date: 66478 | Amount: 250 | MovingAvg: 200.00
Date: 66479 | Amount: 300 | MovingAvg: 250.00

✅ ウィンドウ関数あり（1つの宣言型SQL）

SELECT
  SaleDate,
  Amount,
  AVG(Amount) OVER (
    ORDER BY SaleDate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS MovingAvg
FROM DailySales
ORDER BY SaleDate

これは当日と前の2日間の平均、3日間のローリング平均を計算します。

結果：

SaleDate	Amount	MovingAvg
2023-01-01	100	100
2023-01-02	150	125
2023-01-03	200	150
2023-01-04	250	200
2023-01-05	300	250

---

ウィンドウ関数構文内の各ステートメントを詳しく見てみましょう。

• ORDER BY SaleDate
  売上の時系列順序を定義します。これは、時間ベースの移動平均を計算するために不可欠です。

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  現在の行とその前の2つの行でウィンドウフレームを指定します。 これにより、平均計算のための3日間のローリングウィンドウが作成されます。

• AVG(Amount) OVER (...)
  定義したフレーム全体での Amount の平均を計算するウィンドウ関数を適用します。 各行に対して、計算には当日と前の2日間が含まれます。

図4 - 例3に対するウィンドウ関数の評価

この例には PARTITION BY がないことに注意してください。 これは、カテゴリ、地域、顧客ごとではなく、すべての1日ごとの売上に対して移動平均を計算することが目標だからです。

PARTITION BY を使用するとデータが個別のグループに分割され、各パーティションごとに移動平均がリセットされてしまいます。 時間に沿った連続的なローリング平均を求めるため、データセットを単一の時系列として扱い、ウィンドウを途切れさせないようにするため PARTITION BY は省略します。 もちろん、クエリの要件に応じて使用することができます。

---

🏁 重要なポイント

ウィンドウ関数を使えば、以前は複数のクエリや手続き型コードが必要だった処理を、1つの洗練されたSQLステートメントで表現できます。

そうすることで、クエリがより読みやすくなり、コードの保守性が向上し、分析も高速化されます。すべてSQL内で完結します。

---

🧭 まとめ

InterSystems IRISのウィンドウ関数は、複雑な分析をSQL内で直接実行するための強力かつ洗練された方法を提供します。 累積合計の計算、順位付け、移動平均の計算など、これらの関数を使えばロジックが簡素化され、パフォーマンスも向上します。しかもクエリは読みやすく、保守しやすいままです。

ウィンドウ関数を活用することで、手続き型コードや複雑な結合に頼ることなく、データからより深いインサイトを得ることができます。 便利なだけではありません。ウィンドウ関数は、SQL分析における大きな飛躍です。

詳細やその他のウィンドウ関数については、IRISドキュメントのウィンドウ関数リファレンスをご覧ください。

---

この記事は、概念を明確にし、読みやすさを向上させるために、AIツールの助けを借りて作成されました。