Abfragen einer Tabelle verschneiden

[SirS]

Hallo zusammen,

ich weiß nicht, ob ich einfach den Wald vor lauter Bäumen nicht sehe oder mir wirklich etwas verbaut habe und hoffe auf einen Denkanstoß.

Ich habe eine Tabelle

Benutzer | Wert | Jahr | Status

Jetzt soll der Benutzer seine Eigenschaften in einer Tabelle sehen können, was ich mit einer einfachen MySQL-Abfrage löse.

$jahr = "2008,2009,2010"
$status = "1,2,3"

SELECT Benutzer, Wert, Jahr, Status
FROM Tabelle1
WHERE Benutzer = '1'
AND Jahr IN ($jahr)
AND Status IN ($status)
ORDER BY Benutzer,Jahr,Plan

Manchmal soll jedoch auch zusätzlich der Durchschnittswert und die Anzahl aller Benutzer ausgegeben werden. Dafür nutze ich eine zweite separate Abfrage.

SELECT AVG(Wert), COUNT(Wert)
FROM Tabelle1
WHERE Jahr IN ($jahr)
AND Status IN ($status)
GROUP BY Benutzer,Jahr,Plan

In einer Schleife durchlaufe ich in der Ausgabe den ersten Befehl, gebe die Daten aus und hänge ggf. den Wert der zweiten Spalte an. Das funktioniert aber nur so lange, wie der aktuelle Benutzer alle Werte gepflegt hat. Wenn er z. B. das Jahr 2010 nicht pflegt, ein anderer Benutzer aber schon, stimmt die Anzahl der Datensätze nicht und ich komme durcheinander. Mein Gedanke ist nun, die zweite Abfrage nur auf die Werte zu beschränken, die auch in der ersten gewählt werden.

Mit MySQL-IF habe ich das Problem leider nicht lösen können.

Falls es eine bessere komplett andere Lösung gibt, bitte immer her damit ;-)

[thomas_w]

Ich tendiere dazu alles in eine SQL-Abfrage zu packen. Die Datenbank mit Schleifen abzufragen kann nicht sehr performant sein. Falls diese Abfrage sehr langsam sein sollte, helfen bestimmt passende Indices.

Also ungefähr so (ich hoffe da ist kein Tippfehler drin):

SELECT Benutzer, jahr, plan, status, AVG(Wert), COUNT(Wert)
  FROM Tabelle1
 WHERE Jahr IN ($jahr)
   AND Status IN ($status)
 GROUP BY Benutzer,Jahr,Plan,status
 ORDER BY Benutzer,Jahr,Plan,status;

Grüße
Thomas

[SirS]

Hallo Thomas,

danke für deine Hilfe. Leider komme ich damit aber nicht wirklich weiter. Ich hatte die eine Abfrage getrennt, weil ich mir sonst nicht zu helfen wusste. Mir ist leider gerade erst aufgefallen, dass ich vor dem Posten bei mir das GOUP BY verschlimmbessert hatte.

Das Problem daran war/ist, dass ich den Einzelwert eines einzelnen Benutzers (hier: Benutzer Nr. 1) brauche und ihm gleichzeitig anzeigen möchte, wie viele andere Benutzer diesen Wert gepflegt haben und wie hoch der Durchschnittswert ist. Da passen doch "GROUP BY Benutzer" und die Einzelwerte nicht zusammen, oder? Vielleicht blicke ich auch nicht ganz durch... Entweder ich frage die Einzelwerte zu einem Benutzer ab oder ich frage den Durchschnitt ab.

Daher hatte ich eine Abfrage mit Einzelwerten, die ich dann zeilenweise durchgegangen bin und habe mir den Durchschnittswert aus der richtigen Zeile der anderen Abfrage geholt.

Hier nochmal der Code:

$jahr = "2008,2009,2010"
$status = "1,2,3"

$queryausgabe = "
SELECT Benutzer, Wert, Jahr, Status
   FROM Tabelle1
 WHERE Benutzer = '1'
     AND Jahr IN ($jahr)
     AND Status IN ($status)
  ORDER BY Jahr,Plan";
$resultausgabe = mysql_query($queryausgabe);

$queryausgabe2 = "
SELECT AVG(Wert), COUNT(Wert)
   FROM Tabelle1
 WHERE Jahr IN ($jahr)
     AND Status IN ($status)
  GROUP BY Jahr,Plan";
$resultausgabe2 = mysql_query($queryausgabe2);

	while($rowausgabe = mysql_fetch_array($resultausgabe)){
		$rowausgabe2 = mysql_fetch_array($resultausgabe2);
		$durchschnitt=round($rowausgabe2['AVG(Wert)'], 2);
            echo 'Durchschnitt: '.number_format($durchschnitt, 2, ",", ".");
            echo 'Eigener Wert: '.number_format($rowausgabe['Wert'], 2, ",", ".");
	}

Das geht so lange gut, wie der aktuelle Benutzer alle Werte gepflegt hat und die Zeilen der beiden Abfragen zueinander passen.

Mit Join hatte ich es auch versucht, aber ich bin daran gescheitert, dass es sich um zwei Abfragen und keine zwei Tabellen handelt, die verknüpft werden sollen.


Als eine Lösung könnte ich mir vorstellen, dass ich die Einzelwerte durchgehe wie bisher und jedes Mal am Anfang der Schleife eine neue Abfrage zu der speziellen Zeile (Durchschnittswert zu einzelnem Jahr und Status) mache, aber das hört sich für mich sehr langsam an.

Vielen Dank nochmal!

[thomas_w]

Also im Moment bin ich schon der Meinung, dass es mit einer Abfrage gehen sollte.
Zum besseren Testen bräuchte ich folgendes:

mysql>SHOW CREATE TABLE tabelle1;

Plus eine paar Testdaten.

Grüße
Thomas

[SirS]

Hey Thomas,

danke für deine Geduld! Hier die Daten:

CREATE TABLE `Tabelle1` (
  `Benutzer` smallint(4) NOT NULL,
  `Wert` float DEFAULT NULL,
  `Status` tinyint(1) NOT NULL,
  `Jahr` smallint(4) NOT NULL,
  KEY `Status` (`Status`),
  KEY `Benutzer` (`Benutzer`,`Jahr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Daten für Tabelle `Tabelle1`
-- 

INSERT INTO `Tabelle1` (`Benutzer`, `Wert`, `Status`, `Jahr`) VALUES 
(1, 10, 1, 2008),
(1, 15, 2, 2008),
(1, 20, 1, 2010),
(2, 100, 1, 2008),
(2, 150, 2, 2008),
(2, 500, 1, 2009),
(2, 500, 2, 2009),
(2, 300, 1, 2010),
(2, 300, 2, 2010),
(3, 1, 1, 2008),
(3, 1, 2, 2008),
(3, 120, 1, 2009),
(3, 130, 2, 2009),
(3, 220, 1, 2010),
(3, 250, 2, 2010);

PHP-Code:

$jahr = "2008,2009,2010";
$status = "1,2,3";

$queryausgabe = "
SELECT Benutzer, Wert, Jahr, Status
   FROM Tabelle1
 WHERE Benutzer = '1'
     AND Jahr IN ($jahr)
     AND Status IN ($status)
  ORDER BY Jahr,Status";
$resultausgabe = mysql_query($queryausgabe);

$queryausgabe2 = "
SELECT AVG(Wert), COUNT(Wert)
   FROM Tabelle1
 WHERE Jahr IN ($jahr)
     AND Status IN ($status)
  GROUP BY Jahr,Status";
$resultausgabe2 = mysql_query($queryausgabe2);
echo '<table border="1">
<tr>
<th>Jahr</th>
<th>Status</th>
<th>Durchschnitt</th>
<th>Anzahl</th>
<th>Eigener Wert</th>
</tr>';
    while($rowausgabe = mysql_fetch_array($resultausgabe)){
        $rowausgabe2 = mysql_fetch_array($resultausgabe2);
        $durchschnitt=round($rowausgabe2['AVG(Wert)'], 2);
            echo '<tr><td>'.$rowausgabe['Jahr'].'</td>';
            echo '<td>'.$rowausgabe['Status'].'</td>';
            echo '<td>'.number_format($durchschnitt, 2, ",", ".").'</td>';
            echo '<td>'.number_format($rowausgabe2['COUNT(Wert)'], 0, ",", ".").'</td>';
            echo '<td>'.number_format($rowausgabe['Wert'], 2, ",", ".").'</td></tr>';

    }
echo '</table>'; 


In meiner Version stimmt der dritte Wert nicht, da der Einzelwert aus Jahr=2010 mit Status=1 stammt, der Durchschnittswert aber aus Jahr=2009 und Status=1.

[thomas_w]

So, jetzt habe ich die Daten eingelesen und getesten. Der SQL ist leider etwas größer geworden, liefert ab dieser Spalten, mit denen die gewünschten Vergleichwerte (ein Benutzer im Vergleich zu allen Benutzern) berechnet werden können. Auch das Jahr 2009 ist vorhanden (siehe LEFT JOIN)

SELECT b1.benutzer, b1.avg_wert AS b1_avg_wert, b1.anzahl AS b1_anzahl, 
       gesamt.jahr, gesamt.status, AVG(gesamt.Wert) AS gesamt_avg_wert, 
       COUNT(gesamt.Wert) AS gesamt_anzahl
  FROM Tabelle1 gesamt
  LEFT JOIN ( SELECT Benutzer, Jahr, Status, AVG(wert) AS avg_wert, COUNT(wert) AS anzahl
                FROM Tabelle1
               WHERE Benutzer = 1
                 AND Jahr IN (2008,2009,2010)
                 AND Status IN (1,2,3)
               GROUP BY benutzer, jahr, status
                 
  ) b1     
  ON b1.jahr = gesamt.jahr
  AND b1.status = gesamt.status
 WHERE gesamt.jahr IN (2008,2009,2010)
   AND gesamt.status IN (1,2,3)
 GROUP BY gesamt.jahr,gesamt.status ;
 
+----------+-------------+-----------+------+--------+------------------+---------------+
| benutzer | b1_avg_wert | b1_anzahl | jahr | status | gesamt_avg_wert  | gesamt_anzahl |
+----------+-------------+-----------+------+--------+------------------+---------------+
|        1 |          10 |         1 | 2008 |      1 |               37 |      3        |
|        1 |          15 |         1 | 2008 |      2 | 55.3333333333333 |      3        |
|     NULL |        NULL |      NULL | 2009 |      1 |              310 |      2        |
|     NULL |        NULL |      NULL | 2009 |      2 |              315 |      2        |
|        1 |          20 |         1 | 2010 |      1 |              180 |      3        |
|     NULL |        NULL |      NULL | 2010 |      2 |              275 |      2        |
+----------+-------------+-----------+------+--------+------------------+---------------+
6 rows in set (0.00 sec)

So, ich hoffe die Lösung hat ein bißchen was mit Deinem Problem zu tun.

Grüße
Thomas

[SirS]

Super, alle Daumen hoch!
Ich dachte bisher, ich hätte Joins verstanden, aber erst jetzt hat es wirklich klick gemacht.

Für alle, die ein ähnliches Problem haben:
Ich habe noch LEFT durch INNER JOIN ersetzt, um nur die Zeilen von Benutzer 1 angezeigt zu bekommen. Außerdem habe ich die Spalte "b1.anzahl" herausgenommen, da es durch INNER JOIN und meinen Tabellenaufbau immer 1 ist. Zuletzt habe ich aus dem gleichen Grund "AVG(wert) AS avg_wert, COUNT(wert) AS anzahl" durch "Wert" ersetzt.

Nochmals ein fettes Dankeschön an Thomas.