Re: Abfrage mit Datum als Serie.

Thomas Drebert <[email protected]> wrote:

> Vielleicht kann mir jemand helfen, wie ich das verbinden kann.


Einfaches Besipiel, Du hast:

,----
| test=*# select * from messung order by 1;
|      d      | val
| ------------+-----
|  2014-03-12 |   9
|  2014-03-13 |   5
|  2014-03-15 |   7
|  2014-03-16 |   2
|  2014-03-17 |   7
|  2014-03-17 |   5
|  2014-03-17 |  10
|  2014-03-17 |   6
|  2014-03-19 |   5
|  2014-03-21 |   6
| (10 rows)
`----

Auf die einzelnen Tage via generate_series():

,----
| test=*# select foo.d, messung.val from (select (current_date + s * '1day'::interval)::date as d from generate_Series(1,10)s) foo left join messung using(d) order by 1;
|      d      | val
| ------------+-----
|  2014-03-13 |   5
|  2014-03-14 |
|  2014-03-15 |   7
|  2014-03-16 |   2
|  2014-03-17 |   7
|  2014-03-17 |   5
|  2014-03-17 |  10
|  2014-03-17 |   6
|  2014-03-18 |
|  2014-03-19 |   5
|  2014-03-20 |
|  2014-03-21 |   6
|  2014-03-22 |
| (13 rows)
`----


Und nun aggregieren, z.B. avg():

,----
| test=*# select foo.d, avg(messung.val) from (select (current_date + s * '1day'::interval)::date as d from generate_Series(1,10)s) foo left join messung using(d) group by 1 order by 1;
|      d      |        avg
| ------------+--------------------
|  2014-03-13 | 5.0000000000000000
|  2014-03-14 |
|  2014-03-15 | 7.0000000000000000
|  2014-03-16 | 2.0000000000000000
|  2014-03-17 | 7.0000000000000000
|  2014-03-18 |
|  2014-03-19 | 5.0000000000000000
|  2014-03-20 |
|  2014-03-21 | 6.0000000000000000
|  2014-03-22 |
| (10 rows)
`----


Du baust Dir halt via :

,----
| test=*# select (current_date + s * '1day'::interval)::date as d from generate_Series(1,10)s;
|      d
| ------------
|  2014-03-13
|  2014-03-14
|  2014-03-15
|  2014-03-16
|  2014-03-17
|  2014-03-18
|  2014-03-19
|  2014-03-20
|  2014-03-21
|  2014-03-22
| (10 rows)
`----

eine Hilfstabelle, die ALLE Tagen enthält, und nutzt das als Basis für die weitere Arbeit.

Mit generate_series() warst Du ja schon auf dem richtigen Weg ;-)
Viel Erfolg noch!


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


-- 
Sent via pgsql-de-allgemein mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein