1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

group by Abfrage

Dieses Thema im Forum "MySQL" wurde erstellt von feriz, 11 November 2010.

  1. feriz

    feriz Benutzer

    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
     
  2. thomas_w

    thomas_w Datenbank-Guru

    AW: group by Abfrage

    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
     
  3. feriz

    feriz Benutzer

    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.
     
  4. thomas_w

    thomas_w Datenbank-Guru

    AW: group by Abfrage

    Mal ein Vorschlag dazu:

    Code:
    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
     
  5. feriz

    feriz Benutzer

    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
     
  6. feriz

    feriz Benutzer

    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!
     
  7. thomas_w

    thomas_w Datenbank-Guru

    AW: group by Abfrage

    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
     
  8. feriz

    feriz Benutzer

    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
     
  9. thomas_w

    thomas_w Datenbank-Guru

    AW: group by Abfrage

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

    Code:
    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
     
Die Seite wird geladen...

Diese Seite empfehlen