How much do you really want to know?
That’s the real question when you’re deciding on a GROUP BY vs a window function in SQL. A GROUP by clause (explained here) is fantastic for a quick summary of your information. Totals, averages, and so on.
Window functions, on the other hand, will give you all that information, plus the underlying details. In the example below, you’ll see you’ve got not only the total for each order, but the individual components that make up each order.
A window function sample template:
Generates this report:
This isn’t the only way to do this, of course. You can group with nested FOREACH loops. In those nested loops, you can use either Windward Equations or you can directly call SQL equations in your OUT tags. You could also just use SET tags to keep running totals. I make use of SET tags in the example here to just see if it’s the first step of a new order loop.
With so many options, why would you bother using window functions?
That depends mostly on where your performance bottleneck is. If you need details, you can’t rely on a GROUP BY, so that’s out. If you’ve got a slow connection to your database, then you probably don’t want nested loops making what could be hundreds or thousands of queries.
But with a window function:
- It’s a lot more efficient from a network perspective. You can pull back summary and detail information with one hit to the database.
- It’s also better on the reporting server side than using conditionals and Windward Equations to combine subsets of your data. Your SQL server will optimize it for you and give you the best performance bang for your buck.
The drawback is that you’ve got to write that window function yourself. They’re not that hard for a DBA, but they’re definitely trickier than dragging and dropping from the wizard.
So as you’re designing your report, think about those two questions: How much do you want to know, and where is your performance trouble? Window functions may be what you need.
Author: Logan Miles
Having learned computers from “Hackers” and sales from “Thank You For Smoking,” Logan bridges the gap between the technical and non-technical. As a sales engineer, he enjoys not only solving problems but relating them in some way to the (mostly awful) movies he watches while recovering from skiing or hiking trips, depending on the season.
Other posts by Logan Miles