group by Abfrage

[feriz]

Hallo,

ich hoffe Ihr könnt mir helfen. Ich habe folgende Tabelle:


StrassenNo SegmentNo Geschwindigkeit Beschleunigung
1 1 50 1,4
4 3 80 0,3
1 5 60 0,7
2 1 110 2
1 1 55 1,3


Die Tabelle ist natürlich viel länger. Darin werden Daten über Straßen in einem Simulationsprogramm innerhalb einer Minute gesammelt. Jede Straße ist in gleich langen Segmenten unterteilt. Und zu jeder Straßennummer und Segmentnummer werden durchschnittliche Geschwindigkeit und Beschleunigung gespeichert. Wie man anhand der Zeilen 1 und 5 sehen kann kommen die selben Straßennummern und Segmentnummern auch öfter in dieser Tabelle vor.

Wie ich über Straßennummer und Segmentnummer gruppieren kann, ist klar:

select StrassenNO, SegmentNO, AVG(Geschwindigkeit), AVG(Beschleunigung) GROUP BY StrassenNo, SegmentNO

Gibt es aber eine Möglichkeit die Geschwindigkeit und Beschleunigung von den Nachbarsegmenten mit einzubeziehen? So dass die neue Tabell folgende Zeilen hat: StrassenNO, SegmentNO, avgGeschw, avgBeschleu, avgGeschwVorne, avgBeschleuVorne, avgGeschwHinten, avgBeschleuHinten

Es soll alles in SQL sein.

Vielen Dank

 

[thomas_w]

AW: group by Abfrage

Gibt es aber eine Möglichkeit die Geschwindigkeit und Beschleunigung von den Nachbarsegmenten mit einzubeziehen?

Zwei Fragen dazu:

Wie werden "Nachbarsegmente" genau definiert ?

Ist in der Tabelle eine ID Spalte (PK) mit einer laufenden Nummer (möglichst lückenlos)?

Grüße
Thomas

 

[feriz]

AW: group by Abfrage

Hallo Thomas,

mit Nachbarsegmenten sind nur die beiden unmittelbar an den Aktuellen Segment angrenzenden Segmenten vorne und hinten. Beispielsweise sind die Nachbarsegmente zu StrassenNO 1, SegmentNO 4 :
StrassenNO 1, SegmentNO 3 und StrassenNO 1, SegmentNO 5

Leider gibt es keine ID Spalte in der Tabelle, nur StrassenNO, SegmentNO, Geschwindigkeit und Beschleunigung. Die Datensätze werden von einem Datenstrom kontinuierlich geliefert und nur in einem Ausschnitt von einer Minute gespeichert. Ich muss nun eine Anfrage stellen, die über StrassenNo und SegmentNO gruppiert, wobei zu jedem Segment nicht nur die durchschnittliche Geschwindigkeit und Beschleunigung des Segments sondern auch die der Nachbarsegmenten gespeichert werden.
Eine 1 km lange Strasse ist beispielsweise in 5 hintereinanderliegenden Segmenten (jeweils 200 m) unterteilt.

 

[thomas_w]

AW: group by Abfrage

Mal ein Vorschlag dazu:

CREATE TABLE segmente (
 StrassenNo INT NOT NULL,
 SegmentNo  INT NOT NULL,
 Geschwindigkeit INT NOT NULL,
 Beschleunigung DEC (5,2) NOT NULL
);
INSERT INTO segmente VALUES
(1, 1, 50, 1.4),
(4, 3, 80, 0.3),
(1, 5, 60, 0.7),
(2, 1, 110, 2),
(1, 1, 55, 1.3);
 
SELECT s3.strassenno, s3.segmentno, s3.avg_geschw, s3.avg_beschl, 
       s4.strassenno AS strassenno_vor , s4.segmentno AS segmentno_vor, s4.avg_geschw AS avg_geschw_vor, s4.avg_beschl AS avg_beschl_vor,
       s5.strassenno AS strassenno_nach, s5.segmentno AS segmentno_nach, s5.avg_geschw AS avg_geschw_nach, s5.avg_beschl AS avg_beschl_nach       
  FROM ( SELECT s2.strassenno, s2.segmentno, s2.avg_geschw, s2.avg_beschl, @rownum1 := @rownum1+1 AS rownum1
           FROM ( SELECT @rownum1 := 0 ) AS init,
                ( SELECT s.StrassenNO, 
                         s.SegmentNO, 
                         AVG(s.Geschwindigkeit) AS avg_geschw, 
                         AVG(s.Beschleunigung) AS avg_beschl
                    FROM segmente s
                   GROUP BY s.StrassenNo, s.SegmentNO
                   ORDER BY s.StrassenNo, s.SegmentNO
                ) s2
       ) s3
  LEFT JOIN ( SELECT s2.strassenno, s2.segmentno, s2.avg_geschw, s2.avg_beschl, @rownum2 := @rownum2+1 AS rownum2
           FROM ( SELECT @rownum2 := 0 ) AS init,
                ( SELECT s.StrassenNO, 
                         s.SegmentNO, 
                         AVG(s.Geschwindigkeit) AS avg_geschw, 
                         AVG(s.Beschleunigung) AS avg_beschl
                    FROM segmente s
                   GROUP BY s.StrassenNo, s.SegmentNO
                   ORDER BY s.StrassenNo, s.SegmentNO
                ) s2
       ) s4       
    ON s4.rownum2 = s3.rownum1 -1
  LEFT JOIN ( SELECT s2.strassenno, s2.segmentno, s2.avg_geschw, s2.avg_beschl, @rownum3 := @rownum3+1 AS rownum3
           FROM ( SELECT @rownum3 := 0 ) AS init,
                ( SELECT s.StrassenNO, 
                         s.SegmentNO, 
                         AVG(s.Geschwindigkeit) AS avg_geschw, 
                         AVG(s.Beschleunigung) AS avg_beschl
                    FROM segmente s
                   GROUP BY s.StrassenNo, s.SegmentNO
                   ORDER BY s.StrassenNo, s.SegmentNO
                ) s2
       ) s5       
    ON s5.rownum3 = s3.rownum1 +1;
 
+------------+-----------+------------+------------+----------------+---------------+----------------+----------------+-----------------+----------------+-----------------+-----------------+
| strassenno | segmentno | avg_geschw | avg_beschl | strassenno_vor | segmentno_vor | avg_geschw_vor | avg_beschl_vor | strassenno_nach | segmentno_nach | avg_geschw_nach | avg_beschl_nach |
+------------+-----------+------------+------------+----------------+---------------+----------------+----------------+-----------------+----------------+-----------------+-----------------+
|          1 |         1 |    52.5000 |   1.350000 |           NULL |          NULL |           NULL |           NULL |               1 |              5 |         60.0000 |        0.700000 |
|          1 |         5 |    60.0000 |   0.700000 |              1 |             1 |        52.5000 |       1.350000 |               2 |              1 |        110.0000 |        2.000000 |
|          2 |         1 |   110.0000 |   2.000000 |              1 |             5 |        60.0000 |       0.700000 |               4 |              3 |         80.0000 |        0.300000 |
|          4 |         3 |    80.0000 |   0.300000 |              2 |             1 |       110.0000 |       2.000000 |            NULL |           NULL |            NULL |            NULL |
+------------+-----------+------------+------------+----------------+---------------+----------------+----------------+-----------------+----------------+-----------------+-----------------+
4 rows in set (0.00 sec)
mysql>

Diplomarbeit ?

Grüße
Thomas

 

[feriz]

AW: group by Abfrage

Hallo Thomas,

erstmal vielen vielen Dank für deine Mühe. Ja, es ist die Diplomarbeit und ich bin auch schon fast fertig damit, in zwei Wochen muss ich abgeben. Ich habe das ganze System auch schon implementiert und bin gerade dabei das letzte Kapitel zu schreiben und die Ergebnisse zu evaluieren. Leider kommt mir einer meiner Betreuer kurz vor dem Schluss ständig mit neuen Aufgabe und ich will auch nicht darüber diskutieren und versuche das alles noch irgendwie zu schaffen.
Danke nochmal für deine Hilfe, ich werde das heute im Laufe des Tages mal ausprobieren und mich dann wieder melden.

Viele Grüße

 

[feriz]

AW: group by Abfrage

Hallo Thomas,

noch eine Frage bitte:

In der letzten Zeilen deiner Tabelle beispielsweise steht:
strassenno_vor = 2 und segmenno_vor = 1 bzw.
strassenno_nach = null und segmenno_nach = null.

Aber segmentno_vor und segmentno_nach von strassenno 4 und segmentno 3 sind:
segmenno_vor = 2 und segmentno_nach = 4 mit der selben strassenno 4.
Also die zeilen in meiner Tabelle sind nicht geordnet. Heisst das, ich muss meine Tabelle vorher nach Strassenno und Segmentno ordnen bevor ich deine Anfrage verwenden kann?

Die Straßen sind in der Simulation nicht geordnet. Es kann also vorkommen, dass nach Strassenno 2 erstmal Strassenno 5 vorkommt. Aber die Segmente innerhalb einer Strasse sind geordnet. Das heisst segmentno_vor von dem ersten Segment einer Strasse bzw. segmentno_nach von dem letzten Segment einer Strasse kann man nicht aus der Tabelle lesen. avgGeschwindigkeit und avgBeschleunigung können hierfür ruhig null sein.
strassenno_vor und strassenno_nach müssen in der neuen Tabelle (oder Anfrage) nicht enthalten sein.

Danke!

 

[thomas_w]

AW: group by Abfrage

Aber segmentno_vor und segmentno_nach von strassenno 4 und segmentno 3 sind:
segmenno_vor = 2 und segmentno_nach = 4 mit der selben strassenno 4.
Also die zeilen in meiner Tabelle sind nicht geordnet. Heisst das, ich muss meine Tabelle vorher nach Strassenno und Segmentno ordnen bevor ich deine Anfrage verwenden kann?

Nein, Du must die Daten nicht ordnen, das macht schon der SQL mit ORDER BY. Bei meinen fünf Testdaten gibt es nach dem Sortieren einen "ersten" und einen "letzten" Datensatz. Der "erste" hat logischerweise keinen Vorganger (da keine Daten vorliegen) und der "letzte" hat logischweise keinen Nachfolger (da keine weiteren Daten vorliegen).

Nur die erste und die letzte Ergebniszeile der SQL-Abfrage haben die NULL Spalten.

Grüße
Thomas

 

[feriz]

AW: group by Abfrage

Hallo Thomas,

ich habe noch ein paar Kleinigkeiten geändert und es funktioniert jetzt, danke.

Eine Frage hätte ich noch

Wie kann man aus einer Menge von Zahlen, die Zahl auswählen, die am meisten beim GROUP BY vorkommt?

Beispiel: 1,1,1,2,3,3,7
Hier ist es also die 1

Viele Grüße

 

[thomas_w]

AW: group by Abfrage

Die Anzahl der Mitglieder einer Gruppe läßt sich so ermitteln (siehe COUNT(*) :

SELECT s.StrassenNO, 
       s.SegmentNO, 
       AVG(s.Geschwindigkeit) AS avg_geschw, 
       AVG(s.Beschleunigung) AS avg_beschl,
       COUNT(*) AS anzahl
  FROM segmente s
 GROUP BY s.StrassenNo, s.SegmentNO
 ORDER BY s.StrassenNo, s.SegmentNO;
 
+------------+-----------+------------+------------+--------+
| StrassenNO | SegmentNO | avg_geschw | avg_beschl | anzahl |
+------------+-----------+------------+------------+--------+
|          1 |         1 |    52.5000 |   1.350000 |      2 |
|          1 |         5 |    60.0000 |   0.700000 |      1 |
|          2 |         1 |   110.0000 |   2.000000 |      1 |
|          4 |         3 |    80.0000 |   0.300000 |      1 |
+------------+-----------+------------+------------+--------+
4 rows in set (0.16 sec)
mysql>

Grüße
Thomas