Обсуждение: Window ?

Поиск
Список
Период
Сортировка

Window ?

От
Olivier Leprêtre
Дата:

Hi,

 

I have a road segment table with a few attributes for each. For each segment, I have a road index and a segment index something like :

 

road    seg     colA

1        1        att1

1        2        att2

1        3        att3

1        4        att4

2        1        att5

2        2        att6

 

I want to convert records into lines,

 

1        att1    att2    att3    att4

2        att5    att6    ...

 

I was considering using window function, with a partition on road but the problem is that segment count is different for each road, up to 30. So it's seems a bit rough to write something like

 

select nth_value(colA,1), nth_value(colA,2), nth_value(colA,3), nth_value(colA,4)...

 

Of course I can write a script with a loop to insert segments one road after the other, but before going to this, I would appreciate a smarter idea !

 

Thanks

 

 


Garanti sans virus. www.avast.com

Re: Window ?

От
Gerardo Herzig
Дата:

----- Mensaje original -----
> De: "Olivier Leprêtre" <o.lepretre@gmail.com>
> Para: pgsql-sql@lists.postgresql.org
> Enviados: Miércoles, 13 de Junio 2018 11:33:50
> Asunto: Window ?

> Hi,
>
>
>
> I have a road segment table with a few attributes for each. For each
> segment, I have a road index and a segment index something like :
>
>
>
> road    seg     colA
>
> 1        1        att1
>
> 1        2        att2
>
> 1        3        att3
>
> 1        4        att4
>
> 2        1        att5
>
> 2        2        att6
>
>
>
> I want to convert records into lines,
>
>
>
> 1        att1    att2    att3    att4
>
> 2        att5    att6    ...
>
>
Looks like a solution with "pivot":
https://www.postgresql.org/docs/current/static/tablefunc.html
Look for "crosstab" functions.

HTH
Gerardo

Re: Window ?

От
"David G. Johnston"
Дата:
On Wed, Jun 13, 2018 at 7:33 AM, Olivier Leprêtre <o.lepretre@gmail.com> wrote:


I want to convert records into lines,

 

1        att1    att2    att3    att4

2        att5    att6    ...



​I would recommend either an actual array (array_agg function) or a structured string (string_agg function)

SELECT road, array_agg(colA ORDER BY seg)
FROM tbl
GROUP BY road;

Otherwise you will need a output 31 columns with unused columns holding null.  You can do that brute-force or you can leverage the tablefunc extension's crosstab function.

https://www.postgresql.org/docs/10/static/tablefunc.html

David J.
​

RE: Window ?

От
Olivier Leprêtre
Дата:

Thanks David, Gerardo,

 

I had a look to crosstab functions but wasn't able to make them work, documentation is not precise enough to me, I would appreciate if someone has a working sample. Based on your suggestion, I will try again anyway. The main difficulty is that I have not only one column but half a dozen taht I would like to appear

 

road 1 colA colB colC colD colE ColF colA colB colC colD colE ColF colA colB colC colD colE ColF ...

road 2 colA colB...

 

for each road.

 

Olivier

De : David G. Johnston [mailto:david.g.johnston@gmail.com]
Envoyé : mercredi 13 juin 2018 16:55
À : Olivier Leprêtre
Cc : pgsql-sql
Objet : Re: Window ?

 

On Wed, Jun 13, 2018 at 7:33 AM, Olivier Leprêtre <o.lepretre@gmail.com> wrote:

 

I want to convert records into lines,

 

1        att1    att2    att3    att4

2        att5    att6    ...

 

 

​I would recommend either an actual array (array_agg function) or a structured string (string_agg function)

 

SELECT road, array_agg(colA ORDER BY seg)

FROM tbl

GROUP BY road;

 

Otherwise you will need a output 31 columns with unused columns holding null.  You can do that brute-force or you can leverage the tablefunc extension's crosstab function.

 

 

David J.

​


Garanti sans virus. www.avast.com

Re: Window ?

От
Gerardo Herzig
Дата:

----- Mensaje original -----
> De: "Olivier Leprêtre" <o.lepretre@gmail.com>
> Para: "pgsql-sql" <pgsql-sql@lists.postgresql.org>
> Enviados: Miércoles, 13 de Junio 2018 12:14:45
> Asunto: RE: Window ?

> Thanks David, Gerardo,
>
>
>
> I had a look to crosstab functions but wasn't able to make them work,
> documentation is not precise enough to me, I would appreciate if someone has a
> working sample. Based on your suggestion, I will try again anyway. The main
> difficulty is that I have not only one column but half a dozen taht I would
> like to appear
>
>
>
> road 1 colA colB colC colD colE ColF colA colB colC colD colE ColF colA colB
> colC colD colE ColF ...
>
> road 2 colA colB...
>
>
>
> for each road.
>
Look at section 39.1.4. of https://www.postgresql.org/docs/current/static/tablefunc.html
There is a working example that looks pretty close to what you want.

HTH
Gerardo