verschachtelte SQL Abfrage

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
Hanno
Benutzer
Beiträge: 72
Registriert: Do 21. Jan 2010, 15:19

verschachtelte SQL Abfrage

Beitrag von Hanno »

Hallo Forum,

Ich habe heute mal wieder eine Frage bei der ich irgendwie zu keiner Lösung komme. ich habe jetzt gehofft, dass mir hier eventuell jemand weiterhelfen könnte?

Ich habe ZWEI Tabellen nach folgendem Schema:

CREATE TABLE public.ind_38 (
area_id integer NOT NULL,
"timestamp" date NOT NULL,
"value" double precision,
interpolated boolean NOT NULL DEFAULT false,
calculated boolean NOT NULL DEFAULT false,
/* Keys */
CONSTRAINT ind_38_pkey
PRIMARY KEY (area_id, "timestamp"),
/* Foreign keys */
CONSTRAINT ind_38_area_id_foreign
FOREIGN KEY (area_id)
REFERENCES public."area"(id)
ON DELETE CASCADE
)

Dort sind einer Gemeinde (area_id) immer Datenwerte einem Datum zugeordnet. Also
Gemeinde1 | 13.01.2017 | 10 | true | true
Gemeinde2 | 13.01.2017 | 12 | true | true
Gemeinde1 | 01.01.2016 | 11 | true | true
Gemeinde2 | 01.01.2016 | 15 | true | true

Ich habe hierbei jetzt wie gesagt zwei unterschiedliche Tabellen, die aber gleich aufgebaut sind. ich möchte jetzt die Daten aus Tabelle 1 mit den Daten aus Tabelle 2 verrechnen (Tabelle1 Minus Tabelle2)
Hierbei sollen aber jeweils nur die Daten derselben Gemeinde miteinander Subtrahiert werden. Ausserdem sollen nur die Daten aus demselben Jahr genommen werden, wobei nur das Jahr berücksichtigt wird. Wenn also in Tabelle1 steht: Gemeinde1 | 13.01.2017 | 10 | true | true und in Tabelle zwei steht: Gemeinde1 | 25.05.2017 | 12 | true | true dann soll nur das Jahr 2017 berücksichtigt werden. Tag und Monat ist egal. Es soll dabei also 10-12 gerechnet werden. Wenn es in der Tabelle für die jeweilige Gemeinde mehrere Einträge für ein Jahr gibt, dann soll immer das neueste/jüngste Datum genommen werden. Es gibt also immer nur einen Datenwert pro Gemeinde pro Jahr auch wenn die Ausgangstabellen mehrere Tage in einem Jahr haben. Wenn es keine identischen Jahre gibt, dann wird auch nichts berechnet. Leider bekomme ich jetzt die SQL Select Abfrage irgendwie nicht hin. Ich hoffe jemand kann mir vielleicht dabei helfen.
ich habe folgendes Konstrukt versucht. Dies ist natürlich nicht korrekt, weil das MAX ja immer nur ein Jahr selektiert statt alle vorhandenen, aber eventuell hilft es trotzdem?

SELECT
ind_38."timestamp",
(ind_38.value - ind_40.value) AS value,
ind_38.area_id,
(ind_38.interpolated OR ind_40.interpolated) AS interpolated,
(ind_38.calculated OR ind_40.calculated) AS calculated
FROM ind_38, ind_40
WHERE
(
(EXTRACT (YEAR FROM ind_38."timestamp") = EXTRACT (YEAR FROM ind_40."timestamp")) AND
(ind_38.area_id = ind_40.area_id) AND
(ind_38."timestamp" IN (SELECT max(ind_38."timestamp") FROM ind_38)) AND
(ind_40."timestamp" IN (SELECT max(ind_40."timestamp") FROM ind_40))
)
ORDER BY
ind_38."timestamp"

Für eure Hilfe bedanke ich mich wie immer recht herzlich im Voraus.
LG
Hanno

akretschmer
Erfahrener Benutzer
Beiträge: 7714
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: verschachtelte SQL Abfrage

Beitrag von akretschmer »

Dein FROM table1,table2 schreibe lieber zu einem explizieten JOIN um, Du solltest auch vermeiden, reservierte Wörter wie TIMESTAMP und VALUE als Spaltenbezeichner zu verwenden - das würde die " vermeiden.

Ich denke Du suchst vereinfacht sowas wie:

Code: Alles auswählen

test=*# select * from hanno_1;
 id |   datum    | val 
----+------------+-----
  1 | 2017-01-13 |  10
  2 | 2017-01-12 |  12
  3 | 2017-01-14 |  15
(3 Zeilen)

test=*# select * from hanno_2;
 id |   datum    | val 
----+------------+-----
  1 | 2017-01-13 | 100
  2 | 2017-01-12 | 122
  3 | 2017-01-14 | 150
(3 Zeilen)

test=*# select h1.id, h1.datum, h1.val + h2.val from hanno_1 h1 inner join hanno_2 h2 on((h1.id,h1.datum)=(h2.id,h2.datum));
 id |   datum    | ?column? 
----+------------+----------
  1 | 2017-01-13 |      110
  2 | 2017-01-12 |      134
  3 | 2017-01-14 |      165
(3 Zeilen)
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hanno
Benutzer
Beiträge: 72
Registriert: Do 21. Jan 2010, 15:19

Re: verschachtelte SQL Abfrage

Beitrag von Hanno »

Hallo,
Danke für die schnelle Antwort und die gute Hinweise. Werde die Bezeichnungen noch anpassen.

SELECT
ind_38."timestamp",
(ind_38.value - ind_40.value) AS value,
ind_38.area_id
FROM ind_38
INNER JOIN ind_40
ON ((ind_38.area_id,EXTRACT (YEAR FROM ind_38."timestamp"))=(ind_40.area_id,EXTRACT (YEAR FROM ind_40."timestamp")))

Wenn ich jetzt aber das SQL mit einem JOIN umsetze, dann habe ich trotzdem noch das Problem, dass ich immer alle Tage zurückbekomme. Ich möchte die Ausgabe aber begrenzen auf den jüngsten Tag pro Jahr. d.h. bei Ausgangsdaten wie folgt:
area_id | datum | val
----+------------+-----
1 | 2017-01-13 | 10
1 | 2017-02-12 | 11
1 | 2017-03-14 | 12
1 | 2016-01-13 | 50
1 | 2016-01-12 | 52
1 | 2015-01-14 | 55

area_id | datum | val
----+------------+-----
1 | 2017-01-13 | 10
1 | 2017-01-12 | 12
1 | 2017-01-14 | 15
1 | 2016-01-13 | 10
1 | 2016-01-12 | 12
1 | 2016-01-14 | 15

möchte ich etwas haben wie hier:
id | datum | val
----+------------+-----
1 | 2017-01-14 | 27 (A+B: aus Tabelle A jüngster Zeitstand für Jahr 2017=12 aus Tabelle B jüngster Zeitstand für Jahr 2017= 15 12+15=27)
1 | 2016-01-12 | 65 (A+B: aus Tabelle A jüngster Zeitstand für Jahr 2016=50 aus Tabelle B jüngster Zeitstand für Jahr 2016= 15 50+15=65)

Jahr 2015 ist nur in Tabelle A vorhanden und nicht in Tabelle B und soll somit nicht berücksichtigt werden. Ich habe in der Zieltabelle dann für jedes Jahr eine Zeile pro Gemeinde.
Geht das irgendwie über SQL?

LG
Hanno

akretschmer
Erfahrener Benutzer
Beiträge: 7714
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: verschachtelte SQL Abfrage

Beitrag von akretschmer »

Nun, die Angabe des Datum im Resultat ist dann nicht wirklich nötig, vor allem, nicht exakt anzugeben.

Code: Alles auswählen

test=*# select * from hanno_1;
 id |   datum    | val 
----+------------+-----
  1 | 2017-01-13 |  10
  1 | 2017-02-12 |  11
  1 | 2016-01-13 |  50
  1 | 2016-01-12 |  52
  1 | 2017-03-14 |  12
  1 | 2015-01-14 |  55
(6 Zeilen)

test=*# select * from hanno_2;
 id |   datum    | val 
----+------------+-----
  1 | 2017-01-13 |  10
  1 | 2017-01-12 |  12
  1 | 2017-01-14 |  15
  1 | 2016-01-13 |  10
  1 | 2016-01-12 |  12
  1 | 2016-01-14 |  15
(6 Zeilen)

test=*# select h1.id, h1.jahr, h1.val + h2.val from (select id, extract(year from datum) as jahr, val from (select *, row_number() over (partition by extract(year from datum) order by datum desc) from hanno_1) x where row_number = 1) h1 inner join (select id, extract(year from datum) as jahr, val from (select *, row_number() over (partition by extract(year from datum) order by datum desc) from hanno_2) x where row_number = 1) h2 on((h1.id,h1.jahr)=(h2.id,h2.jahr));
 id | jahr | ?column? 
----+------+----------
  1 | 2016 |       65
  1 | 2017 |       27
(2 Zeilen)
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Antworten