Level Based Aggregate using Incorta Materialized View

[suxinji]

Overview

This is the second part of the articles that discusses level-based measures.  The essential part of a level based measure is an aggregation that is calculated at a specific level of detail with a result can be repeated at the lower level when they are viewed together.

They can be calculated using the standard SQL window functions.  We are including the SQL logic here using an Incorta materialized view as a comparison.  

If the data has to be persisted, either an Incorta Materialized View or an Incorta Analyzer table can be used.  If the data has to be calculated during runtime because the number shown on a dashboard may be subject to dashboard prompt filters, an Incorta dashboard insight using an Aggregate Table can be used.

See: Level-based aggregate in Incorta (using Analyzer) 

Solution

SQL Syntax:

SELECT Column_Name,
       count(Column_Name_ID) OVER (PARTITION BY Column_Name) AS lvl1_row_count,
       min(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE)) OVER (PARTITION BY Column_Name) AS lvl1_discount_min,
       max(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE)) OVER (PARTITION BY Column_Name) AS lvl1_discount_max,
       avg(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE)) OVER (PARTITION BY Column_Name) AS lvl1_discount_avg,
       percentile_approx(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE), 0.5, 100) OVER (PARTITION BY Column_Name) AS lvl1_discount_median
FROM Schema_Name.Table_Name