I want build a sum in a window based on an expression, in in this expression I want reference a value from current row. How can I do that?


create table t 
(
  a integer not null,
  b integer not null, 
  primary key (a)
);

insert into t values (0, 1);
insert into t values (1, 1);
insert into t values (2, 1);

select sum( case when a+b > t.a then 1 else 0 end case) over (order by a)
from t order by a;

sum(case when t.a+t.b > t.a then 1 else 0 end) over(order by t.a asc) 
--------------------------------------------------------------------- 
1                                                                     
2                                                                     
3                                                                     
(3 rows)
Execution time: 0.078 seconds

I expected in the code above always 1 as result.

asked 17 May '12, 16:47

mikron's gravatar image

mikron
913412
accept rate: 0%


For an idea of how to do this, see Example 14 in this whitepaper.

permanent link

answered 17 May '12, 17:24

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

I thought about mentioned approach with two windows, how it pointed in example 14, but I didn't see, how I can apply that. My trouble is to refer to a value from current result row (not current row in window) in an expression, that used in sum. In other words, I want to access a value from current result row and a value from current window row.

(18 May '12, 14:03) mikron
Replies hidden

Currently, you can't do that within the same window function because the semantics of the function are a consequence of the definition of the window, which, if ORDER BY is not specified, is from unbounded preceding to current row.

Today, you have to recompose the expression you're trying to compute and compute it using different window functions.

Recently, the SQL standard committee approved extensions that appear in SQL:2011 that permit you to refer directly to a specific value, like the value of the attribute at the current row. But I'm not aware of any product that supports that new syntax yet.

(18 May '12, 14:13) Glenn Paulley
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19

question asked: 17 May '12, 16:47

question was seen: 2,048 times

last updated: 18 May '12, 14:13