|
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;
|
|
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:
The RANGE unit can only be used when:
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 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. |
