agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: MS (direkt) <martin.stoecker@stb-datenservice.de>
To: Olivier Leprêtre <o.lepretre@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: nth_value and row_number in a partition
Date: Fri, 26 Jan 2018 08:31:11 +0100
Message-ID: <938b82ac-9193-3fca-36fd-caefab52d87b@stb-datenservice.de> (raw)
In-Reply-To: <018901d3961e$7712c360$65384a20$@gmail.com>
References: <015201d3960a$a2db0b60$e8912220$@gmail.com>
<CAKFQuwa3SCRhpnjOK0=dNxL=vt2Lvrs9VYLhJ8p2UZrUqieShQ@mail.gmail.com>
<016401d39615$9b620bd0$d2262370$@gmail.com>
<ebb0e64d-ab2a-cd82-c33f-c85550312d74@stb-datenservice.de>
<018901d3961e$7712c360$65384a20$@gmail.com>
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
view thread (9+ messages) latest in thread
Message-ID: <938b82ac-9193-3fca-36fd-caefab52d87b@stb-datenservice.de>
Permalink: ../938b82ac-9193-3fca-36fd-caefab52d87b@stb-datenservice.de/
Also on: postgresql.org/message-id/938b82ac-9193-3fca-36fd-caefab52d87b@stb-datenservice.de
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: martin.stoecker@stb-datenservice.de, o.lepretre@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: nth_value and row_number in a partition
In-Reply-To: <938b82ac-9193-3fca-36fd-caefab52d87b@stb-datenservice.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox