eine neue Tabelle erstellen

[Papp Nase]

Hallo!

Ich möchte gerne eine neue Tabelle erstellen. Ich habe jetzt eine Rohdatentabelle mit vier Spalten:

Zeit; Geraet; Messwert1; Messwert2

Es gibt drei Geräte, die zum gleichen Zeitpunkt die Daten Messwert1 und Messwert2 liefern.

Ich möchte jetzt in einer neuen Tabelle die Daten neu zusammenfassen, damit ich diese Tabelle z.B. nach Excel importieren kann und dort mehere Spalten habe (genannt Zieltabelle):

Zeit; Messwert1_Geraet1; Messwert2_Geraet1;Messwert1_Geraet2; Messwert2_Geraet2; Messwert1_Geraet3; Messwert2_Geraet3;

Ich habe mir dazu folgene Vorgehensweise überlegt:

1. Erstellen von drei Hilfstabellen, in jede Hilfstabelle ist nur ein Gerät vorhanden:
Tabelle Hilf_Geraet1 mit den Spalten Zeit, Messwert1, Messwert2

Select * from MesswerteGeraete where 'Geraet' = 1

Das Ergebnis in die Tabelle Hilf_Geraet1 schreiben

Für Geraet2 und Geraet3 analog

2. Erstellen einer neuen Tabelle (Zieltabelle)
2.a. Die Daten aus Tabelle Hilf_Geraet1 in Zieltabelle schreiben, dabei Messwert1->Messwert1_Geraet1 und Messwert2->Messwert2_Geraet1 zuwesein.
2.b Jetzt die Daten aus Hilf_Geraet2 einfuegen. Hier gibt es nun identische Zeitstempel, an die identischen Zeitstempel die neuen Werte an den Spalten Messwert1->Messwert1_Geraet2 und Messwert2->Messwert2_Geraet2 anfuegen.
Analog für Geraet3

Ist diese Vorgehensweise so richtig oder gibt es eine bessere Variante?

 

[Hony%]

Wenn die Zeit wirklich immer identisch ist reicht in diesem Fall ein Join. Hier mal ein schneller Ansatz:

SELECT *
FROM table AS A
INNER JOIN table AS B
ON A.Zeit = B.Zeit
INNER JOIN table AS C
ON A.Zeit = C.Zeit
WHERE A.Geraet < B.Geraet
AND B.Gereat < C.Geraet

Gruß
Hony

 

[ukulele]

Sofern die 3 Geräte immer zur gleichen Zeit einen Messwert liefern. Wenn diese mal um eine Sekunde mal um mehrere Stunden im Zeitstempel abweichen wirds schwieriger weil dir dann dein Join-Kriterium flöten geht.

 

[Papp Nase]

Hi Horny,

vielen Dank für Deine Antwort, es funktioniert ganz gut.

Ich habe noch zwei Fragen:

1. Führe ich den Befehl aus, dann erhalte ich das Ergebnis in einer neuen Ansicht. Wie kann ich es erreichen, dass der PC das Ergebnis in einer neuen Tabelle speichert? Also auch generell, wenn ich ein SELECT * ausführe?

2. Ich habe jetzt dreimal die Zeit in der Liste, ich habe A.Zeit, B.Zeit und C.Zeit. B.Zeit und C.Zeit möchte ich aber nicht mehr haben, sondern ich möchte diesen beiden Spalten löschen.

Ich habe verschiedene Sachen ausprobiert, die aber nicht funktionierten. Ich habe an das Ende Deines Beispiels z.B. angefügt:

DROP COLUMN B.Zeit

oder

ALTER TABLE B DROP COLUMN ZeitstempelUTC

leider funktionierte das nicht.

 

[akretschmer]

1. Führe ich den Befehl aus, dann erhalte ich das Ergebnis in einer neuen Ansicht. Wie kann ich es erreichen, dass der PC das Ergebnis in einer neuen Tabelle speichert? Also auch generell, wenn ich ein SELECT * ausführe?

create table as select ... oder insert into ... select ... from ...

2. Ich habe jetzt dreimal die Zeit in der Liste, ich habe A.Zeit, B.Zeit und C.Zeit. B.Zeit und C.Zeit möchte ich aber nicht mehr haben, sondern ich möchte diesen beiden Spalten löschen.

Dann frage sie nicht erst mit ab.

Ich habe verschiedene Sachen ausprobiert, die aber nicht funktionierten. Ich habe an das Ende Deines Beispiels z.B. angefügt:

DROP COLUMN B.Zeit

oder

ALTER TABLE B DROP COLUMN ZeitstempelUTC

leider funktionierte das nicht.

Wirf mal 'funktioniert nicht' bei Google ein. Merkst Du es?

test=*# \d foo;
  Table "public.foo"
 Column |  Type  | Modifiers
--------+---------+-----------
 s  | integer |
 val  | integer |

test=*# alter table foo drop column val;
ALTER TABLE
Time: 52,051 ms
test=*# \d foo
  Table "public.foo"
 Column |  Type  | Modifiers
--------+---------+-----------
 s  | integer |

tut aber bei mir.

 

[Papp Nase]

Vielen Dank für Deine Antwort.

create table as select ... oder insert into ... select ... from ...

Das hatte ich schon vor meiner Frage ausprobiert:

CREATE newtable AS SELECT *
FROM table AS A
INNER JOIN table AS B
ON A.Zeit = B.Zeit
INNER JOIN table AS C
ON A.Zeit = C.Zeit
WHERE A.Geraet < B.Geraet
AND B.Gereat < C.Geraet

Da kommt aber eine Fehlermeldung.

Und das hatte ich auch schon vorher ausprobiert:

SELECT *
FROM table AS A
INNER JOIN table AS B
ON A.Zeit = B.Zeit
INNER JOIN table AS C
ON A.Zeit = C.Zeit
WHERE A.Geraet < B.Geraet
AND B.Gereat < C.Geraet

--- Hier wollte ich jetzt die beiden Spalten entfernen ---

DROP COLUMN B.Zeit
DROP COLUMN C.Zeit

geht aber nicht.

Und das hier ging auch nicht:

SELECT *
FROM table AS A
INNER JOIN table AS B
ON A.Zeit = B.Zeit
INNER JOIN table AS C
ON A.Zeit = C.Zeit
WHERE A.Geraet < B.Geraet
AND B.Gereat < C.Geraet

--- Hier wollte ich jetzt die beiden Spalten entfernen ---

ALTER TABLE B DROP COLUMN Zeit
ALTER TABLE C DROP COLUMN Zeit

dass klappte auch nicht.

 

[akretschmer]

Vielen Dank für Deine Antwort.

Da kommt aber eine Fehlermeldung.

geht aber nicht.

Und das hier ging auch nicht:

dass klappte auch nicht.

Die Fülle an detaillierten Fehlern erschlägt einen geradezu.

 

[Papp Nase]

Ja, der Fehler ist immer der gleiche - Fehler im SQL-Syntax. Leider weis ich nicht, was der Fehler sein soll. Es handelt sich immer um die Zeilen, die ich an das Beispiel im 2. Beitrag angefügt habe.

 

[akretschmer]

Ja, der Fehler ist immer der gleiche - Fehler im SQL-Syntax. Leider weis ich nicht, was der Fehler sein soll. Es handelt sich immer um die Zeilen, die ich an das Beispiel im 2. Beitrag angefügt habe.

Ja, aber es sind immer andere Fehler, vermutlich.

CREATE newtable AS

Fangen wir damit an. Create was?

Widmen wir uns

DROP COLUMN B.Zeit

Wo?

Kennst Du den Unterschied zwischen * und einer Aufzählung im Dunstkreis von SELECT?

 

[Papp Nase]

Ich habe jetzt mit diesem Beispiel aus Antwort 2 etwas herumprobiert:

SELECT *
FROM messungen AS A
INNER JOIN messungen AS B
ON A.zeit = B.zeit
INNER JOIN messungen AS C
ON A.zeit = C.zeit
WHERE A.geraet < B.geraet
AND B.geraet < C.geraet

Damit erhalte ich in der Ausgabe eine Tabelle mit 12 Spalten:

zeit, geraet, mw1, mw2, zeit, geraet, mw1, mw2, zeit, geraet, mw1, mw2

Füge ich an den Anfang des Beispiels vor dem SELECT * den Befehl zu:

CREATE TABLE neu SELECT *
.
.
.

gibt mir MySQL die Fehlermeldung, dass doppelte Tabelleneinträge vorhanden sind, was ja auch der Fall ist.

Also ist mein nächster Versuch erstmal, die Tabelleneinträge umzubenennen:

SELECT zeit, geraet as geraet_0, mw1 as mw1_0, mw2 as mw2_0
FROM messungen AS A

Dass geht ohne Fehler.

Zunächst ein einfaches Beispiel, was noch nicht klappte:

SELECT zeit, geraet as geraet_0, mw1 as mw1_0, mw2 as mw2_0
FROM messungen AS A
INNER JOIN messungen AS B
ON A.zeit = b.zeit

Fehlermeldung: Column time in field list is ambiguous - also zeit ist mehrfach vorhanden. Nun habe ich das zeit bei AS a auch umbenannt:

 SELECT zeit AS zeit_0, geraet as geraet_0, mw1 as mw1_0, mw2 as mw2_0
FROM messungen AS A
INNER JOIN messungen AS B
ON A.zeit_0 = b.zeit

Gleiche Fehlermeldung - Zeit ist doppelt vorhanden.

Jetzt habe ich versucht, die Spalten im INNER JOIN umzubenennen:

 SELECT zeit, geraet, mw1, mw2
FROM messungen AS A
INNER JOIN (SELECT zeit AS zeit1, geraet AS geraet1, mw1 AS mw11, mw2 AS mw21 FROM messungen WHERE geraet = 1) AS B
WHERE geraet = 0

Das hat zwar funktioniert, aber das Ergebnis ist etwas seltsam - ich hatte gehofft, dass ich jetzt wieder eine Tabelle in dieser Form erhalten würde:

zeit, geraet, mw1, mw1, zeit1, geraet1, mw11, mw21

Ich habe aber bekommen:
zeit, geraet, mw1, mw1

und sage und schreibe - die Tabelle hat nun über 105 Mio. Zeilenwerte. Ursprünglich hatte jedes der drei Geräte, die die Nummern 0,1 und 2 haben, etwa 10.000 Messwerte mw1 und mw2.

Jetzt habe ich nach dem INNER JOIN noch eine ON-Zeile angefügt, auch wie im urprünglichen Beispiel A.zeit = B.zeit1

SELECT zeit, geraet, mw1, mw2
FROM messungen AS A
INNER JOIN (SELECT zeit AS zeit1, geraet AS geraet1, mw1 AS mw11, mw2 AS mw21 FROM messungen WHERE geraet = 1) AS B
ON A.zeit = B.zeit1
WHERE geraet = 0

Ich erhalte nur wieder vier Spalten mit etwa 10.000 Zeilen.

Im Ziel möchte ich eigentlich Folgendes haben:

zeit, mw1_0, mw2_0, mw1_1, mw2_1, mw1_2, mw2_2

Zeit soll also nur einmal vorkommen, die Messgeraete mit geraet garnicht mehr und die Spalten mw1 und mw2 sollen zu den jeweilgen Messgeaeten den Appendix _0, _1 und _2 bekommen - also die Tabelle soll am Ende nur die 7 Spalten haben.

Und wenn ich diese Tabelle so hinbekommen würde, dann könnte ich auch am Anfang einfach das CREATE TABLE neu ... anfügen und der Server dürfte dann keine Fehlermeldung mehr herausgeben, dass doppelte Column-Namen vorhanden sind.

Meine Frage jetzt ist aber - damit es nicht zu kompliziert für mich wird - wie bekomme ich es hin, dass ich eine Tabelle mit 8 (12) Spalten bekomme, wo alle zwei (drei) Geräte nebeneinander vorkommen mit allen 4 Apalten, also die Tabelle so aussieht:

zeit_0, geraet_0, mw1_0, mw2_0, zeit_1, geraet_1, mw1_1, mw2_1, ...

Also die Tabellennamen vom INNER-JOIN sollen auch umbenannt werden.

 

[akretschmer]

Ich habe jetzt mit diesem Beispiel aus Antwort 2 etwas herumprobiert:

tl;dr

select a.col1, b.col2 from table1 a left join table2 b on ...

 

[Papp Nase]

Ich habe jetzt so eine Kombination ausprobiert, um eine mehrspaltige Tabelle in der Form zu erhalten, wie ich sie haben möchte:

DROP TABLE IF EXISTS excel_report;
CREATE TABLE excel_report
SELECT  zeit, mw1 AS mw1_0, mw2 AS mw2_0,
  (SELECT mw1 FROM messungen AS B WHERE B.geraet=1 AND A.zeit = B.zeit) AS mw1_1,
  (SELECT mw2 FROM messungen AS B WHERE B.geraet=1 AND A.zeit = B.zeit) AS mw2_1,
  (SELECT mw1 FROM messungen AS C WHERE C.geraet=2 AND A.zeit = C.zeit) AS mw1_2,
  (SELECT mw2 FROM messungen AS C WHERE C.geraet=2 AND A.zeit = C.zeit) AS mw2_2
FROM messungen AS A
WHERE geraet=0

Sie führt zum Ziel.

Gibt es eine "bessere" Möglichkeit, wie ich zu dem gleichen Ergebnis kommen könnte?

 

[akretschmer]

Ich habe jetzt so eine Kombination ausprobiert, um eine mehrspaltige Tabelle in der Form zu erhalten, wie ich sie haben möchte:


Sie führt zum Ziel.

Gibt es eine "bessere" Möglichkeit, wie ich zu dem gleichen Ergebnis kommen könnte?

Ja, Du brauchst nur die in der ersten genannten Anwort gezeigte Abfrage vom * zu befreien und statt dessen die gewünschetn Spalten mit einem eindeutigen Namen als Alias aufzuzählen.

Aus

test=*# select * from pappnase ;
 zeit | geraet | wert1 | wert2
------+--------+-------+-------
  1 |  1 |  10 |  12
  1 |  2 |  13 |  14
  1 |  3 |  8 |  7
  2 |  1 |  100 |  101
  2 |  2 |  110 |  111
  2 |  3 |  120 |  121
(6 rows)

wird dann

test=*# select p1.zeit, p1.geraet, p1.wert1 as mw11, p1.wert2 as mw12, p2.wert1 as mw21, p2.wert2 as mw22, p3.wert1 as mw31, p3.wert2 as mw32  from pappnase p1 inner join pappnase p2 on p1.zeit=p2.zeit inner join pappnase p3 on p1.zeit=p3.zeit where p1.geraet < p2.geraet and p2.geraet < p3.geraet;
 zeit | geraet | mw11 | mw12 | mw21 | mw22 | mw31 | mw32
------+--------+------+------+------+------+------+------
  1 |  1 |  10 |  12 |  13 |  14 |  8 |  7
  2 |  1 |  100 |  101 |  110 |  111 |  120 |  121
(2 rows)

 

[Papp Nase]

Danke für Deine Antwort, jetzt klappt das auch mit den Inner-Joins.

Ich habe eine Frage zu dem WHERE-Kriterium

In Deinem Beispiel steht:

WHERE p1.geraet < p2.geraet and p2.geraet < p3.geraet;

Ginge das auch damit?:

 WHERE p1.geraet = 0 AND p2.geraet = 1 AND p3.geraet=2;

Das scheint zwar das gleiche Ergebnis anzuzeigen, ich weis aber nicht, ob es dennoch Probleme damit geben könnte, denn ihr habt sicherlich nicht ohne Grund gesagt, dass p1.geraet < p2.geraet sein soll, oder?

 

[akretschmer]

Danke für Deine Antwort, jetzt klappt das auch mit den Inner-Joins.

Ich habe eine Frage zu dem WHERE-Kriterium

In Deinem Beispiel steht:

WHERE p1.geraet < p2.geraet and p2.geraet < p3.geraet;

Ginge das auch damit?:

 WHERE p1.geraet = 0 AND p2.geraet = 1 AND p3.geraet=2;

Das scheint zwar das gleiche Ergebnis anzuzeigen, ich weis aber nicht, ob es dennoch Probleme damit geben könnte, denn ihr habt sicherlich nicht ohne Grund gesagt, dass p1.geraet < p2.geraet sein soll, oder?

Die Idee hatte @Hony% , ich denke mal, er wird antworten ;-)