INNER JOIN - zuviele Datensätze?

[Samoht]

Gudn!

Frage an die Datenbankexperten bezüglich eines INNER JOIN

Habe folgende Tabelle (vereinfacht):

messreihe	lambda	messung
2	1.7	2.55
2	1.8	3.45
2	1.9	4,32
3	1.7	3.21
3	1.8	2.89
3	1.9	1.42
5	1.7	3.86
5	1.8	2.28
5	1.9	1.12

Eigentlich sind es mehrere Millionen Einträge.
Mein Wunschergebnis sieht so aus:

| lambda | messreihe2 | messreihe3 | messreihe5 |
| 1.7    | 2.55       | 3.21       | 3.86       |
| 1.8    | 3.45       | 2.89       | 2.28       |
| 1.9    | 4.32       | 1.42       | 1.12       |

bzw als Array in PHP

Array (
[0] => Array ([lambda] => 1.7 [messreihe2] => 2.55 [messreihe3] => 3.21 [messreihe5] => 3.86])
[1] => Array ([lambda] => 1.8 [messreihe2] => 3.45 [messreihe3] => 2.89

usw...
)

Versucht habe ich einen INNER JOIN wie folgt:

  
SELECT daten1.lambda, daten1.messung, daten2.messung  
FROM daten AS daten1  
INNER JOIN daten AS daten2 ON daten1.lambda = daten2.lambda  
INNER JOIN daten AS daten3 ON daten1.lambda = daten3.lambda  

Hier gibt mir SQL aus:
1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Ist meine Überlegung zur Abfrage nun überhaupt richtig und ich muss einfach nur die Parameter ändern, oder habe ich da Bockmist gebaut?

Gruß aus Fürth in Mittelfranken,
Samoht

[Kalle_B]

Hier gibt mir SQL aus:
1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Kommt ein Ergebnis, wenn du LIMIT 0,100 anfügst?

Kalle

[Samoht]

Gudn!

Kommt ein Ergebnis, wenn du LIMIT 0,100 anfügst?

Nein, es kommt die gleiche Meldung.

Gruß aus Fürth in Mittelfranken,
Samoht

[Ilja]

moin,

ich vermute durch die fehlermeldung du benutzt mysql in welcher version ?

Eigentlich sind es mehrere Millionen Einträge.

wenn du nur die drei spalten haben willst, also keine unbekannte anzahl an spalten, dann geht das auch über die self joins. aber es ist unschön und bedarf einiger tricks.

SELECT daten1.lambda, daten1.messung, daten2.messung
FROM daten AS daten1
INNER JOIN daten AS daten2 ON daten1.lambda = daten2.lambda
INNER JOIN daten AS daten3 ON daten1.lambda = daten3.lambda

  
mal davon abgesehen, dass mir persönlich die aliasnamen zu lang wären, das AS überflüssig ist, ist dein code nicht optimal und fachlich falsch. es fehlt die selektion der einzelnen tabellen auf die messreihen. den zweiten join würde ich auch anders setzen und gegebenfalls noch ein DISTINCT spendieren.  
  
.....  
INNER JOIN daten AS daten3 ON daten3.lambda = daten2.lambda  
WHERE daten1.messreihe = 2  
AND daten2.messreihe = 3  
daten3.messreihe = 5  
  
viel besser würden sich aber korrelierte unterabfragen machen, wobei das erst ab mysql 4.1+ geht und dann auch nur, wenn du sicher stellen kannst, dass pro messreihe und lamda immer nur ein wert geliert wird.  
  
SELECT d1.lambda, d1.messung  
       (SELECT d2.messung  
        FROM daten d2  
        WHERE d2.lambda = d1.lambda  
        AND d2.messreihe = 3  
        ) Messung2,  
       (SELECT d23.messung  
        FROM daten d3  
        WHERE d3.lambda = d1.lambda  
        AND d3.messreihe = 5  
        ) Messung3  
FROM daten d1  
WHERE d1.messreihe = 2  
;  
  
und noch eleganter finde ich eine gruppierung  
  
SELECT d1.lambda,  
       MAX(CASE  
               WHEN d1.messreihe = 2  
               THEN d1.messung  
               ELSE NULL  
          ) Messung1,  
       MAX(CASE  
               WHEN d1.messreihe = 3  
               THEN d1.messung  
               ELSE NULL  
          ) Messung2,  
       MAX(CASE  
               WHEN d1.messreihe = 5  
               THEN d1.messung  
               ELSE NULL  
          ) Messung3,  
FROM daten d1  
GROUP BY d1.lambda  
  
Ilja

[Samoht]

Gudn, Ilja!

Vielen Dank für Dein ausführliches Posting. Ich werde es mir morgen genauer zu Gemüte ziehen und mich nochmal rückmelden. Sieht sehr vielversprechend aus - ich hoffe mehr in Bezug auf JOINS zu lernen :)

Gruß aus Fürth in Mittelfranken,
Samoht

[Samoht]

Gudn!

ich vermute durch die fehlermeldung du benutzt mysql in welcher version ?

Ja, sorry. Es ist MySQL 5.0.32

wenn du nur die drei spalten haben willst, also keine unbekannte anzahl an spalten, dann geht das auch über die self joins. aber es ist unschön und bedarf einiger tricks.

Nein, unbekannt ist die Anzahl nicht, sie wird vorher bestimmt.

und noch eleganter finde ich eine gruppierung

SELECT d1.lambda,
       MAX(CASE
               WHEN d1.messreihe = 2
               THEN d1.messung
               ELSE NULL
          ) Messung1,
       MAX(CASE
               WHEN d1.messreihe = 3
               THEN d1.messung
               ELSE NULL
          ) Messung2,
       MAX(CASE
               WHEN d1.messreihe = 5
               THEN d1.messung
               ELSE NULL
          ) Messung3,
FROM daten d1
GROUP BY d1.lambda

Klasse, herzlichen Dank! Das funktioniert super :)
Vinzenz hat nach Dir auch diese Variante gewählt, werde bei ihm weiterloben ;)

Gruß aus Fürth in Mittelfranken,
Samoht

[Vinzenz Mai]

Hallo,

Frage an die Datenbankexperten bezüglich eines INNER JOIN

messreihe	lambda	messung
2	1.7	2.55
2	1.8	3.45
2	1.9	4,32
3	1.7	3.21
3	1.8	2.89
3	1.9	1.42
5	1.7	3.86
5	1.8	2.28
5	1.9	1.12

Eigentlich sind es mehrere Millionen Einträge.
Mein Wunschergebnis sieht so aus:

| lambda | messreihe2 | messreihe3 | messreihe5 |
| 1.7    | 2.55       | 3.21       | 3.86       |
| 1.8    | 3.45       | 2.89       | 2.28       |
| 1.9    | 4.32       | 1.42       | 1.12       |

Sieht nach einer Kreuztabelle aus :-)
MySQL bietet keine eingebaute Unterstützung für Kreuztabellen :-(

Gehe ich nun davon aus, dass auf der Spaltenkombination messreihe, lambda ein kombinierter eindeutiger Index liegt, so gibt es für jedes Tupel (lambda, messreihe) genau einen Wert, den Du anzeigen möchtest.

Da Du für jeden lambda-Wert eine einzige Zeile haben willst, ist eine Gruppierung nach lambda erforderlich. Für jeden unterschiedlichen Wert der Spalte messreihe willst Du eine eigene Spalte erzeugen.

Da Du eine Gruppierung verwendest, solltest Du auf die weiteren Spalten eine Aggregatsfunktion anwenden (auch wenn MySQL das nicht unbedingt haben will). MAX, MIN, AVG sollten alle den einzigen vorhandenen Wert liefern. Ich verwende einfach MAX.

Bei Deinen Beispieldaten sähe das Statement wie folgt aus:

  
SELECT       Gib mir zu jedem unterschiedlichen Wert der Spalte  
    lambda,  -- lambda  
    -- berücksichtige nur Zeilen mit dem Wert 2 in Messreihe  
    -- und von allen diesen Zeilen hätte ich nur das Maximum  
    -- (das ja genau einmal zu einem lambda-Wert vorkommt)  
    -- und benenne diese Reihe messreihe2  
    MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END) AS messreihe2,  
    -- berücksichtige nur Zeilen mit dem Wert 3 in Messreihe  
    -- und von allen diesen Zeilen hätte ich nur das Maximum  
    -- (das ja genau einmal zu einem lambda-Wert vorkommt)  
    -- und benenne diese Reihe messreihe3  
    MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END) AS messreihe3,  
    -- analog für 5  
    MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END) AS messreihe5  
FROM  
    daten  
GROUP BY lambda  

Dies liefert das gewünschte Resultat.

| lambda | messreihe2 | messreihe3 | messreihe5 |
| 1.7    | 2.55       | 3.21       | 3.86       |
| 1.8    | 3.45       | 2.89       | 2.28       |
| 1.9    | 4.32       | 1.42       | 1.12       |

Auf die Aggregatsfunktion kannst Du auch unter MySQL nicht verzichten, das in dieser Hinsicht ja großzügig ist, weil nicht garantiert ist, ob der Wert aus messung oder NULL genommen wird. Jedenfalls zeigt das um die Kommentare bereinigte Statement die Systematik, nach der Du Dein Gesamtstatement dynamisch zusammenbauen könntest:

  
SELECT  
    lambda,  
    MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END) AS messreihe2,  
    MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END) AS messreihe3,  
    MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END) AS messreihe5  
FROM  
    daten  
GROUP BY lambda  

wobei Du die unterschiedlichen Werte in messreihe, aufsteigend sortiert verwendest, wie sie Dir

  
SELECT DISTINCT  
    messreihe  
FROM  
    daten  
ORDER BY  
    messreihe  

Ergebnis:

messreihe
---------
    2
    3
    5

oder alternativ

  
SELECT  
    messreihe  
FROM  
    daten  
GROUP BY  
    messreihe  

liefern, da eine GROUP-BY-Spalte in MySQL implizit eine Sortierung nach dieser Spalte verursacht.

Die notwendige Abfrage nach dem Schema

  
SELECT  
    lambda,  
    MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END) AS messreihe2,  
    MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END) AS messreihe3,  
    MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END) AS messreihe5  
FROM  
    daten  
GROUP BY lambda  

solltest Du in PHP somit bequem zusammenbauen können. Mit Hilfe einer Stored Procedure und einem Cursor über das Ergebnis der anderen Abfrage könntest Du
dies auch hinbekommen, aber ich bezweifle, dass sich der Aufwand lohnt. Für ein vergleichbares Problem beim MS SQL-Server 2000 habe ich mal was gepostet.

Eine SP, die eine beliebige Kreuzabfrage zusammenbaut, wäre schon ganz nett. Da muss man aber alle Eventualitäten berücksichtigen. Es dürfte nicht trivial sein.

Weiterhin solltest Du unbedingt die Performance dieser Abfrage mit Deiner Datenmenge prüfen, und mit Hilfe von EXPLAIN schauen, ob Indexe genutzt werden können.

Freundliche Grüße

Vinzenz

[Samoht]

Gudn!

Gehe ich nun davon aus, dass auf der Spaltenkombination messreihe, lambda ein kombinierter eindeutiger Index liegt, so gibt es für jedes Tupel (lambda, messreihe) genau einen Wert, den Du anzeigen möchtest.

Richtig, sonst hätte ich was falsch gespeichert :)
Abgesehen davon liebt sowieso ein Unique über den Spalten.

Da Du für jeden lambda-Wert eine einzige Zeile haben willst, ist eine Gruppierung nach lambda erforderlich. Für jeden unterschiedlichen Wert der Spalte messreihe willst Du eine eigene Spalte erzeugen.

Da Du eine Gruppierung verwendest, solltest Du auf die weiteren Spalten eine Aggregatsfunktion anwenden (auch wenn MySQL das nicht unbedingt haben will). MAX, MIN, AVG sollten alle den einzigen vorhandenen Wert liefern. Ich verwende einfach MAX.

Ah, verstehe. Verstehen ist zwar leider nicht merken, dafür mache ich nicht genügend "komplizierte" (= "nicht standard") Abfragen, aber zumindest ein Schritt dahin...

Bei Deinen Beispieldaten sähe das Statement wie folgt aus:

SELECT       Gib mir zu jedem unterschiedlichen Wert der Spalte
    lambda,  -- lambda
[...]
FROM
    daten
GROUP BY lambda

>   
> Dies liefert das gewünschte Resultat.  
>   
> | lambda | messreihe2 | messreihe3 | messreihe5 |  
> | 1.7    | 2.55       | 3.21       | 3.86       |  
> | 1.8    | 3.45       | 2.89       | 2.28       |  
> | 1.9    | 4.32       | 1.42       | 1.12       |  
  
Ich bin echt begeistert, das Ergebnis sieht so aus, wie ich es mir vorgestellt habe. Alleine wäre ich da sicher nicht drauf gekommen.  
  
  

> solltest Du in PHP somit bequem zusammenbauen können. [...]  
  
Das war mein Gedanke. Eine Abfrage vorher werden die messreihen bestimmt, die benötigt werden (aus einer anderen Tabelle), hier ging es um die Messdaten, um sie plotten zu können.  
  

> Weiterhin solltest Du unbedingt die Performance dieser Abfrage mit Deiner Datenmenge prüfen, und mit Hilfe von EXPLAIN schauen, ob Indexe genutzt werden können.  
  
Werde mit ansehen. Wobei die Performance mich in erster Linie überhaupt nicht interessiert, außer die Abfragen würden Minuten dauern (was sie aber mit den ersten Tests kein bisschen tun).  
  
Erst hatte ich angefangen mit PHP die Arrays neu zu strukturieren, nur wäre das nicht wirklich flexibel gewesen, bzw. umständlich. Habs mir doch gedacht, dass das SQL selber hinbekommen müsste :)  
  
Vielen herzlichen Dank nochmal an Dich und Ilja, Ihr habt mir sehr geholfen!  
  
  
Gruß aus Fürth in Mittelfranken,  
Samoht

-- 
fl:| br:> va:) ls:< n4:( ss:) de:] js:| mo:}

[Samoht]

Gudn!

Eine Frage hätte ich noch :)

Gehe ich Recht in der Annahme, dass es performanter ist zu schreiben

  
SELECT  
 lambda,  
 MAX(CASE WHEN messreihe = 2 AND lambda < 2.2 THEN messung ELSE NULL END) AS messreihe2,  
 MAX(CASE WHEN messreihe = 3 AND lambda < 2.2 THEN messung ELSE NULL END) AS messreihe3,  
 MAX(CASE WHEN messreihe = 5 AND lambda < 2.2 THEN messung ELSE NULL END) AS messreihe5  
 FROM  
   daten  
 GROUP BY lambda  
 HAVING lambda < 2.2  

als

  
SELECT  
 lambda,  
 MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END) AS messreihe2,  
 MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END) AS messreihe3,  
 MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END) AS messreihe5  
 FROM  
   daten  
 GROUP BY lambda  
 HAVING lambda < 2.2  

?

Zumindest erscheint es mir logisch und erste Tests geben mir Recht bezüglich der Dauer der Abfrage.

Gruß aus Fürth in Mittelfranken,
Samoht

[Samoht]

Gudn!

Na, so schnell erledigen sich meine Fragen doch nicht. Ein weiteres kleines Problemchen hat sich gerade ergeben. Wenn ich versuche von jeder "Zeile" einen Wert abzuziehen

  
 SELECT  
  lambda,  
  (MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END)-5) AS messreihe2,  
  (MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END)-5) AS messreihe3,  
  (MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END)-5) AS messreihe5  
  FROM  
    daten  
  GROUP BY lambda  

dann funktioniert das wunderbar. Bei

  
 SELECT  
  lambda,  
  MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END) AS messreferenz,  
  (MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END)-messreferenz) AS messreihe2,  
  (MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END)-messreferenz) AS messreihe3,  
  (MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END)-messreferenz) AS messreihe5  
  FROM  
    daten  
  GROUP BY lambda  

ergibt sich nun ein "1054 - Unknown column 'messreferenz' in 'field list'". Ich dachte man könne mit diesen Werten gleich weiterarbeiten? Oder muss ich hierbei noch etwas beachten, dass ich übersehen habe.

Gruß aus Fürth in Mittelfranken,
Samoht

[Vinzenz Mai]

Hallo

dann funktioniert das wunderbar. Bei

SELECT
  lambda,
  MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END) AS messreferenz,
  (MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END)-messreferenz) AS messreihe2,
  (MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END)-messreferenz) AS messreihe3,
  (MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END)-messreferenz) AS messreihe5
  FROM
    daten
  GROUP BY lambda

>   
> ergibt sich nun ein "1054 - Unknown column 'messreferenz' in 'field list'". Ich dachte man könne mit diesen Werten gleich weiterarbeiten?  
  
Nein, kannst Du mit [Spaltenaliasen nicht](http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html).  
  
Daher wäre es  
  
~~~sql
  
SELECT  
    lambda,  
    MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END) AS messreferenz,  
    MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END)  
      - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe2,  
    MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END)  
      - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe3,  
    MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END)  
      - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe5  
    FROM  
        daten  
    GROUP BY lambda  

Zu Deiner Einschränkung für lambda < 2.2:

Gehe ich Recht in der Annahme, dass es performanter ist zu schreiben

SELECT
lambda,
MAX(CASE WHEN messreihe = 2 AND lambda < 2.2 THEN messung ELSE NULL END) AS messreihe2,
MAX(CASE WHEN messreihe = 3 AND lambda < 2.2 THEN messung ELSE NULL END) AS messreihe3,
MAX(CASE WHEN messreihe = 5 AND lambda < 2.2 THEN messung ELSE NULL END) AS messreihe5
FROM
   daten
GROUP BY lambda
HAVING lambda < 2.2

  
verwendest Du \*nicht\* die HAVING-Klausel sondern die WHERE-Klausel, vergleiche dazu einfach den verlinkten Abschnitt über Aliasnamen  
  
~~~sql
  
SELECT  
    lambda,  
    MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END) AS messreferenz,  
    MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END)  
      - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe2,  
    MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END)  
      - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe3,  
    MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END)  
      - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe5  
    FROM  
        daten  
    WHERE  
        -- Schränke die betrachteten Datensätze ein, bevor ausgewertet und  
        -- gruppiert wird.  
        lambda < 2.2  
    GROUP BY lambda  

HAVING schlägt erst dann zu, wenn schon alles ausgewertet wurde. EXPLAIN sollte da Unterschiede aufzeigen.

Freundliche Grüße

Vinzenz

[Samoht]

Gudn!

Nein, kannst Du mit Spaltenaliasen nicht.

Da bin ich nach meinem Post auch drüber gestolpert... :(

Daher wäre es

SELECT
    lambda,
    MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END) AS messreferenz,
[...]
    FROM
        daten
    GROUP BY lambda

  
\*handandiestirnklatsch\*  
  
Klar! :)  
  
Danke!  
  

> Zu Deiner Einschränkung für lambda < 2.2:  
> [...]  
  

> verwendest Du \*nicht\* die HAVING-Klausel sondern die WHERE-Klausel, vergleiche dazu einfach den verlinkten Abschnitt über Aliasnamen  
>   
> ~~~sql
  

> SELECT  
>     lambda,  
>     MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END) AS messreferenz,  
>     MAX(CASE WHEN messreihe = 2 THEN messung ELSE NULL END)  
>       - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe2,  
>     MAX(CASE WHEN messreihe = 3 THEN messung ELSE NULL END)  
>       - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe3,  
>     MAX(CASE WHEN messreihe = 5 THEN messung ELSE NULL END)  
>       - MAX(CASE WHEN messreihe = 1 THEN messung ELSE NULL END AS messreihe5  
>     FROM  
>         daten  
>     WHERE  
>         -- Schränke die betrachteten Datensätze ein, bevor ausgewertet und  
>         -- gruppiert wird.  
>         lambda < 2.2  
>     GROUP BY lambda  
> 

Mhm... ich hatte als erstes WHERE drin am Schluss... Aus welchem Grund auch immer hatte es nicht funktioniert und ich bin zu HAVING übergegangen. Hatte wahrscheinlich einen Fehler drin, es funktioniert nämlich tatsächlich wie man es erwarten sollte. Danke.

HAVING schlägt erst dann zu, wenn schon alles ausgewertet wurde. EXPLAIN sollte da Unterschiede aufzeigen.

Macht Sinn und EXPLAIN zeigt es auch in der Tat. Ergebnis ist, die Abfrage ist nochmal ne Spur kürzer.

Falls Du mal in der Gegend sein solltest: Ein kühles Bier sei Dir sicher! ;)

Gruß aus Fürth in Mittelfranken,
Samoht