Amazon Redshift cloud data warehouse supports many useful windows or analytical functions. Many Redshift reporting queries use the analytic functions such as cumulative sum and average. But, because of Redshift limitation, you cannot call an analytics function within another. Whenever you try to call an analytics function within another analytics function, you will end up with an error such as “Invalid operation: aggregate function calls may not have nested aggregate or window function”. In this article, we will check how to use the nested window functions in Amazon Redshift with an alternate example.
Amazon Redshift does not allow you to define the nested window function in your queries. You will have to use alternative methods such as common table expressions (CTEs) or create table subquery to calculate value for windows function and again apply another window function in a SELECT clause.
Amazon Redshift Nested Window Functions
In SQL, a window is a group of related rows. A window function is any function that operates over a group (window) of rows. For example, you can define a window on date, with all rows in the same month grouped in the same window.
Reusing Columns using Redshift Lateral Column alias
Amazon Redshift supports reusing expressions at the same level.
For example,
SELECT i AS col_1,
col_1 * p AS col_2,
CASE
WHEN col_1 > col_2 THEN 'Y'
ELSE 'N'
END AS col_3
FROM table_1;Above query work perfectly fine on Redshift cluster. But, query will fail if you include multiple aggregate or window functions on column alias.
For example, following query will end up in “aggregate function calls may not have nested aggregate” error.
SELECT Sum(i) AS col_1,
Avg(col_1) * p AS col_2,
CASE
WHEN col_1 > col_2 THEN 'Y'
ELSE 'NO'
END AS col_3
FROM table_1; Redshift Nested Window Functions Using Common Table Expression (CTE)
As an alternative method to nest Redshift window functions or aggregate function, we can create a common table expression or derived tables to nest windows or analytic functions.
For example, consider following example that nest windows functions using common table expressions (CTE).
WITH tmp
AS (SELECT SUM(i)
over (
PARTITION BY p
ORDER BY o ROWS BETWEEN unbounded preceding AND CURRENT ROW)
cumu_sum,
*
FROM EXAMPLE_CUMULATIVE)
SELECT SUM(cumu_sum)
over(
PARTITION BY p
ORDER BY o ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS nest_cumu_sum,
*
FROM tmp;The above query will work on Amazon Redshift without any issues.
Amazon Redshift Nested Window Functions Using Subquery
Similar to CTE, another method is to use a subquery to nest window or aggregate functions.
Consider following example that nest windows functions using common table expressions.
SELECT SUM(cumu_sum)
over(
PARTITION BY p
ORDER BY o ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS nest_cumu_sum,
*
FROM (SELECT SUM(i)
over (
PARTITION BY p
ORDER BY o ROWS BETWEEN unbounded preceding AND CURRENT ROW)
cumu_sum,
*
FROM example_cumulative) AS tmp; The above query will work on Redshift cluster without any issues.
Hope this helps 🙂