Обсуждение: nth_value and row_number in a partition

Hi,

I can't find the right syntax to use simultaneously nth_value and row_number in a partition.

I have a table with roads and their orders, a road segment name and two values v1 and v2

I want to decrement first_value v1 of each partition with the current record value v2 of the partition in a third column v3 in the same record. So I thought that I could combine and row_number which returns "number of the current row within its partition, counting from 1" with nth_value

My problem is that I get either syntax error near over, window function needs an over clause, nth_value(integer, bigint) doesn't exists. Query works if I just uses something like

nth_value(v2,1) but I can't find how I can replace "1" with the current row position in the partition (1,2,3…)

I tried dozen of syntax with no success. Here is one of them

select roads,orders,(first_value(v1) over (partition by roads,segment order by orders)-(nth_value(v2,(cast(row_number() as integer)  over (partition by roads,segments order by orders))) over (partition by roads,segments order by orders)))  as result from my table

Thanks for any help !

Olivier

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:david.g.johnston@gmail.com]
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <o.lepretre@gmail.com>
Cc : pgsql-sql@lists.postgresql.org
Objet : Re: nth_value and row_number in a partition

On Thursday, January 25, 2018, Olivier Leprêtre <o.lepretre@gmail.com> wrote:

nth_value(integer, bigint) doesn't exists.  

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

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.

Thanks very much for your help

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

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:david.g.johnston@gmail.com]
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <o.lepretre@gmail.com>
Cc : pgsql-sql@lists.postgresql.org
Objet : Re: nth_value and row_number in a partition

 

On Thursday, January 25, 2018, Olivier Leprêtre <o.lepretre@gmail.com> 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.

David, Martin, thanks for your kind help everything works now !

Nice week-end to all of you

Olivier

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

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:david.g.johnston@gmail.com]
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <o.lepretre@gmail.com>
Cc : pgsql-sql@lists.postgresql.org
Objet : Re: nth_value and row_number in a partition

 

On Thursday, January 25, 2018, Olivier Leprêtre <o.lepretre@gmail.com> 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.