Savvas Savvides Savvas Savvides - 7 months ago 10
SQL Question

SQL windowing scope

I am learning how to use windowing functions and came across this SQL statement.

select
...
sum(ss_ext_sales_price) * 100 / sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio

from
...
where
...
group by
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price


My question is whether the
i_class
partition applies to all 3 of these
SUM
functions or whether some of them are calculated using the
group by
grouping.

Answer

Window functions and aggregation functions . . . they look very awkward when you first start using them together. The partitioning clause has nothing to do with the first sum(), so let's just look at the denominator:

 sum( sum(ss_ext_sales_price) ) over (partition by i_class) as revenueratio

The over applies to the first sum(). This is taking calculate the sum of the sum column . . . that is, the total amount. I think it helped me to break this out:

 sum(ss_ext_sales_price)
 sum( sum(ss_ext_sales_price) ) over (partition by i_class) as revenueratio

In fact, you are not allowed to have a window function as an argument to an aggregation function:

 sum( sum(ss_ext_sales_price) over (partition by i_class) ) as revenueratio