Window Functions in SQL: What They Are and How to Use Them

-

Introduction

Window functions in SQL let you do calculations across rows related to the current row, without combining them into one. Unlike regular aggregate functions, window functions keep each row in the result; they just look at other rows while calculating.

For example, you can calculate:

  • the rank of each salesperson within their region,
  • a running total of sales for each customer,
  • or the difference between current and previous month’s revenue.
SELECT
  [Customer No_],
  [Location Code],
  [Remaining Amount],
  SUM([Remaining Amount]) OVER (
    PARTITION BY [Location Code]
    ORDER BY [Customer No_]
  ) AS LocationOutstandingTotal
FROM [Customer Ledger Entry];
  • We’re calculating the sum of [Remaining Amount]
  • OVER (...) turns it into a window function
  • PARTITION BY [Location Code] groups the rows by location
  • ORDER BY [Customer No_] gives a running total within each location

Note: You can’t use window functions directly in a GROUP BY clause or mix them with group-based aggregations.

References

https://www.postgresql.org/docs/9.1/tutorial-window.html
Mode - Window Functions

Usage/Examples

The SUM() window function adds up values over a defined set of rows related to the current row.
The AVG() window function gives the average value over the window of rows.
The COUNT() window function counts how many rows are in the window frame for each row.

By adding "ORDER BY" inside the window function tells SQL to process rows in a specific order (by start_time) within each start_terminal group.
This converts it into a running total/average/count.

ROW_NUMBER() - Assigns a unique row number to each row, starting from 1 in each partition.
RANK() - Ranks rows within a partition, but skips ranks if there are ties (e.g. 1, 2, 2, 4).
DENSE_RANK() - It is similar to RANK(), but does not skip numbers (1, 2, 2, 3).

The below screenshot with row number 12 highlights the difference between rank and denserank.

LAG() - Returns the value of a column from a previous row in the partition.
LEAD() - Returns the value of a column from a next row in the partition.
FIRST_VALUE() - Returns the first or last value within the window frame.
LAST_VALUE() - Returns the last value within the window frame.

Note that I did not add an "Order By" on the Last_Value function as that would've ended up showing me the current value (as it works like a running total with Order By) and skipping "Order By" gave me the absolute last value within the frame.

Using a WINDOW Alias

If you are using the same window (PARTITION BY / ORDER BY) in several window functions, you don’t need to repeat it each time.
You can define it once using a WINDOW clause and then use an alias.

This makes the query neat and tidy while also reducing redundancy.

Note: The WINDOW clause (if used) should come after the WHERE clause in your query.

Conclusion

Window functions help you do more with your SQL queries. You can calculate running totals, ranks, averages, and compare rows without losing the details of each row.

They can save you from writing long and complicated queries. Once you get used to using simple window functions like SUM() or ROW_NUMBER(), you can explore more like LAG() or RANK().

Try using them in your reports, you’ll find that your queries become easier to write and understand.

Comments

Post a Comment

Popular posts from this blog

Event-Driven Architecture: How to Create Custom Business Events in Business Central

-
Image
Introduction Events are notifications that signal something has happened in the system. A record getting created, updated or deleted, a sales order getting posted, etc are all examples of events. An event driven architecture provides asynchrony and decoupled communication between different components of the system supporting flexibility, scalability and modularity. In Business Central, we have two main types of events - Business and Integration. Integration Events are related to things within Business Central. They are use for extending the functionality of the Base Application without modifying it directly. Business Events are related to things outside of Business Central. They are used for notifying external systems (e.g., via Power Automate or Logic Apps) regarding any changes that happen within Business Central. Microsoft allows us to create custom Integration and Business Events based on our requirements. In this blog, we'll see how to create a custom Business Event and...
Read more

Leverage Postman for Streamlined API Testing in Finance and Operations

-
Image
Introduction Postman is a tool for testing APIs. It lets you send requests, automate tests, and share your work with your team.  We're going to connect Postman to our Finance and Operations environment so we can test out standard or custom APIs. I've already covered automating the testing in Postman in my blog here  so once the connections are in place you'll be good to go! Pre-requisites Postman Finance and Operations Environment Configuration We'll start with creating an App Registration in Azure Portal. Go to the Azure Portal (of the same tenant of your FnO Environment). Search for "App Registration" and click on "New Registration". Add a name for your new app and click on "Register." Once it is completed, you'll be taken to the Overview of the app. Here, click on the "Add a certificate or secret" under the "Client Credentials." Add an appropriate name and select the expiration date of the certificate as necessary...
Read more

Integration with Finance and Operations - From Basics (Part 2)

-
Image
Introduction Finance and Operations provides two major ways to interact with tables (or data entities) for external system using APIs; namely Custom Services and Data Entities. Data entities in D365 Finance and Operations simplify data management by grouping data from multiple tables. They make it easier to import, export, and integrate data with other systems. Custom services in D365 Finance and Operations allow developers to create web services for specific business needs. They enable external systems to interact with D365 F&O by exposing custom logic and operations. This helps in integrating and automating processes with other applications. In the previous blog, we saw how we can use Data Entities to create APIs. In this blog, we'll see how we can use Custom Services to create APIs. References Custom Service Development Exposing an X++ Class as a Data Contract Using Data Contracts Pre-requisites A Finance and Operations Environment Postman connected to FnO Enviroment (If you...
Read more