Re: Abfrage mit Datum als Serie.

Thomas Drebert <drebert(at)web(dot)de> 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°