Window Functions!
Window Functions are a great way to analyze your data in a different way! Similar to other calculation statements in SQL, window functions allow you to do a calculation across a set of rows or columns. The difference is with window functions the data will not be grouped into a single output row and will return a value for every row in that window. The values returned are calculated by utilizing the values from the sets of rows in that window. The window defines, for each row in the table, as set of rows that is used to compute additional attributes. A window is defined using a window specification (the OVER clause), and is based on three main concepts:
- Window Partitioning, which forms groups of rows (PARTITION clause)
- Window Ordering, which defines an order or sequence of rows within each partition (ORDER BY clause)
- Window Frames, which are defined relative to each row to further restrict the set of rows (ROWS specification)
There are two types of functions aggregation (AVG, COUNT, SUM, etc.) and ranking functions (Rank, Row_number, etc.) that can be used in window functions.
1. When do I use a Window Function?
There are many useful cases. Window functions can be leveraged so summarize information at a different level. For example, the below query without a window function will group the amount column by user_id and platform and give use summarized results.
Select user_id , platform , [Sum(price):$] total_user_spent From Purchases Group By user_id , platform
This would give you the following results:
If you wanted to see the individual rows of user ids, platform and price but also see the total spend by users you could use a window function! The query below will give you the detailed results of the User Id, individual price, platform and the total spend by user.
Select user_id , [price:$] Price , platform , [Sum(price) Over(Partition By user_id):$] As total_user_spent From Purchases
This would give you the following results:
2. How do I set up a Window Function?
The basic structure of a window function is:
Select < Window_Function > Over(Partition By < Field >) As < Field_Name > From < Table >
3. What functions are used in Window Functions?
The functions below can be used in window functions. Note that the available functions and syntax maybe different between databases. Window functions are available in PostgreSQL, SQL Server, Redshift, and Oracle (and calls them "Analytic Functions" ).
- AVG
- COUNT
- CUME_DIST
- DENSE_RANK
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- MAX
- MEDIAN
- MIN
- NTH_VALUE
- NTILE
- PERCENTILE_CONT
- PERCENTILE_DISC
- PERCENT_RANK
- RANK
- RATIO_TO_REPORT
- ROW_NUMBER
- STDDEV_POP
- STDDEV_SAMP
- SUM
- VAR_POP
- VAR_SAMP
And some more!
More examples on using the window functions check out this amazing blog post.
Using window functions with date series check out this great blog post.
-
I love using window functions -- so much so that I was wondering if they will ever be added to SiSense as an available function? I know you have the ranking functions so your engine already allows for row partitioning and calculations (albeit simple ones like counting from 1); would it be possible to expand on this to allow for at least *some* of the more common window functions? (This would greatly enhance the ability to perform these calculations on "non-sql" sources of data).
-
Reading this post, I was hoping it meant that window functions had been added to Sisense. They are one of the most powerful tools for BI, the kind that elevates BI tools from being a web-version of the old spreadsheets people used to use. To Sven's point, with more non-sql sources (like REST APIs) being added every day, the need for Sisense to natively adopt powerful analytical functionality like window functions is only continuing to grow.
With RANK, it is possible to create a pretty complex system of queries to get around it, but I really don't think anyone here wants to spend time repeatedly recreating well-documented window functions from scratch. Please consider adding!
Something like SQL Server's FIRST_VALUE() would be particularly handy!
Please sign in to leave a comment.
Comments
2 comments