agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feednth_value and row_number in a partition
9+ messages / 5 participants
[nested] [flat]
* nth_value and row_number in a partition
@ 2018-01-25 18:30 Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Olivier Leprêtre @ 2018-01-25 18:30 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-01-25 18:43 ` David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2018-01-25 18:43 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
@ 2018-01-25 19:49 ` Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 19:57 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 20:12 ` Re: nth_value and row_number in a partition Martin Stöcker <martin.stoecker@stb-datenservice.de>
0 siblings, 2 replies; 9+ messages in thread
From: Olivier Leprêtre @ 2018-01-25 19:49 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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 <mailto: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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-01-25 19:57 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2018-01-25 19:57 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
On Thu, Jan 25, 2018 at 12:49 PM, Olivier Leprêtre <o.lepretre@gmail.com>
wrote:
> 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
>
I take it "imbricated" is the word for "nested" in a different language...
WITH vals AS ( VALUES (1), (2), (3) )
SELECT max( row_number() OVER () ) OVER ()
FROM vals;
Results in:
SQL Error: ERROR: window function calls cannot be nested
LINE 2: SELECT max( row_number() OVER () ) OVER ()
Which means that what you are trying to do (or at least they way you are
trying to do it) cannot be done.
You will need to resort to a subquery to compute the row_number for each
row and then feed that into the upper query as column reference.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-01-25 20:12 ` Martin Stöcker <martin.stoecker@stb-datenservice.de>
2018-01-25 20:52 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-26 16:56 ` RE: nth_value and row_number in a partition Olivier Leprêtre <olepretre@maule.fr>
1 sibling, 2 replies; 9+ messages in thread
From: Martin Stöcker @ 2018-01-25 20:12 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-sql@lists.postgresql.org
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
> <mailto: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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 20:12 ` Re: nth_value and row_number in a partition Martin Stöcker <martin.stoecker@stb-datenservice.de>
@ 2018-01-25 20:52 ` Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-26 07:31 ` Re: nth_value and row_number in a partition MS (direkt) <martin.stoecker@stb-datenservice.de>
1 sibling, 1 reply; 9+ messages in thread
From: Olivier Leprêtre @ 2018-01-25 20:52 UTC (permalink / raw)
To: 'Martin Stöcker' <martin.stoecker@stb-datenservice.de>; pgsql-sql@lists.postgresql.org
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 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 <mailto:o.lepretre@gmail.com> <o.lepretre@gmail.com>
Cc : pgsql-sql@lists.postgresql.org <mailto: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 <mailto: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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 20:12 ` Re: nth_value and row_number in a partition Martin Stöcker <martin.stoecker@stb-datenservice.de>
2018-01-25 20:52 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-01-26 07:31 ` MS (direkt) <martin.stoecker@stb-datenservice.de>
0 siblings, 0 replies; 9+ messages in thread
From: MS (direkt) @ 2018-01-26 07:31 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-sql@lists.postgresql.org
Hi
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
Am 25.01.2018 um 21:52 schrieb Olivier Leprêtre:
>
> 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 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>
> <mailto:o.lepretre@gmail.com>
> *Cc :* pgsql-sql@lists.postgresql.org
> <mailto: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 <mailto: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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 20:12 ` Re: nth_value and row_number in a partition Martin Stöcker <martin.stoecker@stb-datenservice.de>
@ 2018-01-26 16:56 ` Olivier Leprêtre <olepretre@maule.fr>
2018-01-26 17:04 ` Re: nth_value and row_number in a partition Martin Stöcker <martin.stoecker@stb-datenservice.de>
1 sibling, 1 reply; 9+ messages in thread
From: Olivier Leprêtre @ 2018-01-26 16:56 UTC (permalink / raw)
To: 'Martin Stöcker' <martin.stoecker@stb-datenservice.de>; pgsql-sql@lists.postgresql.org; 'David G. Johnston' <david.g.johnston@gmail.com>
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 <mailto:o.lepretre@gmail.com> <o.lepretre@gmail.com>
Cc : pgsql-sql@lists.postgresql.org <mailto: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 <mailto: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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: nth_value and row_number in a partition
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` Re: nth_value and row_number in a partition David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` RE: nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 20:12 ` Re: nth_value and row_number in a partition Martin Stöcker <martin.stoecker@stb-datenservice.de>
2018-01-26 16:56 ` RE: nth_value and row_number in a partition Olivier Leprêtre <olepretre@maule.fr>
@ 2018-01-26 17:04 ` Martin Stöcker <martin.stoecker@stb-datenservice.de>
0 siblings, 0 replies; 9+ messages in thread
From: Martin Stöcker @ 2018-01-26 17:04 UTC (permalink / raw)
To: Olivier Leprêtre <olepretre@maule.fr>; pgsql-sql@lists.postgresql.org, "'David G. Johnston'" <david.g.johnston@gmail.com>
same to you
it's always a pleasure to help
Am 26.01.2018 um 17:56 schrieb Olivier Leprêtre:
>
> 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>
> <mailto:o.lepretre@gmail.com>
> *Cc :* pgsql-sql@lists.postgresql.org
> <mailto: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 <mailto: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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2018-01-26 17:04 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-25 18:30 nth_value and row_number in a partition Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 18:43 ` David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 19:49 ` Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-25 19:57 ` David G. Johnston <david.g.johnston@gmail.com>
2018-01-25 20:12 ` Martin Stöcker <martin.stoecker@stb-datenservice.de>
2018-01-25 20:52 ` Olivier Leprêtre <o.lepretre@gmail.com>
2018-01-26 07:31 ` MS (direkt) <martin.stoecker@stb-datenservice.de>
2018-01-26 16:56 ` Olivier Leprêtre <olepretre@maule.fr>
2018-01-26 17:04 ` Martin Stöcker <martin.stoecker@stb-datenservice.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox