Calculated KPIs: Window Function

A calculated KPI now supports window functions for calculations.

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.

First read about how window functions work: 

 https://www.postgresql.org/docs/9.1/tutorial-window.html

• Go to Manage->KPIs & Dimensions and create a calculated KPI
• In the formula field you can now use window functions e.g. 

  • SUM(SUM(clicks)) OVER (PARTITION BY 0)

• The OVER clause can either be left empty OVER () or look like 'OVER (PARTITION BY n)', where n is a non-negative integer, meaning the window function will be computed over all but the last n dimensions.
• OVER () will compute the function over all records
• OVER (PARTITION BY 0) will compute the function differently for all distinct combinations of dimension values. When using aggregates as window functions, this will have the same effect as not computing the function at all (SUM(SUM(metric)) OVER () will yield the same results as SUM(metric))
• OVER (PARTITION BY 1) will compute the function for all distinct combinations of dimension values, excluding the last dimension. If dimensions are 'channel' and 'campaign', the function will be computed for every distinct value of 'channel'.
• Try the new metric in Explore!

EXAMPLE

 In this example:

• 2 dimensions are used in the table: GROUPS & Daily
• Metric Clicks with partition over 0: every row shows the same result as Clicks
• Metric Window over P 1: every row shows the result without considering the split from dimension Daily
• Metric Window over P 2: every row shows the result without considering the split from Dimension Daily & GROUPS, hence it shows the total result across all rows.

APPLICATION USECASE

Use the window function to compute the distance from the average cost.

• Define a custom KPI with the following formula:

  • (costs) - AVG(SUM(costs)) OVER (PARTITION BY 1). 

Definition of window function taken from here: https://www.postgresql.org/docs/9.1/tutorial-window.html