Re: Wert im Zeitverlauf darstellen für z.B. die letzen 5 Tage

Thomas Uzunoff <postgresql(at)grizzlycrm(dot)de> writes:

> Hallo,
>
> ich habe ein Problem, was ich momentan nicht gelöst bekomme. Ich habe
> eine solche Tabelle:
>
> ID |Name |Datum |Platz
> ----------------------------
> 1 |Gerhard|14.1.2015| 1
> 2 |Helmut |14.1.2015| 2
> 3 |Angela |14.1.2015| 3
> 4 |Gerhard|13.1.2015| 2
> 5 |Helmut |13.1.2015| 1
> 6 |Angela |13.1.2015| 3
> 7 |Gerhard|12.1.2015| 1
> 8 |Helmut |12.1.2015| 2
> 9 |Gerhard|11.1.2015| 2
> 10 |Angela |11.1.2015| 1
> 11 |Gerhard|10.1.2015| 2
> 12 |Angela |10.1.2015| 1
>
> Das gewünschte Ergebnis ist:
>
> Name |14.1.2015|13.1.2015|12.1.2015|11.1.2015|10.1.2015|
> -----------------------------------------------------------
> Gerhard | 1 | 2 | 1 | 2 | 2 |
> Helmut | 2 | 1 | 2 | | |
> Angela | 3 | 3 | | 1 | 1 |
>
> Hierbei wird an jedem Tag der Platz für alle anwesenden Personen
> festgestellt und über den Zeitverlauf dargestellt. Wenn eine Person an
> einem Tag nicht anwesend war, soll die Zelle leer oder z.B. mit einem
> x oder einer 0 markiert werden. Momentan habe ich das Problem, dass
> meine Abfrage mir zwar die Personen mit den Tagen schon anzeigt,
> leider werden die leeren Felder wie z.B. 11.1.2015-Angelas Platz 1 auf
> den 12.1.2015 verschoben. Die Spalte am 12.1.2015 sieht dann
> fälschlicher weise so aus:
>
> |12.1.2015|
> | 1 |
> | 2 |
> | 1 |
>
> Gerne auch ohne das Datum als Spaltenname. Geht das mit Postgres
> irgendwie? Wenn ja, wie?

Das geht mit der tablefunc-Erweiterung:

CREATE EXTENSION tablefunc

Dann kann man die crosstab-Funktion verwenden:

SELECT *
FROM crosstab (
'SELECT name, datum, platz FROM mytable ORDER BY name, datum',
'SELECT DISTINCT datum FROM mytable ORDER BY datum DESC'
) AS (
name text,
"14.01.2015" int,
"13.01.2015" int,
"12.01.2015" int,
"11.01.2015" int,
"10.01.2015" int
)
ORDER BY 2;

Allerdings willst Du wahrscheinlich die Spaltennamen nicht von Hand
eintippen, sondern dynamisch über sowas wie

SELECT x::date AS datum
FROM generate_series((SELECT min(datum) FROM mytable),
(SELECT max(datum) FROM mytable),
INTERVAL '1 day') AS g(x)
ORDER BY x DESC

erzeugen lassen, und dann wird es kompliziert, weil man dazu PL/pgSQL braucht:

DO $$BEGIN
EXECUTE $q$
CREATE TEMP TABLE txx AS
SELECT *
FROM crosstab (
'SELECT name, datum, platz FROM mytable ORDER BY name, datum',
'SELECT DISTINCT datum FROM mytable ORDER BY datum DESC'
) AS (
name text,$q$ ||
(SELECT string_agg('"' || datum::text || '" int', ',')
FROM (
SELECT x::date AS datum
FROM generate_series((SELECT min(datum) FROM mytable),
(SELECT max(datum) FROM mytable),
INTERVAL '1 day') AS g(x)
ORDER BY x DESC
) AS g) ||
$q$)
ORDER BY 2$q$;
END$$;

SELECT * FROM txx;