CODEWITHSUNDEEP
sqlpartitioningwindowing
savx2
savx2

Reputation: 1071

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.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 1

Related Questions