InterSystems IRIS中的窗口函数（Window Functions）可让您直接在SQL中执行强大的分析操作，例如累计总和、排名和移动平均值等。
这些函数针对与当前行相关的一组行（即“窗口”）进行操作，且不会像 GROUP BY那样合并结果。
这意味着您可以编写更简洁、更快速且更易于维护的查询——无需循环、无需连接、无需临时表。
在本文中，我们将通过处理一些常见的数据分析任务来了解窗口函数的作用机制。

---

InterSystems IRIS中的SQL窗口函数入门

SQL窗口函数（SQL window functions）是数据分析的强大工具。
它们允许你在保留各行列可见性的同时，跨行计算聚合值和排名。
无论你是在构建仪表盘、报表还是进行复杂分析，窗口函数都能简化你的逻辑并提升性能。

注：我并非窗口函数领域的专家，但我愿意分享助我理解窗口函数的心得体会和相关资源。非常欢迎大家提出建议或进行指正！

---

🚀 为什么窗口函数这么重要

你是否曾为了计算累计总和、排名或行间差值，而编写过多条SQL查询语句，甚至使用过程化循环？

窗口函数能让你仅通过一条SQL查询语句就实现所有这些操作。

它们将强大的分析功能直接融入SQL——无需额外连接操作、无需临时表，也无需过程化循环。

---

🧠 什么是窗口函数

窗口函数会针对一组与当前行存在某种关联的行进行计算——这组行被称为窗口(window)。

如图1所示，与GROUP BY不同，窗口函数不会合并行。它们允许你在计算聚合值的同时仍保留每行的可见性。

图1 - 聚合函数与窗口函数的区别

通用语法如下所示：

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

Where:

• PARTITION BY defines groups of rows (like "per customer" or "per department").
• ORDER BY defines the order of rows within each partition.
• ROWS BETWEEN ... defines which subset of rows are visible to the function (the window frame).

---

⚙️ 为什么要使用窗口函数?

在窗口函数出现之前，开发人员常常不得不：

• 运行多条查询以获取中间结果。
• 使用临时表或子查询来合并部分聚合数据。
• 在ObjectScript中编写过程化代码来模拟排名或累计总和的计算。

窗口函数巧妙地解决了这些问题——只需一条查询，无需循环，也无需管理额外状态。

---

🧩 示例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

Result:

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

---

这种方法虽然可行，但需要用到自连接（self-join）和GROUP BY，对于大型数据集而言计算成本会很高。
而窗口函数则能让我们编写出更为简洁的SQL查询语句。

✅ 使用窗口函数——仅需一条查询语句

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

Result:

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 (...)
  这是应用于分区的窗口函数。在此例中，它计算每行订单金额的总和，并累加这些值，包括同一分区（客户）中截至该行之前的所有行。

图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
    }
}

Result:

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

---

✅ 使用窗口函数——一条声明式SQL语句即可实现

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

Result:

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 — 日销售额的移动平均值

让我们通过一个移动平均值的例子来说明 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), !
    }
}

Result:

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

✅ 使用窗口函数——一条声明式SQL即可实现

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

这会计算当前日期及前两天的平均值，即一个滚动三日平均值。

Result:

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
  指定窗口范围：当前行及其之前的两行。这为平均值计算创建了一个滚动三日窗口。

• AVG(Amount) OVER (...)
  应用窗口函数，在所定义的范围内计算Amount的平均值。对于每一行，计算时都会包含当前日期及前两天的数据。

Figure 4 - Window function evaluation for example 3

Note that there's no PARTITION BY in this example. This is because the goal is to calculate the moving average across all daily sales, not separately by category, region, or customer.

Using PARTITION BY would split the data into independent groups, causing the moving average to reset within each partition. Since we want a continuous rolling average over time — treating the dataset as a single chronological sequence — we omit PARTITION BY to keep the window unbroken. But off course you can use it if it makes sense for your query requirements.

---

🏁 关键要点

窗口函数使你能够用一条简洁优雅的SQL语句，实现以往需要多个查询或过程化代码才能完成的操作。

它们让你的查询更易读、代码更易维护，且分析速度更快——所有这些都无需脱离SQL环境。

---

🧭最终思考

InterSystems IRIS中的窗口函数提供了一种强大且优雅的方式，可直接在SQL中执行复杂的数据分析。无论你是计算累计总和、分配排名，还是计算移动平均值，这些函数都能简化你的逻辑并提升性能——同时让你的查询保持可读性和可维护性。

窗口函数可以支持你从数据中挖掘出更深层次的洞察，而无需借助过程化代码或复杂的连接操作。这不仅仅是一种便利工具——更是我们在SQL分析思维方式上的一次飞跃。

你可以在IRIS文档的 Window Functions reference 中查看更多细节和更多窗口函数。

---

本文借助人工智能工具撰写，旨在阐明概念并提升可读性。