Window Functions

When do you use window functions over aggregate functions?

1. When you want to measure trends or changes over rows.

2. When you want to rank a column to use for outreach/prioritization.

Example:

Problem	Window Function
Has the average price of airline tickets gone up this year?	Calculate a running average of ticket prices partitioned by the month and ordered by time
What’s the best way to keep the running total orders of customers?	Calculate the running count of orders and maintain it as a separate row within the table
Use a combination of factors to rank companies most likely to need a loan.	Include a rank column in the output to be used by a BD rep to reach out to potential customers.

What is window function?

Window Function: A window function is a calculation across a set of rows in a table that are somehow related to the current row. This means we’re typically:

1. Calculating running totals that incorporate the current row or,

2. Ranking records across rows, inclusive of the current one

A window function is similar to aggregate functions combined with GROUP BY clauses but have one key difference: Window functions retain the total number of rows between the input table and the output table (or result). Behind the scenes, the window function is able to access more than just the current row of the query result.

a window function allows users to compare one row to another without doing any joins.

When window functions are used, you’ll notice new column names like the following:

• Average running price

• Running total orders

• Running sum sales

• Rank

• Percentile

3.5. Window FunctionsPostgreSQL Documentation

9.22. Window FunctionsPostgreSQL Documentation

Core

• PARTITION BY: A sub-clause of the OVER clause. Similar to GROUP BY.

• OVER: Typically precedes the partition by that signals what to “GROUP BY”.

• Aggregates: Aggregate functions that are used in window functions, too (e.g., sum, count, avg).

AGGREGATE_FUNCTION (column_1) OVER
 (PARTITION BY column_2 ORDER BY column_3)
  AS new_column_name;

SQL SERVER - What is the OVER Clause? - Notes from the Field #101 - SQL Authority with Pinal DaveSQL Authority with Pinal Dave

GROUP BY	PARTITION BY
The ouput has a lessened # of records based on the GROUP BY column	The output maintains the # of records in the original table.
The output is one row per GROUP BY in the result set.	If the original table had 10 rows, the PARTITION BY will maintain 10 rows.

Ranking

• ROW_NUMBER(): Ranking is distinct amongst records even with ties in what the table is ranked against.

• RANK(): Ranking function where a row could get the same rank if they have the same value and ranks skip for subsequent values.

• DENSE_RANK(): Ranking function similar to RANK() but ranks are not skipped with ties.

SELECT ROW_NUMBER() OVER(ORDER BY date_time) AS rwrank,
       RANK() OVER(ORDER BY date_time) AS rrank,
       DENSE_RANK() OVER(ORDER BY date_time) AS drrank,
       date_time
FROM   db;

Advanced

• Aliases: Shorthand that can be used if there are several window functions in one query.

SELECT order_id,
       order_total,
       order_price,
       SUM(order_total) OVER monthly_window AS running_monthly_sales,
       COUNT(order_id) OVER monthly_window AS running_monthly orders,
       AVG(order_price) OVER monthly_window AS average_monthly_price
FROM   amazon_sales_db
WHERE  order_date < '2017-01-01'
WINDOW monthly_window AS
       (PARTITION BY month(order_date) ORDER BY order_date);

• Percentiles: Defines what percentile a value falls into over the entire table.

NTILE(# of buckets) OVER (ORDER BY ranking_column) AS new_column_name

SELECT  customer_id,
        composite_score,
        NTILE(100) OVER(ORDER BY composite_score) AS percentile
FROM    customer_lead_score;

• Lag/Lead: Calculating differences between rows’ values.

SELECT account_id,
       standard_sum,
       LAG(standard_sum) OVER(ORDER BY standard_sum) AS lag,
       LEAD(standard_sum) OVER (ORDER BY standard_sum) AS lead,
       standard_sum - LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag_diff,
       LEAD(standard_sum) OVER (ORDER BY standard_sum) - standard_sum AS lead_diff
FROM (
       SELECT account_id,
              SUM(standard_qty) AS standard_sum
       FROM orders
       GROUP BY 1
) sub