Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eeoW6-0005t1-SZ for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 20:53:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eeoW6-0006fN-0M for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 20:53:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eeoW5-0006fA-Lu for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 20:53:01 +0000 Received: from mail-wm0-x22e.google.com ([2a00:1450:400c:c09::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eeoVx-0000qz-TC for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 20:53:00 +0000 Received: by mail-wm0-x22e.google.com with SMTP id v71so17400045wmv.2 for ; Thu, 25 Jan 2018 12:52:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=Xzz0kSzJ0JjyyhzBMQD/dFWg8qd2o+RMBXJ79smCK/M=; b=RfazbtfdUQcnzTc56XZJPDPHVXFKhPEAuRTEealfXDEFnSY624NMG5ra0Mb1hzx9l2 /tnC8YfpoNmQqBPmINJ6Jnqvu0A/zXLBYJih2tacf4QGJB2FXdcy9AF1csyBlwjv0QFH 6IEM1/i4460xCaTpyVNYcT0ZUpbdPa5xB7j8h7oa1YNeSUHgAo2vFvCZn1Ubu3uaDisi +IqVCDuotseACHXkjBjqnybQdbLi654Voj6y9YQ5Be79VSsCrocW/400JC8/shF7qqyn dRdIbCi8wxYbb4O+HnICOgsyZfn5956+YqH0LN56bCBF45ZPjp86ON1iXHlM6aX31Z1l nfUQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=Xzz0kSzJ0JjyyhzBMQD/dFWg8qd2o+RMBXJ79smCK/M=; b=qLELGARLYNaJOGqZDwmkeCvA/GTzj6tmo55W+oQ2+TGUVNd1NvEef21vEH0L+56H5Y JEz4SApHDsVG0BhuJP41/j99RiwbThixlJbEbkhfNDCOOe75acsUxSISaPA+f2YCoOhT Wvn/79fS1OagqsjXxe2BFMC2MY09Sij0QGQCtleLDi7QPOz73njcczoJ993rOi8aINwX l/dfiC09+xfLVjENbFvDZqRz6GdMQSoAfmV4Hae/+1EfY3MUcC7KxWAgd3jb7YYQ1zoD 3zrnvWPFTKgeVaN+YhfNhpBceZSgjyVhTD9+xpAOm/HYlvF4z50upDcp9hSeU9GtLbKv SSjQ== X-Gm-Message-State: AKwxyte0ZNysSr/ICFfJO3eNtXHFrvOvqFPUkwdCs1oW+ZdwQb6Hyedn 3+I0cs9rqW1mv0oGhEjWCEQ= X-Google-Smtp-Source: AH8x225fNJGnEzWnDGDeHQH+3Ph5dLyaYXzpfdSfHY+dchKZHGJhkAtY2KjXvXwPFMA8IZRRTdxb/g== X-Received: by 10.28.245.25 with SMTP id t25mr8158407wmh.77.1516913572036; Thu, 25 Jan 2018 12:52:52 -0800 (PST) Received: from PARME (66.228.71.37.rev.sfr.net. [37.71.228.66]) by smtp.gmail.com with ESMTPSA id s44sm7806916wrc.64.2018.01.25.12.52.50 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 25 Jan 2018 12:52:51 -0800 (PST) From: =?utf-8?Q?Olivier_Lepr=C3=AAtre?= To: =?utf-8?Q?'Martin_St=C3=B6cker'?= , References: <015201d3960a$a2db0b60$e8912220$@gmail.com> <016401d39615$9b620bd0$d2262370$@gmail.com> In-Reply-To: Subject: RE: nth_value and row_number in a partition Date: Thu, 25 Jan 2018 21:52:49 +0100 Message-ID: <018901d3961e$7712c360$65384a20$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_018A_01D39626.D8DAD4E0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQG77s7ltW0bH8Fhn8qzGyplstaMAQHCKlo9Ac3YeKAB/y9JjKOH5K2Q Content-Language: fr X-Antivirus: Avast (VPS 180125-2, 25/01/2018), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_018A_01D39626.D8DAD4E0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi Martin, =20 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. =20 Davir, you're right, imbricated is my bad translation for nested. =20 =09 A B C D E F =09 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 =20 Thanks very much for your help =20 De : Martin St=C3=B6cker [mailto:martin.stoecker@stb-datenservice.de]=20 Envoy=C3=A9 : jeudi 25 janvier 2018 21:13 =C3=80 : Olivier Lepr=C3=AAtre ; = pgsql-sql@lists.postgresql.org Objet : Re: nth_value and row_number in a partition =20 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=C3=AAtre: Hi David, =20 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 =20 select roads,orders,(first_value(v1)=20 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=20 from mytable or select roads,orders,(first_value(v1)=20 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=20 from mytable =20 >>syntax error near order (bold) =20 select roads,orders,(first_value(v1)=20 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=20 from mytable =20 >> syntax error near over =20 select roads,orders,(first_value(v1)=20 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=20 from mytable =20 >> window call cannot be imbricated =20 select roads,orders,(first_value(v1)=20 over (partition by roads,segment order by = orders)-nth_value(v2,row_number() over (partition by roads,segment = order by orders)::integer)) as result=20 from mytable =20 >> nth_value requires an over clause =20 select roads,orders,(first_value(v1)=20 over (partition by roads,segment order by = orders)-nth_value(v2,row_number()::integer) over (partition by = roads,segment order by orders)) as result=20 from mytable =20 >> row_number requires an over clause =20 =20 =20 =20 =20 De : David G. Johnston [mailto:david.g.johnston@gmail.com]=20 Envoy=C3=A9 : jeudi 25 janvier 2018 19:44 =C3=80 : Olivier Lepr=C3=AAtre = Cc : pgsql-sql@lists.postgresql.org = =20 Objet : Re: nth_value and row_number in a partition =20 On Thursday, January 25, 2018, Olivier Lepr=C3=AAtre = > wrote: nth_value(integer, bigint) doesn't exists. =20 This is close, you just need to cast to integer.=20 (cast(row_number() as integer) over (partition by roads,segments order = by orders)))=20 You cannot separate the window function from its over clause. =20 Cast( Row_number() over (...) as integer ) =20 Not tested...and I tend to use :: instead of cast =20 David J. =20 ------=_NextPart_000_018A_01D39626.D8DAD4E0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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=C3=B6cker = [mailto:martin.stoecker@stb-datenservice.de] =
Envoy=C3=A9 : jeudi 25 janvier 2018 = 21:13
=C3=80 : Olivier Lepr=C3=AAtre = <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=C3=AAtre:

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=C3=A9 : jeudi 25 janvier 2018 = 19:44
=C3=80 : Olivier Lepr=C3=AAtre <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=C3=AAtre = <o.lepretre@gmail.com> = wrote:

nth_value(in= teger, 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.

 

------=_NextPart_000_018A_01D39626.D8DAD4E0--