transposed query?

[javier garcia - CEBAS]

Hi;
I've got problems with a query. I'm not sure if it is possible to do this
with Postgres, although I think it should be.

I had resolved these kind of queryes in MSAccess, where they are called
"cross references querys" but I can't find my way in Postgres.

My table 'muestras_rambla' is like:
date | id_punto | muestra | flow | n_nitrato ...
--------------------------+----------+---------+---------+-----------
06/02/2003 00:00:00 CET | 1 | 1 | 699.462 | 18.44
06/02/2003 00:00:00 CET | 1 | 2 | 699.462 | 13.79
06/02/2003 00:00:00 CET | 2 | 1 | 341.05 | 11.15
06/02/2003 00:00:00 CET | 2 | 2 | 341.05 | 17.3
06/02/2003 00:00:00 CET | 3 | 1 | 514.05 | 22.17
06/02/2003 00:00:00 CET | 3 | 2 | 514.05 | 16.62
06/02/2003 00:00:00 CET | 4 | 1 | 466.884 | 18.99
06/02/2003 00:00:00 CET | 4 | 2 | 466.884 | 19.02
06/02/2003 00:00:00 CET | 6 | 1 | 30.012 | 5.91
06/02/2003 00:00:00 CET | 6 | 2 | 30.012 | 4.15
19/02/2003 00:00:00 CET | 1 | 1 | 911.43 | 17.66
19/02/2003 00:00:00 CET | 1 | 2 | 911.43 | 9.23
....

where I 'id_punto' can take the values from 1 to 6, and muestra is 1 or 2.
Commonly, for each 'date' and 'id_punto' we have two samples (muestra), and
the 'flow' is also commonly the same for the two samples.

The case is that for every 'date' I want to have the 'time series' of flows
in the way:
date 1 2 3 4 6
03/10/2002 606.92 323.08 440.69 393.61 5.49
17/10/2002 348.19 400.32 319.33 211.26 2.53
29/10/2002 411.57 409.66 310.37 362.20 9.36
14/11/2002 446.96 373.72 302.79 348.69 5.18
26/11/2002 381.20 386.85 307.77 240.75 6.41
12/12/2002 416.84 307.03 317.94 249.36 5.64
26/12/2002 743.04 380.30 539.94 521.60 3.69
09/01/2003 403.07 301.16 370.30 379.41 3.69
....

where the names of the rows are the values in 'id_punto' and the values of
the data are the average (for every 'id_punto' and 'date') between the two
samples (muestra).

In MSaccess this is solve with:
TRANSFORM avg(muestras_rambla.flow) AS avg_flow
SELECT muestras_rambla.date
FROM muestras_rambla
GROUP BY muestras_rambla.date
PIVOT muestras_rambla.id_punto;

All I can do in postgres is :
--------
SELECT muestras_rambla.fecha, id_punto, avg(caudal) AS avg_caudal
FROM muestras_rambla
GROUP BY fecha, id_punto
--------
to get:
fecha | id_punto | avg_caudal
--------------------------+----------+------------------
03/10/2002 00:00:00 CEST | 1 | 606.924
03/10/2002 00:00:00 CEST | 2 | 323.079
03/10/2002 00:00:00 CEST | 3 | 440.690
....
But, how could manage this to get the 'time series' table?

Thanks for your help and best wishes

Javier

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Nick Barr]

javier garcia - CEBAS wrote:

Hi;
I've got problems with a query. I'm not sure if it is possible to do this
with Postgres, although I think it should be.

I had resolved these kind of queryes in MSAccess, where they are called
"cross references querys" but I can't find my way in Postgres.

My table 'muestras_rambla' is like:
date | id_punto | muestra | flow | n_nitrato ...
--------------------------+----------+---------+---------+-----------
06/02/2003 00:00:00 CET | 1 | 1 | 699.462 | 18.44
06/02/2003 00:00:00 CET | 1 | 2 | 699.462 | 13.79
06/02/2003 00:00:00 CET | 2 | 1 | 341.05 | 11.15
06/02/2003 00:00:00 CET | 2 | 2 | 341.05 | 17.3
06/02/2003 00:00:00 CET | 3 | 1 | 514.05 | 22.17
06/02/2003 00:00:00 CET | 3 | 2 | 514.05 | 16.62
06/02/2003 00:00:00 CET | 4 | 1 | 466.884 | 18.99
06/02/2003 00:00:00 CET | 4 | 2 | 466.884 | 19.02
06/02/2003 00:00:00 CET | 6 | 1 | 30.012 | 5.91
06/02/2003 00:00:00 CET | 6 | 2 | 30.012 | 4.15
19/02/2003 00:00:00 CET | 1 | 1 | 911.43 | 17.66
19/02/2003 00:00:00 CET | 1 | 2 | 911.43 | 9.23
...

where I 'id_punto' can take the values from 1 to 6, and muestra is 1 or 2.
Commonly, for each 'date' and 'id_punto' we have two samples (muestra), and
the 'flow' is also commonly the same for the two samples.

The case is that for every 'date' I want to have the 'time series' of flows
in the way:
date 1 2 3 4 6
03/10/2002 606.92 323.08 440.69 393.61 5.49
17/10/2002 348.19 400.32 319.33 211.26 2.53
29/10/2002 411.57 409.66 310.37 362.20 9.36
14/11/2002 446.96 373.72 302.79 348.69 5.18
26/11/2002 381.20 386.85 307.77 240.75 6.41
12/12/2002 416.84 307.03 317.94 249.36 5.64
26/12/2002 743.04 380.30 539.94 521.60 3.69
09/01/2003 403.07 301.16 370.30 379.41 3.69
...

where the names of the rows are the values in 'id_punto' and the values of
the data are the average (for every 'id_punto' and 'date') between the two
samples (muestra).

In MSaccess this is solve with:
TRANSFORM avg(muestras_rambla.flow) AS avg_flow
SELECT muestras_rambla.date
FROM muestras_rambla
GROUP BY muestras_rambla.date
PIVOT muestras_rambla.id_punto;

All I can do in postgres is :
--------
SELECT muestras_rambla.fecha, id_punto, avg(caudal) AS avg_caudal
FROM muestras_rambla
GROUP BY fecha, id_punto
--------
to get:
fecha | id_punto | avg_caudal
--------------------------+----------+------------------
03/10/2002 00:00:00 CEST | 1 | 606.924
03/10/2002 00:00:00 CEST | 2 | 323.079
03/10/2002 00:00:00 CEST | 3 | 440.690
...
But, how could manage this to get the 'time series' table?

Thanks for your help and best wishes

Javier

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

How about:
SELECT
date,
(SELECT s1.flow FROM muestras_rambla s1 WHERE s1.date=t1.date AND
s1.id_punto=1) AS flow_1,
(SELECT s2.flow FROM muestras_rambla s2 WHERE s2.date=t1.date AND
s1.id_punto=2) AS flow_2,
(SELECT s3.flow FROM muestras_rambla s3 WHERE s3.date=t1.date AND
s1.id_punto=3) AS flow_3,
(SELECT s4.flow FROM muestras_rambla s4 WHERE s4.date=t1.date AND
s1.id_punto=4) AS flow_4,
(SELECT s6.flow FROM muestras_rambla s6 WHERE s6.date=t1.date AND
s1.id_punto=6) AS flow_6
FROM
muestras_rambla t1
GROUP BY
t1.date;
This uses sub-queries in the Select clause to get the data in columns.
There may be another way.

Nick Barr

[Nick Barr]

Nick Barr wrote:

javier garcia - CEBAS wrote:

Hi;
I've got problems with a query. I'm not sure if it is possible to do
this with Postgres, although I think it should be.

I had resolved these kind of queryes in MSAccess, where they are
called "cross references querys" but I can't find my way in Postgres.

My table 'muestras_rambla' is like:
date | id_punto | muestra | flow | n_nitrato ...
--------------------------+----------+---------+---------+-----------
06/02/2003 00:00:00 CET | 1 | 1 | 699.462 | 18.44
06/02/2003 00:00:00 CET | 1 | 2 | 699.462 | 13.79
06/02/2003 00:00:00 CET | 2 | 1 | 341.05 | 11.15
06/02/2003 00:00:00 CET | 2 | 2 | 341.05 | 17.3
06/02/2003 00:00:00 CET | 3 | 1 | 514.05 | 22.17
06/02/2003 00:00:00 CET | 3 | 2 | 514.05 | 16.62
06/02/2003 00:00:00 CET | 4 | 1 | 466.884 | 18.99
06/02/2003 00:00:00 CET | 4 | 2 | 466.884 | 19.02
06/02/2003 00:00:00 CET | 6 | 1 | 30.012 | 5.91
06/02/2003 00:00:00 CET | 6 | 2 | 30.012 | 4.15
19/02/2003 00:00:00 CET | 1 | 1 | 911.43 | 17.66
19/02/2003 00:00:00 CET | 1 | 2 | 911.43 | 9.23
...

where I 'id_punto' can take the values from 1 to 6, and muestra is 1
or 2. Commonly, for each 'date' and 'id_punto' we have two samples
(muestra), and the 'flow' is also commonly the same for the two samples.

The case is that for every 'date' I want to have the 'time series' of
flows in the way:
date 1 2 3 4 6
03/10/2002 606.92 323.08 440.69 393.61 5.49
17/10/2002 348.19 400.32 319.33 211.26 2.53
29/10/2002 411.57 409.66 310.37 362.20 9.36
14/11/2002 446.96 373.72 302.79 348.69 5.18
26/11/2002 381.20 386.85 307.77 240.75 6.41
12/12/2002 416.84 307.03 317.94 249.36 5.64
26/12/2002 743.04 380.30 539.94 521.60 3.69
09/01/2003 403.07 301.16 370.30 379.41 3.69
...

where the names of the rows are the values in 'id_punto' and the
values of the data are the average (for every 'id_punto' and 'date')
between the two samples (muestra).

In MSaccess this is solve with:
TRANSFORM avg(muestras_rambla.flow) AS avg_flow
SELECT muestras_rambla.date
FROM muestras_rambla
GROUP BY muestras_rambla.date
PIVOT muestras_rambla.id_punto;

All I can do in postgres is :
--------
SELECT muestras_rambla.fecha, id_punto, avg(caudal) AS avg_caudal
FROM muestras_rambla
GROUP BY fecha, id_punto
--------
to get:
fecha | id_punto | avg_caudal
--------------------------+----------+------------------
03/10/2002 00:00:00 CEST | 1 | 606.924
03/10/2002 00:00:00 CEST | 2 | 323.079
03/10/2002 00:00:00 CEST | 3 | 440.690
...
But, how could manage this to get the 'time series' table?

Thanks for your help and best wishes

Javier

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

How about:
SELECT
date,
(SELECT s1.flow FROM muestras_rambla s1 WHERE s1.date=t1.date AND
s1.id_punto=1) AS flow_1,
(SELECT s2.flow FROM muestras_rambla s2 WHERE s2.date=t1.date AND
s1.id_punto=2) AS flow_2,
(SELECT s3.flow FROM muestras_rambla s3 WHERE s3.date=t1.date AND
s1.id_punto=3) AS flow_3,
(SELECT s4.flow FROM muestras_rambla s4 WHERE s4.date=t1.date AND
s1.id_punto=4) AS flow_4,
(SELECT s6.flow FROM muestras_rambla s6 WHERE s6.date=t1.date AND
s1.id_punto=6) AS flow_6
FROM
muestras_rambla t1
GROUP BY
t1.date;
This uses sub-queries in the Select clause to get the data in columns.
There may be another way.

Nick Barr

Ooops, having tested that query I found a typo (or four). Try:

SELECT
date,
(SELECT s1.flow FROM muestras_rambla s1 WHERE s1.date=t1.date AND
s1.id_punto=1) AS flow_1,
(SELECT s2.flow FROM muestras_rambla s2 WHERE s2.date=t1.date AND
s2.id_punto=2) AS flow_2,
(SELECT s3.flow FROM muestras_rambla s3 WHERE s3.date=t1.date AND
s3.id_punto=3) AS flow_3,
(SELECT s4.flow FROM muestras_rambla s4 WHERE s4.date=t1.date AND
s4.id_punto=4) AS flow_4,
(SELECT s6.flow FROM muestras_rambla s6 WHERE s6.date=t1.date AND
s6.id_punto=6) AS flow_6
FROM
muestras_rambla t1
GROUP BY
t1.date;

Nick Barr

[Joe Conway]

Nick Barr wrote:

called "cross references querys" but I can't find my way in Postgres.

My table 'muestras_rambla' is like:
date | id_punto | muestra | flow | n_nitrato ...
--------------------------+----------+---------+---------+-----------
06/02/2003 00:00:00 CET | 1 | 1 | 699.462 | 18.44
06/02/2003 00:00:00 CET | 1 | 2 | 699.462 | 13.79
06/02/2003 00:00:00 CET | 2 | 1 | 341.05 | 11.15
06/02/2003 00:00:00 CET | 2 | 2 | 341.05 | 17.3
06/02/2003 00:00:00 CET | 3 | 1 | 514.05 | 22.17
06/02/2003 00:00:00 CET | 3 | 2 | 514.05 | 16.62
06/02/2003 00:00:00 CET | 4 | 1 | 466.884 | 18.99
06/02/2003 00:00:00 CET | 4 | 2 | 466.884 | 19.02
06/02/2003 00:00:00 CET | 6 | 1 | 30.012 | 5.91
06/02/2003 00:00:00 CET | 6 | 2 | 30.012 | 4.15
19/02/2003 00:00:00 CET | 1 | 1 | 911.43 | 17.66
19/02/2003 00:00:00 CET | 1 | 2 | 911.43 | 9.23
...

where I 'id_punto' can take the values from 1 to 6, and muestra is 1
or 2. Commonly, for each 'date' and 'id_punto' we have two samples
(muestra), and the 'flow' is also commonly the same for the two samples.

The case is that for every 'date' I want to have the 'time series' of
flows in the way:
date 1 2 3 4 6
03/10/2002 606.92 323.08 440.69 393.61 5.49
17/10/2002 348.19 400.32 319.33 211.26 2.53

See the crosstab function in contrib/tablefunc. Here's what is looks
like given the above data:

select * from crosstab(
'select thedate, id_punto, avg(flow) from muestras_rambla
group by thedate, id_punto order by 1,2',
'select distinct id_punto from muestras_rambla
order by 1'
) as (thedate timestamp with time zone,
c1 float8, c2 float8, c3 float8, c4 float8, c6 float8);

thedate | c1 | c2 | c3 | c4 | c6
------------------------+---------+--------+--------+---------+--------
2003-02-05 15:00:00-08 | 699.462 | 341.05 | 514.05 | 466.884 | 30.012
2003-02-18 15:00:00-08 | 911.43 | | | |
(2 rows)

This form of crosstab() requires 7.4RC1 or a 7.3 backpatched copy from here:
http://www.joeconway.com/

HTH,

Joe
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly