Introduction
The main goal of this article is to understand the fundamental concepts of window functions and apply them to your SQL workflow. Window functions are nothing more than FOPO (Function Over PartitionBy OrderBy). It is the combination of existing SQL concepts bundled into a powerful SQL function. Here’s a quick outline of what I’ll be covering in this article.
- The GROUP BY
- The Window Function — 4 Major Components (The FOPO)
- The func() OVER ()
- The PARTITION BY
- The ORDER BY
- The PARTITION BY & ORDER BY
- Summary
- Appendix
The GROUP BY
If you have a good understanding of the GROUP BY clause, you already have a head start in understanding window functions. In a nutshell, the GROUP BY clause in a SQL statement condenses a table into fewer rows. In the case of a single GROUP BY column, you are grouping by a column and outputting a single row for every unique value under that column. You can also group multiple columns. In that case, the output will consist of unique combinations of the specified columns with or without some aggregate functions. Here’s an example.
The 1 in GROUP BY 1 represents the position of the grouping column under the SELECT. Columns listed under the SELECT is like an array with starting index position 1.
In this product_orders table, every order_id contains all product items (product_name) purchased by a customer. On the top-right view, the goal of the SQL query is to output the summed amount for each order_id. First, the order_id is designated as the GROUP BY column. Second, all the different product amounts corresponding to the order_id are summed into one value (total_amount) using the SUM aggregate function. Now, let’s take a deeper look into the mechanics of window functions.
#window-functions #data-analysis #postgresql #sql #data-science #data analysis