RE: nth_value and row_number in a partition
От | Olivier Leprêtre |
---|---|
Тема | RE: nth_value and row_number in a partition |
Дата | |
Msg-id | 004e01d396c6$907e6590$b17b30b0$@maule.fr обсуждение исходный текст |
Ответ на | Re: nth_value and row_number in a partition (Martin Stöcker <martin.stoecker@stb-datenservice.de>) |
Список | pgsql-sql |
David, Martin, thanks for your kind help everything works now !
Nice week-end to all of you
Olivier
De : Martin Stöcker [mailto:martin.stoecker@stb-datenservice.de]
Envoyé : jeudi 25 janvier 2018 21:13
À : Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-sql@lists.postgresql.org
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: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.
В списке pgsql-sql по дате отправления: