Dynamisches Generieren von Spalten in PostgreSQL
Ich habe gesehen, dass es ein paar ähnliche Fragen wie diese gibt, aber ich habe nicht verstanden, wie ich es selbst codieren kann. Bitte denken Sie daran, dass ich in diesem Bereich nur ein Anfänger bin.
rundsätzlich möchte ich den Tisch so schwenken:
zoom | day | point zoom | 2015-10-01 | 2015-10-02 | ......
------+-----------+------- ---> ------+------------+-------------+
1 | 2015-10-01 | 201 1 | 201 | 685 |
2 | 2015-10-01 | 43 2 | 43 | 346 |
3 | 2015-10-01 | 80 3 | 80 | 534 |
4 | 2015-10-01 | 324 4 | 324 | 786 |
5 | 2015-10-01 | 25 5 | 25 | 685 |
1 | 2015-10-02 | 685
2 | 2015-10-02 | 346
3 | 2015-10-02 | 534
4 | 2015-10-02 | 555
5 | 2015-10-02 | 786
:
:
:
Zeit kann variieren.
Ergebnisse auf der linken Seite bekomme ich mit:
SELECT
zoom,
to_char(date_trunc('day', time), 'YYYY-MM-DD') AS day,
count(*) as point
FROM province
WHERE time >= '2015-05-01' AND time < '2015-06-01'
GROUP BY to_char(date_trunc('day', time), 'YYYY-MM-DD'), zoom;
Ich habe gelesen, dass es einige Probleme gibt, wenn ich @ benutcount
und auch dass es besser wäre wenn ich @ benutCASE
undGROUP BY
, aber ich habe keine Ahnung, wie manCASE
Dies
Crosstab
selbst unterstützt die dynamische Erstellung von Spaltennamen nicht, dies kann jedoch mit @ erreicht werdecrosstab_hash
, wenn ich es richtig verstanden habe.
Dies ist wahrscheinlich eine nette Lösung:http: //okbob.blogspot.ca/2008/08/using-cursors-for-generating-cross.htm aber ich bin festgefahren beim Versuch, es selbst zu programmieren.
Ich muss diese Art des Schwenkens ziemlich oft verwenden, daher würde ich jede Art von Hilfe und zusätzliche Erklärung dahinter zu schätzen wissen.
Edit1
Ich versuche herauszufinden, wie die Kreuztabelle mit Datumsangaben funktioniert, ohne dass derzeit dynamische Spaltennamen zurückgegeben werden. Ich werde später erklären, warum. Es wird auf die Hauptfrage eingegangen. Für dieses Beispiel verwende ich nur einen Zeitraum von 2 Daten.
Basiert auf @Erwin Brandstetter Antwort:
SELECT * FROM crosstab(
'SELECT zoom, day, point
FROM province
ORDER BY 1, 2'
, $VALUES ('2015-10-01'::date), ('2015-10-02')$)
AS ct (zoom text, day1 int, day2 int);
returned Ergebnisse sind:
zoom | day1 | day2 |
-----+------------+-------------+
1 | 201 | 685 |
2 | 43 | 346 |
3 | 80 | 534 |
4 | 324 | 786 |
Ich versuche dieses @ zu bekomm
zoom | 2015-10-01 | 2015-10-02 |
-----+------------+-------------+
1 | 201 | 685 |
2 | 43 | 346 |
3 | 80 | 534 |
4 | 324 | 786 |
aber meine Abfrage funktioniert nicht:
SELECT *
FROM crosstab(
'SELECT *
FROM province
ORDER BY 1,2')
AS ct (zoom text, "2015-10-01" date, "2015-10-02" date);
ERROR: return and sql tuple descriptions are incompatible
Edit1, Q1. Warum funktioniert das nicht und wie kann ich solche Ergebnisse zurückgeben?
Ich habe Links gelesen, die mir @Erwin Brandstetter zur Verfügung gestellt hat, insbesondere diese:Ausführen einer dynamischen Kreuztabellenabfrage. Ich habe seine Funktion kopiert / eingefügt:
CREATE OR REPLACE FUNCTION pivottab(_tbl regclass,
_row text, _cat text,
_expr text,
_type regtype)
RETURNS text AS
$func$
DECLARE
_cat_list text;
_col_list text;
BEGIN
-- generate categories for xtab param and col definition list
EXECUTE format(
$SELECT string_agg(quote_literal(x.cat), '), (')
, string_agg(quote_ident (x.cat), %L)
FROM (SELECT DISTINCT %I AS cat FROM %s ORDER BY 1) x$
, ' ' || _type || ', ', _cat, _tbl)
INTO _cat_list, _col_list;
-- generate query string
RETURN format(
'SELECT * FROM crosstab(
$q$SELECT %I, %I, %s
FROM %I
GROUP BY 1, 2
ORDER BY 1, 2$q$
, $c$VALUES (%5$s)$c$
) ct(%1$I text, %6$s %7$s)'
, _row, _cat, _expr, _tbl, _cat_list, _col_list, _type
);
END
$func$ LANGUAGE plpgsql;
und nenne es mit query
SELECT pivottab('province','zoom','day','point','date');
Function hat mich zurückgegeben:
pivottab
----------------------------------------------------------
SELECT * FROM crosstab( +
$q$SELECT zoom, day, point +
FROM province +
GROUP BY 1, 2 +
ORDER BY 1, 2$q$ +
, $c$VALUES ('2015-10-01'), ('2015-10-02')$c$ +
) ct(zoom text, "2015-10-01" date, "2015-10-02" date)
(1 row)
Also, als ich die Abfrage bearbeitet und hinzugefügt habe; (es wäre schön das; ist schon da) ich hab:
ERROR: column "province.point" must appear in the GROUP BY clause or be used in an aggregate function
Edit1, Q2. Irgendwelche Ideen, wie man das löst?
Edit1, Q3. Ich denke, die nächste Frage wird sein, wie die Funktion automatisch ausgeführt wird, das ebenfalls unter demselben Link erwähnt wird, aber bei vorherigen Schritten nicht weiter funktioniert hat.