DEV Community

InterSystems profile image InterSystems Developer
InterSystems Developer for InterSystems

Posted on • Originally published at community.intersystems.com

Understanding SQL Window Functions (Part 1)

#sql #webdev #analytics

Window functions in InterSystems IRIS let you perform powerful analytics — like running totals, rankings, and moving averages — directly in SQL.

They operate over a "window" of rows related to the current row, without collapsing results like GROUP BY.

This means you can write cleaner, faster, and more maintainable queries — no loops, no joins, no temp tables.

In this article let's understand the mechanics of window functions by addressing some common data analisys tasks.

Introduction to SQL Window Functions in InterSystems IRIS

SQL window functions are a powerful tool for data analysis.

They allow you to compute aggregates and rankings across rows while preserving individual row visibility.

Whether you're building dashboards, reports, or complex analytics, window functions simplify your logic and boost performance.

Note: I'm not an expert in window functions, but I’d like to share the insights and resources that helped me understand them. Suggestions or corrections are very welcome!

🚀 Why Window Functions Matter

Have you ever written multiple SQL queries, or even procedural loops, just to calculate running totals, ranks, or differences between rows?

Window functions let you do all that in a single SQL query.

They bring powerful analytics directly into SQL — no extra joins, no temporary tables, and no procedural loops.

🧠 What Are Window Functions?

A window function performs a calculation across a set of rows that are somehow related to the current row — this set of rows is called a window.

As depicted in Figure 1, unlike GROUP BY, window functions don’t collapse rows. They allow you to compute aggregates while still keeping each row visible.


Figure 1 - Differences between Aggregations and Window Functions

The general syntax looks like this:

window_function_name(...) OVER (
  PARTITION BY column_name
  ORDER BY column_name
  ROWS BETWEEN ...
)
Enter fullscreen mode Exit fullscreen mode

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).

⚙️ Why Use Window Functions?

Before window functions, developers often had to:

  • Run multiple queries to get intermediate results.
  • Use temporary tables or subqueries to merge partial aggregates.
  • Write procedural code in ObjectScript to simulate ranking or running totals.

Window functions solve this neatly — one query, no loops, no extra state to manage.

🧩 Example 1 — Running Total Per Customer

Let’s start with a simple example: compute the running total of each customer’s orders over time.

🛠️ Create and Populate Table for the example 

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
Enter fullscreen mode Exit fullscreen mode

❌ Without window functions — multiple queries 

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
Enter fullscreen mode Exit fullscreen mode

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

This works, but it needs a self-join and a GROUP BY, and becomes expensive for large datasets.
Window functions allow to write a much more clean SQL query.

✅ With window functions — one query 

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

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

Let's break down each statement in the window function syntax:

  • PARTITION BY CustomerID
    Ensures that the running total is calculated separately for each customer. Without this, the sum would span across all customers.

  • ORDER BY OrderDate
    Defines the sequence of orders for each customer, so the running total accumulates in chronological order.

  • SUM(OrderAmount) OVER (...)
    This is the window function applied over the partitions. In this case, it computes the sum of OrderAmount for each row, accumulating them, including all previous rows in the same partition (customer) up to that point.


Figure 2 - Window function evaluation for example 1

💡 Example 2 — Ranking Employees by Salary

🛠️ Create and Populate Table for the example 

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
Enter fullscreen mode Exit fullscreen mode

❌ Without window functions — Dynamic SQL and ObjectScript loops 

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
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

✅ With window functions — one declarative SQL 

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

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

Let's break down each statement in the window function syntax:

  • PARTITION BY Department

    Ensures that ranking is calculated separately within each department. Without this clause, employees would be ranked across the entire company, ignoring departmental boundaries.

  • ORDER BY Salary DESC

    Sorts employees within each department from highest to lowest salary. This determines the ranking order — higher salaries get lower rank numbers.

  • RANK() OVER (...)

    Applies the ranking function over each department's sorted list. It assigns a rank to each employee based on their salary, with ties receiving the same rank and gaps appearing in the sequence.


Figure 3 - Window function evaluation for example 2

🧩 Example 3 — Moving Average of Daily Sales

Let’s illustrate how ROWS BETWEEN works with a moving average.

🛠️ Create and Populate Table for the example 

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
Enter fullscreen mode Exit fullscreen mode

❌ Without window functions — multiple queries and ObjectScript loops 

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), !
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

✅ With window functions — one declarative SQL 

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

This computes the average of the current day and the two previous days — a rolling 3-day average.

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

Let's break down each statement in the window function syntax:

  • ORDER BY SaleDate

    Defines the chronological order of sales, which is essential for calculating a time-based moving average.

  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

    Specifies the window frame: the current row and the two rows before it. This creates a rolling 3-day window for the average calculation.

  • AVG(Amount) OVER (...)

    Applies the window function to compute the average of Amount across the defined frame. For each row, it includes the current day and the two previous days in the calculation.


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.

🏁 Key Takeaway

Window functions let you express what used to require multiple queries or procedural code in a single, elegant SQL statement.

They make your queries more readable, your code more maintainable, and your analytics faster — all without leaving SQL.

🧭 Final Thoughts

Window functions in InterSystems IRIS offer a powerful and elegant way to perform complex analytics directly in SQL. Whether you're calculating running totals, assigning ranks, or computing moving averages, these functions simplify your logic and improve performance — all while keeping your queries readable and maintainable.

By embracing window functions, you unlock a deeper level of insight from your data without resorting to procedural code or convoluted joins. They’re not just a convenience — they’re a leap forward in how we think about SQL analytics.

You can checkout more details and more window function in the Window Functions reference in IRIS documentation.

This article was written with the help of AI tools to clarify concepts and improve readability.

Top comments (0)