RE: nth_value and row_number in a partition

Hi Martin,

 

Here is an example in a "excel view". Red value are first value. 3 partitions defined by roads and segment columns.

The goal is to substract v2 (D) from v1 first value for each window. Hope this is clear enough.

 

Davir, you're right, imbricated is my bad translation for nested.

 

	
A

B

C

D

E

F

	

1

roads

orders

v1

v2

v3

segment

v3 calculation


2

41

1

632

0

632

1055

C2-D2


3

41

2

632

0

632

1055

C2-D3


4

41

3

600

16

616

1055

C2-D4


5

41

4

70

25

607

1055

C2-D5


6

41

5

60

30

30

1041

C6-D6


7

41

6

64

3

57

1041

C6-D7


8

41

7

14

2

12

1042

C8-D8


9

41

8

2

6

8

1042

C8-D9

 

Thanks very much for your help

 

De : Martin Stöcker [mailto:[email protected]] 
Envoyé : jeudi 25 janvier 2018 21:13
À : Olivier Leprêtre <[email protected]>; [email protected]
Objet : Re: nth_value and row_number in a partition

 

Hi Olivier

can you please give me the structure of your table, maybee some sample data too.
And please describe in words not in SQL your calculation.

Regards Martin

Am 25.01.2018 um 20:49 schrieb Olivier Leprêtre:

Hi David,

 

Thanks for your answer, I tried your suggestion as well as many other combinations, no success. Here are some of them. I just don't understand which syntax is required

 

select roads,orders,(first_value(v1) 

over (partition by roads,segment order by orders)-(nth_value(v2,cast(row_number() over (partition by roads,order by orders)  as integer)) over (partition by roads,segment order by orders))) as result 

from mytable

or

select roads,orders,(first_value(v1) 

over (partition by roads,segment order by orders)-(nth_value(v2,row_number() over (partition by roads,order by orders)::integer)) over (partition by roads,segment order by orders))) as result 

from mytable

 

>>syntax error near order (bold)

 

select roads,orders,(first_value(v1) 

over (partition by roads,segment order by orders)-(nth_value(v2,row_number() over (partition by roads)::integer)) over (partition by roads,segment order by orders))) as result 

from mytable

 

>> syntax error near over

 

select roads,orders,(first_value(v1) 

over (partition by roads,segment order by orders)-nth_value(v2,row_number() over (partition by roads)::integer) over (partition by roads,segment order by orders)) as result 

from mytable

 

>> window call cannot be imbricated

 

select roads,orders,(first_value(v1) 

over (partition by roads,segment order by orders)-nth_value(v2,row_number()  over (partition by roads,segment order by orders)::integer)) as result 

from mytable

 

>> nth_value requires an over clause

 

select roads,orders,(first_value(v1) 

over (partition by roads,segment order by orders)-nth_value(v2,row_number()::integer) over (partition by roads,segment order by orders)) as result 

from mytable

 

>> row_number requires an over clause

 

 

 

 

 

De : David G. Johnston [mailto:[email protected]] 
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre  <mailto:[email protected]> <[email protected]>
Cc : [email protected] <mailto:[email protected]> 
Objet : Re: nth_value and row_number in a partition

 

On Thursday, January 25, 2018, Olivier Leprêtre <[email protected] <mailto:[email protected]> > wrote:

nth_value(integer, bigint) doesn't exists.  

This is close, you just need to cast to integer. 

(cast(row_number() as integer)  over (partition by roads,segments order by orders))) 

You cannot separate the window function from its over clause.

 

Cast( Row_number() over (...) as integer )

 

Not tested...and I tend to use :: instead of cast

 

David J.