
Zitat von
bierstuegbl
Hallo zusammen,
ich wurde heute mit einem Problem konfrontiert, dass mir so bisher eigentlich nicht untergekommen ist :-)
Ich soll Abfrageergebnisse als csv-Datei liefern, die dann in Excel geladen werden und per Makro in Diagramme umgewandelt werden. Die Makros stehen schon und sollen erstmal nicht verändert werden, das heißt das CSV-Format ist mir genau vorgegeben. Leider erwarten diese Makros Ergebnisstabellen, bei denen die Ergebnisse nicht nur Zeilenweise sondern auch Spaltenweise dynamisch sind.
Ich bin mir nicht sicher, ob so ein Ansatz mit einer Datenbankanfrage überhaupt möglich ist, mir fällt zumindest im Moment nix Gescheites dazu ein. Zur Klärung hier mal ein Beispiel:
Vorweg: vielleicht hab ich Dich zumindest partiell falsch verstanden. Ich hab im folgenden auch auf Details wie CSV verzichtet, dafür aber es so gemacht, daß man Ergebnistabellen hat. Diese kann man ja trivial via COPY als CSV raushauen.
Nehmen wir an, wir haben eine Tabelle in der für alle Bauteile eines bestimmten Typs (z.B. Typ Stoßstange) Fehler beim Produktionsvorgang abgespeichert werden. Dafür haben wir eine Tabelle, in der alle aufgetretenen Fehler verzeichent sind, und zwar bezüglich eines Bauteils (für jede konkrete Stoßstange, die per id gezeichnet ist) und der Stelle, an der der Fehler auftrat (die Naht). Sie könnte z.B. so aussehen:
Hier mal meine Tabellen:
Code:
test=*# \d bauteile
Table "fehler.bauteile"
Column | Type | Modifiers
--------+---------+-------------------------------------------------------
id | integer | not null default nextval('bauteile_id_seq'::regclass)
name | text |
Indexes:
"bauteile_pkey" PRIMARY KEY, btree (id)
test=*# \d fehlerorte
Table "fehler.fehlerorte"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
id | integer | not null default nextval('fehlerorte_id_seq'::regclass)
ort | text |
Indexes:
"fehlerorte_pkey" PRIMARY KEY, btree (id)
test=*# \d bauteil_fehlerorte
Table "fehler.bauteil_fehlerorte"
Column | Type | Modifiers
--------------+---------+-----------
bauteil_id | integer |
fehlerort_id | integer |
Indexes:
"idx_bauteil_fehlerorte" UNIQUE, btree (bauteil_id, fehlerort_id)
Foreign-key constraints:
"bauteil_fehlerorte_bauteil_id_fkey" FOREIGN KEY (bauteil_id) REFERENCES bauteile(id)
"bauteil_fehlerorte_fehlerort_id_fkey" FOREIGN KEY (fehlerort_id) REFERENCES fehlerorte(id)
test=*# \d fehler
Table "fehler.fehler"
Column | Type | Modifiers
------------+---------+-----------
bauteil_id | integer |
ort_id | integer |
fehler | integer |
Foreign-key constraints:
"fehler_bauteil_id_fkey" FOREIGN KEY (bauteil_id, ort_id) REFERENCES bauteil_fehlerorte(bauteil_id, fehlerort_id) Die tablen enthalten:
Code:
test=*# select * from fehler;
bauteil_id | ort_id | fehler
------------+--------+--------
1 | 1 | 5
1 | 1 | 3
1 | 2 | 5
1 | 2 | 7
2 | 1 | 4
2 | 2 | 6
2 | 2 | 7
3 | 2 | 3
(8 rows)
test=*# select * from bauteile;
id | name
----+-----------
1 | Bauteil 1
2 | Bauteil 2
3 | Bauteil 3
(3 rows)
test=*# select * from fehlerorte;
id | ort
----+--------
1 | Naht 1
2 | Naht 2
3 | Naht 3
(3 rows)
test=*# select * from bauteil_fehlerorte ;
bauteil_id | fehlerort_id
------------+--------------
1 | 1
1 | 2
1 | 3
2 | 3
3 | 2
3 | 3
2 | 1
2 | 2
(8 rows)
Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:
Ich hab mal eine Funktion geschrieben, für ein bestimmtes Bauteil die Summe aller Fehler für dieses Bauteil gültigen Fehlerorte ausgibt:
Code:
create or replace function fehler(PRODUKT int) returns setof text as $$
declare
r1 record;
r2 record;
sql text;
g text;
begin
sql = 'select ' || $1 || ' as produkt';
g = '';
for r1 in select o.id, o.ort from fehlerorte o left join bauteil_fehlerorte b on b.fehlerort_id=o.id where b.bauteil_id = $1 order by 1 l
return next r1.ort;
sql = sql || ', sum(case when ort_id = ' || r1.id || ' then 1 else 0 end) as "' || r1.ort || '" ';
end loop;
sql = sql || 'from fehler where bauteil_id = ' || $1 || ' group by produkt;';
raise notice '%', sql;
execute sql;
end;
$$ language plpgsql; Das ist so noch nicht fertig. Es baut einen String mit einem SQL-Befehl auf, den es als letztes 'raise notice' in voller Länge ausgibt (und dann auch ausführt, aber ohne ein Resultat zu vermelden) Du kannst das in psql probieren und mit C&P mal ausführen. Es erzeugt, aufgerufen mit der ID eines Bauteiles, dynamisch ein SQL, welches eine Table mit den Orten, wo Fehler auftreten können als Spaltennamen zurück.
Mit Testdaten:
Code:
test=*# select * from fehler(2);
NOTICE: select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1"
NOTICE: select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2"
NOTICE: select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3"
NOTICE: select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3" from fehler where bauteil_id = 2 group by produkt;
fehler
--------
Naht 1
Naht 2
Naht 3
(3 rows)
-- nun via C&P
test=*# create table prod2 as select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3" from fehler where bauteil_id = 2 group by produkt;
test=*# select * from prod2 ;
produkt | Naht 1 | Naht 2 | Naht 3
---------+--------+--------+--------
2 | 1 | 2 | 0
(1 row) Als 'Bonus' siehst Du sogar die Anzahl der Fehler pro Fehlerstelle.
Die Funktion liefert auf alle Fälle unterschiedliche Tabellen, je nachdem, was definert wurde, und benennt auch die Spalten nach den Fehlerquellen.
Andreas
Lesezeichen