Re: nth_value and row_number in a partition
От | MS (direkt) |
---|---|
Тема | Re: nth_value and row_number in a partition |
Дата | |
Msg-id | 938b82ac-9193-3fca-36fd-caefab52d87b@stb-datenservice.de обсуждение исходный текст |
Ответ на | RE: nth_value and row_number in a partition (Olivier Leprêtre <o.lepretre@gmail.com>) |
Список | pgsql-sql |
I think you can do this without any need to use nth_value.
Only first_value and current v2 is needed.
select roads, orders, v1, v2, segment,
first_value(v1) over(partition by roads, segment order by roads, orders) - v2
from test order by roads, orders;
The point is to define the partition by roads and segment but to order it via roads and orders.
Regards Martin
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: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 MartinAm 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.
-- Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010 HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
В списке pgsql-sql по дате отправления: