Summe der Werte eines generated column aus der union mehrerer SELECT-Abfragen berechnen?

Hallo allerseits,

mich würde interessieren, ob es möglich ist, in PostgreSQL die Summe eines generated columns zu berechnen, der durch eine UNION von 3 verschiedenen SELECT-Befehlen zustande gekommen ist. Genauer sieht die Situation wie folgt aus:

Die 3 SELECT-Abfragen suchen aus derselben Tabelle (gears g) Daten nach unterschiedlichen Kriterien heraus.
Neben der Spalte g.machine_id soll ein generated column mit einer Aggregationsfunktion (SUM()) angezeigt werden.
Die Werte sollen dabei nach g.machine_id gruppiert werden.
Die aggregated columns der 3 SELECT-Abfragen werden mit “workload_one/two/three” betitelt.
Als Ergebnis erhält man eine Tabelle mit zwei Spalten. Eine Spalte listet die herausgefilterten machine_ids auf, und die andere Spalte listet die Ergebnisse der einzelnen SELECT-Abfragen auf. Soweit alles gut. Es ist zu sehen, dass machine_id “1” zwei mal in der Spalte “machine_id” auftaucht.

Die Frage ist nun, ob man die Werte des generated column “workload_one”, den man unten im Output-Fenster sieht, auch nochmal summieren und auf die einzelnen machine_ids gruppieren kann, sodass jede machine_id nur einmal vorkommt, und die Summe der Ergebnisse aller SELECT-Abfragen für diese machine_id enthält.

Über eine zeitnahe Antwort würde ich mich sehr freuen

Infinarius

Code als Screenshot ist eine wirklich schlechte Idee

ich schließe mich @castorp an.

Vielleicht hilft das folgende, ich gebe mir jetzt nicht die Mühe Deine Bilder zu parsen…

postgres=# create table foo (id int generated always as identity primary key, val int);
CREATE TABLE
postgres=# insert into foo (val) select * from generate_series(1,10) s;
INSERT 0 10
postgres=# select sum(val) from foo where id < 5 union select sum(val) from foo where id > 5;
 sum 
-----
  10
  40
(2 rows)

postgres=# with x as (select sum(val) from foo where id < 5 union select sum(val) from foo where id > 5) select sum(sum) from x;
 sum 
-----
  50
(1 row)

postgres=# 

Tut mir leid. Hier:

SELECT g.machine_id, (SUM(EXTRACT(EPOCH FROM g.end_time) - EXTRACT(EPOCH FROM g.start_time))/3600/720)*100 AS workload_one
FROM gears g
WHERE g.start_time > ‘2022-11-01 00:00:00’ AND g.end_time < ‘2022-12-01 00:00:00’
GROUP BY g.machine_id
UNION
SELECT g.machine_id, (SUM(EXTRACT(EPOCH FROM g.end_time) - EXTRACT(EPOCH FROM timestamptz ‘2022-11-01 00:00:00’))/3600/720)*100 AS workload_two
FROM gears g
WHERE g.start_time < ‘2022-11-01 00:00:00’ AND g.end_time > ‘2022-11-01 00:00:00’ AND g.end_time < ‘2022-12-01 00:00:00’
GROUP BY g.machine_id
UNION
SELECT g.machine_id, (SUM(EXTRACT(EPOCH FROM timestamptz ‘2022-12-01 00:00:00’) - EXTRACT(EPOCH FROM g.start_time))/3600/720)*100 AS workload_three
FROM gears g
WHERE g.end_time > ‘2022-12-01 00:00:00’ AND g.start_time > ‘2022-11-01 00:00:00’ AND g.start_time < ‘2022-12-01 00:00:00’
GROUP BY g.machine_id;

Danke schonmal für die Antwort!

Du kannst das gesamte UNION in eine sog. derived Table packen. Der Spaltenalias des Gesamtresultats wird übrigens durch die Spalten der ersten Abfrage der UNION definiert. Der Alias as workload_two hat also überhaupt keinen Effekt.

Das Umrechnen in Tage(?) kann man dann auch nur einmal am Ende machen:

select machine_id, 
       sum(workload) as workoad, 
       extract(epoch from sum(workload)/3600/720)*100 as workload_adjusted
from (
  SELECT g.machine_id, sum(g.end_time - g.start_time) as workload -- nur dieser Alias ist relevant!
  FROM gears g
  WHERE g.start_time > '2022-11-01 00:00:00' AND g.end_time < '2022-12-01 00:00:00'
  GROUP BY g.machine_id
  UNION
  SELECT g.machine_id, SUM(g.end_time - timestamptz '2022-11-01 00:00:00')
  FROM gears g
  WHERE g.start_time < '2022-11-01 00:00:00' AND g.end_time > '2022-11-01 00:00:00' AND g.end_time < '2022-12-01 00:00:00'
  GROUP BY g.machine_id
  UNION
  SELECT g.machine_id, SUM(timestamptz '2022-12-01 00:00:00' - g.start_time)
  FROM gears g
  WHERE g.end_time > '2022-12-01 00:00:00' AND g.start_time > '2022-11-01 00:00:00' AND g.start_time < '2022-12-01 00:00:00'
  GROUP BY g.machine_id
) t
group by machine_id
1 „Gefällt mir“

Ich habe eine Weile zum Nachvollziehen gebraucht, aber habe es nun verstanden, und es funktioniert genau so wie beabsichtigt. Vielen Dank für Hilfe! :slight_smile:

Vielen Dank für die Hilfestellung. Ich habe die Abfragen noch nicht ganz nachvollziehen können, aber habe bereits eine hinreichende Antwort erhalten. :slight_smile: