What does this SUM OVER code do?

[Breck_Carter]

Once upon a time I wrote this code... today my Sustaining Engineer asked "what does it do?" and alas, I am at a loss for words:

SELECT rroad_ranked_table.table_id,
       CAST ( SUM ( rroad_ranked_table.row_count ) 
                 OVER ( ORDER BY rroad_ranked_table.row_count DESC,
                                 rroad_ranked_table.table_id )       -- OVER ( ORDER BY must be unique
              AS DECIMAL ( 15, 4 ) )      AS sum_row_count,
       sum_row_count 
          / CAST ( ( SELECT SUM ( row_count ) 
                       FROM rroad_ranked_table ) 
                   AS DECIMAL ( 15, 4 ) ) AS fraction
  INTO #temp_row_fraction
  FROM rroad_ranked_table;

Here's the table:

DECLARE LOCAL TEMPORARY TABLE rroad_ranked_table (
   table_id                 UNSIGNED INT NOT NULL,
   row_fraction             DECIMAL ( 15, 4 ) NOT NULL DEFAULT ( 0.0 ),
   row_count                UNSIGNED BIGINT NOT NULL,
   size_fraction            DECIMAL ( 15, 4 ) NOT NULL DEFAULT ( 0.0 ),
   size_in_bytes            UNSIGNED BIGINT NOT NULL,
   CONSTRAINT PRIMARY KEY (
      table_id ) )
   NOT TRANSACTIONAL;

[Former Member · Accepted solution]

From the help, you'll know that

"If the window specification contains an ORDER BY clause, it is equivalent to specifying RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW."

Aside: my recommedation is to always specify either ROWS or RANGE in a window so that the intent of the computation is clear.

So the window aggregate SUM() is hence computing a cumulative sum for each row in rroad_ranked_table. The third SELECT list expression then computes the fraction of this cumulative sum over the sum of all of the row counts.

Does that help?

[Former Member · Accepted solution]

Yes, it helps a great deal! Thank you! Breck, give Glenn a million bounty points!!!

[Breck_Carter · Accepted solution]

@Glenn: My mental roadblock was "why on earth would I want a cumulative fraction?"... then I thought, "Glenn must be wrong"... then I realized "That's absurd!" (the Glenn must be wrong part, not the cumulative fraction part). Turns out this is a teeny part of the logic behind Foxhound's Largest Tables list which ranks the tables that account for at least 80% of the rows and 80% of the bytes... hence the ranking AND the summing. Not the funkiest code in Foxhound, but close... calculating the database version number, that wins funkiest.

[Breck_Carter · Accepted solution]

@Margaret: Glenn is Number One in points and will probably stay there without any bounties 🙂

[Former Member]

I really should know better than to quibble with Glenn, but here are a few comments anyhow:

First, the following statement from the help and Glenn's answer is not correct:

"If the window specification contains an ORDER BY clause, it is equivalent to specifying RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW."

The RANGE unit can only be used when:

• The ORDER BY clause contains a single element
• The single element of the ORDER BY if of datetime type or a number type

If you have an OVER clause with an ORDER BY with two elements, the you are not allowed to use RANGE units (you get an error Composite ORDER BY not allowed with RANGE SQLCODE=-966). In Breck's example, you could not use RANGE because there are two order-by elements. In other queries, the order by element could be a string.

Aha, you may be thinking, RANGE is clearly the wrong unit and we should instead use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Unfortunately the semantics are not the same when there are ties in the ORDER BY column. Consider the following:

create table T_OverExample(pk int, x int, y double);
insert into T_OverExample select row_num as pk, pk/2 as x, pk as y from sa_rowgenerator(0,10);
select *
, sum(y) over(order by x) sum
, sum(y) over(order by x rows between unbounded preceding and current row) sumrows
, sum(y) over(order by x,x range between unbounded preceding and current row) sumrange
from  T_OverExample;

In this example, we have sum and sumrange always equal. Since there are duplicate x values, the sum is computed over a group with a single x value, and the sum is returned for all rows in the group. The sumrows column is computed using ROWS units and the result is different. Even if there are tie groups according to the ORDER BY, the SUM is computed cumulatively with each row generating a new value. If there are tie groups, the order within the group is non-deterministic(*).

SQL Anywhere does not support a relatively new unit of GROUPS. When an ORDER BY is specified but the window does not define bounds, then the default bounds are actually GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The only real difference is that GROUPS does not put any restrictions on the number or types of elements in the ORDER BY.

A second comment is that in Breck's formulation there is a subquery to compute the total sum--usually this would be done as a second OVER clause as follows:

select pk, sum(y) over(order by x)/sum(y) over() cumulative_frac
from  T_OverExample;

In many cases there wont be a huge performance difference but this approach is really important if you have a PARTITION BY -- you want the fraction within the partition instead of the entire table.

(*) If the ORDER BY in an OVER clause is not unique, then the ordering is non-deterministic. Nevertheless, if there are two window functions with an identical ORDER BY, the server is required to choose the same ordering for both. So, if you were worrying about that detail, that one's ok.