Auswertung einer Umfrage

[Alex]

Mahlzeit,

ich schraube gerade an einer Datenbankabfrage für eine Umfragesoftware und könnte etwas Hilfe beim Weiterentwickeln meines bisherigen Ergebnisses gebrauchen. Folgende Tabellen und Felder sind relevant:

questions:

• qid: Primary Key
• sid: ordnet Fragen einer Umfrage zu
• aid: ordnet Fragen einen Antworttyp zu
• question: enthält den Fragetext

answer_types:

• aid: Primary Key
• sid: ordnet Antworttyp einer Umfrage zu (jede Umfrage definiert sich ihre eigenen Typen)
• type: Antworttyp (Textfeld, Multiply Choice, ...)

answer_values:

• avid: Primary Key
• aid: ordnet einem Antworttypen die Antwortmöglichkeiten zu
• value: Antworttext ("Ja", "Nein", "Zu viel", ...)

results: (vom Benutzer ausgewählte Antworten in Multiple Choice u.ä.)

• rid: Primary Key
• qid: ordnet ein Ergebnis einer Frage zu
• sid: ordnet ein Ergebnis einer Umfrage zu
• avid: ordnet dem Ergebnis einen Antworttyp zu

results_text: (vom Benutzer frei eingegebene Texte)

• wie results, nur mit einem Feld answer für den Texteintrag anstatt der avid

Wie man sieht, ist diese Struktur nicht komplett normalisiert -- das DB-Design ist aber vorgegeben. Ich hantiere hier übrigens mit MySQL 5.

Nun möchte ich aus Datensätzen dieser DB die Auswertung einer Umfrage erstellen. Gehen wir mal davon aus, daß es nur zwei Fragetypen gibt:

(A) Multiple Choice (Tabelle results) und
  (B) Textfelder, die der Benutzer nach Belieben ausfüllen kann (Tabelle results_text).

Für A interessieren mich in der Auswertung die verfügbaren Optionen mit Text (answer_values.value) und wie häufig jede dieser Optionen insgesamt ausgewählt wurde.

Für B möchte ich einfach eine Auflistung der Texte haben (results_text.answer). Inhaltliche Doppelungen werden nicht berücksichtigt, d.h. wenn zwei Leute den gleichen Text geschrieben haben, dürfen ruhig beide Texte in der Auswertung auftauchen.

Das ist erstmal nicht so kompliziert und funktioniert im Einsatz auch schon. Allerdings werden u.A. die Antworten auf jeweils eine Frage einzeln von der DB erfragt -- nicht besonders schön, besonders bei umfangreichen Umfragen. Das muß sich doch auch in einem Rutsch erledigen lassen, dachte ich mir und widmete mich erstmal Teil A. Das sah dann so aus:

  
SELECT  
 q.qid, q.question, q.aid,  
 a.type,  
 av.avid, av.value,  
 COUNT(av.avid)  
FROM  
 questions q,  
 answer_types a,  
 answer_values av,  
 results r  
WHERE  
 -- nur die Fragen einer bestimmten Umfrage  
 q.sid = 32  
  
 -- nur die Antworttypen der vorher gefilterten Fragen  
 AND a.aid = q.aid  
  
 -- nur die Antwortwerte der vorher gefilterten Antworttypen  
 AND av.aid = a.aid  
  
 -- nur die ausgewählten Antworten (Ergebnisse) auf vorher  
 -- gefilterte Fragen und Antwortwerte  
 AND r.qid = q.qid  
 AND r.avid = av.avid  
GROUP BY  
 av.avid, q.qid  

Das läßt sich natürlich auch mit JOINs schreiben. Ich wollte beim Konstruieren nur erstmal den Überblick behalten, und das fiel mir leichter mit den Kreuzprodukt mehrere Tabellen, welches jeweils durch WHERE gefiltert wird ...

Beim Ergebnis gibt es allerdings ein Problem: Durch die Gruppierung gehen Antworttypen verloren, die kein einziges Mal ausgewählt wurden (wofür COUNT(av.avid) also 0 liefern würde). Krieg ich doch bloß wieder durch JOINs mit hinein?

Dann fehlt natürlich noch Teil B der Auswertung, und hier steh ich nun wirklich auf dem Schlauch. Das Endergebnis muß ja etwas Gemischtes sein, Weil sich A und B in der Art und Weise, wie sie in der DB abgelegt werden, ziemlich unterscheiden. Ich stelle mir das etwa so vor (mc = Multiple Choice, txt = freier Texteintrag):

+-----+------+---------------+------------------+------+
| qid | type | question      | value            | cnt  |
+-----+------+---------------+------------------+------+
| 1   | mc   | Was gibt's?   | Nichts           |  120 |
| 1   | mc   | Was gibt's?   | Ärger            |   50 |
| 2   | txt  | Und sonst so? | Muß ja.          | NULL |
| 2   | txt  | Und sonst so? | Gut. Und selber? | NULL |
| 2   | txt  | Und sonst so? | Ooch, weiß nich. | NULL |
+-----+------+---------------+------------------+------+

Wie krieg ich meinen bisherigen Ansatz noch erweitert, um results_text in dieser Form mit ins Ergebnis einfließen zu lassen? (Oder ist es gar keine so gute Idee, diese beiden unterschiedlichen Ergebnistypen auf Teufel komm raus in dieser Form miteinander verheiraten zu wollen?)

Für jeden hilfreichen Tip dankbar,

Alex

[Vinzenz Mai]

Hallo,

Beim Ergebnis gibt es allerdings ein Problem: Durch die Gruppierung gehen Antworttypen verloren, die kein einziges Mal ausgewählt wurden (wofür COUNT(av.avid) also 0 liefern würde). Krieg ich doch bloß wieder durch JOINs mit hinein?

Deine Abfrage ist leider so kaputt, dass nur MySQL keinen Syntaxfehler meldet. (Du wählst sechs Spalten aus, die keine Aggregatsfunktion enthalten - gruppierst jedoch nur nach zwei davon).

Und ja, Du benötigst OUTER JOINS.

Abgesehen davon finde ich explizite Joinsyntax wesentlich übersichtlicher und verständlicher als alles in der WHERE-Klausel zu mixen. Ich schreib' erst mal auf explizite Join-Syntax um, um die Abfrage zu verstehen, wenn implizite Joins verwendet werden.

Um Dir besser helfen zu können, liefere doch bitte ein paar Beispieldaten, sowohl für Multiple Choice, als auch Text.

+-----+------+---------------+------------------+------+
| qid | type | question      | value            | cnt  |
+-----+------+---------------+------------------+------+
| 1   | mc   | Was gibt's?   | Nichts           |  120 |
| 1   | mc   | Was gibt's?   | Ärger            |   50 |
| 2   | txt  | Und sonst so? | Muß ja.          | NULL |
| 2   | txt  | Und sonst so? | Gut. Und selber? | NULL |
| 2   | txt  | Und sonst so? | Ooch, weiß nich. | NULL |
+-----+------+---------------+------------------+------+

Wie krieg ich meinen bisherigen Ansatz noch erweitert, um results_text in dieser Form mit ins Ergebnis einfließen zu lassen?

UNION könnte Dir helfen.

(Oder ist es gar keine so gute Idee, diese beiden unterschiedlichen Ergebnistypen auf Teufel komm raus in dieser Form miteinander verheiraten zu wollen?)

Ich würde diese beiden Typen *nicht* zusammenstellen.

Freundliche Grüße

Vinzenz

[Alex]

Hallo,

Deine Abfrage ist leider so kaputt, dass nur MySQL keinen Syntaxfehler meldet. (Du wählst sechs Spalten aus, die keine Aggregatsfunktion enthalten - gruppierst jedoch nur nach zwei davon).

Es fällt mir schwer, dazu etwas zu sagen, da MySQL bisher das einzige RDBS ist, mit dem ich zu tun hatte. ;-)

Um Dir besser helfen zu können, liefere doch bitte ein paar Beispieldaten, sowohl für Multiple Choice, als auch Text.

Das wird sehr schnell sehr viel. Deshalb erstmal ein etwas überschaubarerer Auszug:

questions:
+-----+-----+------+----------------------------------------------------+
| sid | qid | aid  | question                                           |
+-----+-----+------+----------------------------------------------------+
| 32  | 318 | 1006 | In welcher Gruppe hast Du gearbeitet?              |
| 32  | 319 | 1007 | Welche Programmiersprachen beherrschst Du?         |
| 32  | 321 | 1008 | Sollten lieber JOINs benutzt werden?               |
| 32  | 324 | 1008 | Kommen auch andere DBS zum Einsatz?                |
| 32  | 342 |  999 | Welche anderen Programmiersprachen beherrschst Du? |
+-----+-----+------+----------------------------------------------------+

answer_types:
+------+-----+------+
| aid  | sid | type |
+------+-----+------+
|  999 | 32  | T    | <- Textfeld
| 1006 | 32  | MS   | <- Multiple Choice mit Einfachankreuzen
| 1007 | 32  | MM   | <- Multiple Choice mit Mehrfachankreuzen
| 1008 | 32  | MS   |
+------+-----+------+

answer_values:
+------+------+-------------------+
| avid | aid  | value             |
+------+------+-------------------+
| 4040 | 1006 | Produktdesign     |
| 4041 | 1006 | Schaltungstechnik |
| 4042 | 1006 | Programmierung    |
| 4045 | 1007 | C/C++             |
| 4046 | 1007 | Haskell           |
| 4047 | 1007 | Java              |
| 4048 | 1007 | Andere ...        |
| 4051 | 1008 | Ja                |
| 4052 | 1008 | Nein              |
+------+------+-------------------+

results:
+------+-----+-----+------+
| rid  | qid | sid | avid |
+------+-----+-----+------+
| 1678 | 321 | 32  | 4051 |
| 1698 | 321 | 32  | 4051 |
| 1716 | 321 | 32  | 4051 |
| 1849 | 319 | 32  | 4045 |
| 1877 | 318 | 32  | 4041 |
| 1953 | 318 | 32  | 4042 |
| 1982 | 318 | 32  | 4041 |
| 1985 | 319 | 32  | 4045 |
| 1990 | 324 | 32  | 4051 |
| 2019 | 318 | 32  | 4042 |
| 2021 | 319 | 32  | 4046 |
| 2026 | 319 | 32  | 4048 |
| 2027 | 324 | 32  | 4052 |
| 2039 | 318 | 32  | 4040 |
| 2040 | 319 | 32  | 4045 |
| 2041 | 319 | 32  | 4046 |
| 2042 | 319 | 32  | 4047 |
| 2043 | 319 | 32  | 4048 |
| 2047 | 324 | 32  | 4052 |
+------+-----+-----+------+

results_text:
+------+-----+-----+---------+
| rid  | qid | sid | answer  |
+------+-----+-----+---------+
| 249  | 342 | 32  | Cobol   |
| 311  | 342 | 32  | Fortran |
| 316  | 342 | 32  | Fortran |
+------+-----+-----+---------+

Wie krieg ich meinen bisherigen Ansatz noch erweitert, um results_text in dieser Form mit ins Ergebnis einfließen zu lassen?

UNION könnte Dir helfen.

Klingt nicht schlecht. Das probier ich eventuell mal aus. Je nachdem, wie Deine Antwort auf die Folgefrage ausfällt. ;-)

(Oder ist es gar keine so gute Idee, diese beiden unterschiedlichen Ergebnistypen auf Teufel komm raus in dieser Form miteinander verheiraten zu wollen?)

Ich würde diese beiden Typen *nicht* zusammenstellen.

Was wäre Dein alternativer Vorschlag? Lieber zwei getrennte Abfragen oder einfach nur die beiden Typen auf jeweils eigene Spalten im Ergebnis verteilen?

Viele Grüße

Alex