SQL Window Function

Window Function 1

PARTITION BY Clause

A PARTITION BY clause is used to partition rows of table into groups.

Syntax:

Window_function ( expression ) 
       Over ( partition by expr [order_clause] [frame_clause] ) 

order_clause and frame_clause are optional.
expr can be column names or built-in functions in MySQL.
But, standard SQL permits only column names in expr.

over() clause defines how to partition and order rows of table, which is to be processed by window function rank().

Running Total

select standard_qty,
		   date_trunc('month', occurred_at) as month,
		   sum(standard_qty) over (partition by date_trunc('month', occurred_at) order by occurred_at) as running_total
from orders;

1. Using Derek’s previous video as an example, create another running total. This time, create a running total of standard_amt_usd (in the orders table) over order time with no date truncation. Your final table should have two columns: one with the amount being added for each new row, and a second with the running total.

SELECT standard_amt_usd,
       SUM(standard_amt_usd) OVER (ORDER BY occurred_at