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
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=#
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;
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