Die Berechnung mehrerer Spalten Durchschnitt in SQLite3

Muss ich Durchschnitt einige Werte in einer Zeile-Weise Mode, sondern als eine Spalte-wise-Mode. (Wenn ich waren gerade dabei, eine spaltenweise Durchschnitt, ich konnte einfach avg()). Meine konkrete Anwendung dieser verlangt von mir ignorieren Null-Werte in die Mittelung. Es ist ganz einfach Logik, aber es scheint unheimlich schwierig zu tun in SQL. Gibt es einen eleganten Weg das zu tun, meine Berechnung?

Ich bin mit SQLite3, für was es Wert ist.

Details

Wenn Sie weitere Einzelheiten benötigen, hier ist eine illustration:

Ich habe eine Tabelle mit einer übersicht:

| q1 | q2    | q3    | ... | q144 |
|---- | ------- | ------- | ----- | ------|
| 1  | 3     | 7     | ... | 2    |
| 4  | 2     | NULL  | ... | 1    |
| 5  | NULL  | 2     | ... | 3    |

(Das sind nur einige Beispiel-Werte und einfachen Spaltennamen. Gültige Werte sind 1 bis 7 und NULL.)

Muss ich rechnen einige Durchschnitte etwa so:

q7 + q33 + q38 + q40 + ... + q119 / 11 as domain_score_1
q10 + q11 + q34 + q35 + ... + q140 / 13 as domain_score_2
...
q2 + q5 + q13 + q25 + ... + q122 / 12 as domain_score_14

...aber ich brauche, um zu ziehen aus der null-Werte und Durchschnittliche auf der Grundlage der nicht-null-Werte. So, für domain_score_1 (die 11 items), ich würde tun müssen:

Input:  3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1

(3 + 5 + 7 + 2 + 3 + 1 + 5 + 1) / (11 - 3)
27 / 8
3.375

Einen einfachen Algorithmus, die ich überlege ist:

Eingang:

3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1

Verschmelzen Sie jeden Wert auf 0, wenn Sie NULL:

3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1

Summe:

Bekommen, die Anzahl der nicht-Nullen durch die Konvertierung von Werten > 0 bis 1 und die Summe:

3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1
1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 1
8

Teilen Sie diese beiden Nummern

27 / 8
3.375

Aber das scheint wie eine Menge mehr Programme als das nehmen sollte. Gibt es einen eleganten Weg dies zu tun, die ich nicht kenne?

Update:

Es sei denn, ich bin Missverständnis, etwas, avg() nicht für diese Arbeit. Beispiel für das was ich machen möchte:

select avg(q7, q33, q38, ..., q119) from survey;

Ausgabe:

SQL error near line 3: wrong number of arguments to function avg()

Ich denke, dass die DB in Ihrer form nicht normalisiert, so dass es nicht leicht ist, Weise zu manipulieren die Daten in einer "set-based" - Weg.
Ich habe aktualisiert, meine Antwort entsprechend den update Bedenken, mit AVG.
In Bezug auf die Normalisierung betrifft, dies ist, wie die Daten in der aktuellen Datenbank vorhanden. (Ich habe nicht design es-im ernst, 144+ Spalten? - aber ich meine Schlachten.) Ich kann nur den sauren Apfel beißen und etwas schreiben zu normalisieren, dies zu tun, die Verarbeitung.

InformationsquelleAutor Benjamin Oakes | 2010-03-30

4

In standard-SQL
```
SELECT 
(SUM(q7)+SUM(q33)+SUM(q38)+SUM(q40)+..+SUM(q119))/
(COUNT(q7)+COUNT(q33)+COUNT(q38)+COUNT(q40)+..+COUNT(q119)) AS domain_score1 
FROM survey
```
würden Sie geben, was Sie wollen, SUMME verbinden sich zu 0, wenn Sie null und der Zähler nicht zählen Null.
(hoffe SQLite3 entspricht).

EDIT: Überprüft die http://www.sqlite.org/lang_aggfunc.html und SQLite entspricht; wenn (Summe) gehen zu überlaufen könnten Sie Summe() statt.

Auch ich schließe mich den Meinungen der re-Normalisierung, wenn Sie nicht normalisieren, Ihr Tisch-design (und immer, wenn du Spalten mit zahlen in Ihrem Namen eine rote fahne) Sie sind nicht zu elegant SQL.
- Ich landete mit etwas ähnlich zu dem, was Sie vorgeschlagen. (Für andere war es (coalesce(#{question}, 0) + ...) / ((case when #{question} > 0 then 1 else 0 end) + ...).) Ich möchte zu normalisieren, aber es ist nicht lohnt sich an dieser Stelle-dies wird wohl das Letzte mal, als wir jemals Blick auf diese Daten. (Ich ging hin und her für eine Weile und entschied, dass das hack war eine bessere Wahl in diesem Fall.)
InformationsquelleAutor Unreason
4

AVG schon ignoriert null-Werte, und tut, was Sie wollen:

Die avg () - Funktion gibt den Mittelwert aller nicht-NULL-X in einer Gruppe. String-und BLOB-Werte, die nicht Aussehen wie zahlen werden interpretiert als 0. Das Ergebnis von avg() ist immer ein Gleitkomma-Wert solange es mindestens einen nicht-NULL-Eingabe, selbst wenn alle Eingaben ganze zahlen sind. Das Ergebnis von avg() ist NULL, wenn und nur wenn es keine nicht-NULL-Eingänge.

Vom http://www.sqlite.org/lang_aggfunc.html

So können Sie wahrscheinlich die Werte, die Sie haben pro domain und laden Sie Sie in einer anderen Tabelle und dann einfach laufen Mittelwerte in der Tabelle. Oder Sie könnten nur unpivot Ihre Breite Tabelle, und führen Sie Durchschnitte auf, dass auch.

AVG arbeitet auf den Spalten, nicht Zeilen. Also, wenn Sie unpivoted Ihrer Tabelle, die Sie verwenden könnten AVG und nicht das problem, das Sie gegenüberstellen. Schauen wir uns ein kleines Beispiel:

Haben Sie eine Tabelle und es sieht wie folgt aus:
```
ID  | q1  | q2  | q3
----------------------
1   | 1   | 2   | NULL
2   | NULL| 2   | 56
```
Wollen Sie Durchschnitt q1 und q2 zusammen, weil Sie in der gleichen Domäne, aber Sie sind separate Spalten, so können Sie es nicht. Aber wenn Sie verändert Ihre Tabelle wie folgt Aussehen:
```
ID  | question | value
-----------------------
1   | 1        | 1
1   | 2        | 2
1   | 3        | NULL
2   | 1        | NULL
2   | 2        | 2
2   | 3        | 56
```
Dann könnte man den Durchschnitt der beiden Fragen ganz einfach:
```
SELECT AVG(value)
FROM Table
WHERE question IN (1,2)
```
Und Sie können die group by ID, wenn Sie möchten, eine Durchschnittliche pro-ID, anstatt einen globalen Durchschnitt:
```
SELECT ID, AVG(value)
FROM Table
WHERE question IN (1,2)
GROUP BY ID
```
- Es sei denn, ich missunderstand, avg() löst ein anderes problem. Ich aktualisiere die Frage.
- Es tut lösen ein anderes problem, so dass ich Vorschlage, die Sie konvertieren Sie Ihre problem in die eine, die AVG löst, indem unpivoting Ihren Tisch.
- Ah, ich verpasst, dass. Vielen Dank für das update-es sieht aus wie das, was ich suchte.
- Er ignoriert es. Siehe das, was ich zitiert ganz oben auf meine Antwort. Die Möglichkeit, die SQL-Aggregat-Funktionen definiert, nicht nur spezifisch SQLite.
- Also, warten... gibt es einen einfachen Weg, um die pivot-Tabelle wie du Sie erwähnt hast? Ich bin nicht etwas zu finden, einfach.
- Es ist nicht wahrscheinlich, eine einfache Möglichkeit, es zu tun in SQLite. (Es ist in der SQL Server 2005 - ich weiß, dass sehr viel - nicht, dass das hilft dir hier.) Aber je nachdem, wie diese Tabelle wird aufgefüllt, es sollte eine Frage der änderung des Modells und ändern die Weise, die es bevölkerten.
- Ja, ich sah, dass bei der Suche um... ich war versucht, etwas zu tun mit information_schema, aber es ist nicht wirklich Arbeit aus.
InformationsquelleAutor Welbog
2

Dies ist eine monströse Abfrage, aber Sie könnten dies tun:
```
SELECT AVG(q) FROM
((SELECT q7 AS q FROM survey) UNION ALL
(SELECT q33 FROM survey) UNION ALL
(SELECT q38 FROM survey) UNION ALL
...
(SELECT q119 FROM survey))
```
Dieser wandelt Ihre Spalten in Zeilen und nutzt die AVG() Funktion.

Natürlich, werden Sie wahrscheinlich wollen, dass diese nur für eine bestimmte Umfrage aufnehmen, also vergessen Sie nicht die WHERE-Klausel:
```
SELECT AVG(q) FROM
((SELECT q7 AS q FROM survey WHERE survey_id = 1) UNION ALL
(SELECT q33 FROM survey WHERE survey_id = 1) UNION ALL
(SELECT q38 FROM survey WHERE survey_id = 1) UNION ALL
...
(SELECT q119 FROM survey WHERE survey_id = 1))
```
Hätten Sie eine viel einfachere Zeit, wenn Sie normalisiert die q Spalten in einer eigenen Tabelle, mit einer Frage pro Zeile, und Referenzen zurück zur übersicht. Sie haben eine 1 zu N Beziehung zwischen Umfrage und Frage.
- AVG ist definiert durch die SQL-Norm zu ignorieren Null-Werte während der Berechnung den Durchschnitt einer Spalte. Auch die Dokumentation für SQLite zeigt deutlich, dass AVG ignoriert Null-Werte, so dass selbst wenn es nicht der standard, wäre es immer noch hier bewerben. Also bitte stoppen Sie die Verbreitung von Fehlinformationen wie dieser.
- Fixe Antwort, wie Sie sagten, AVG() funktioniert.
InformationsquelleAutor Marcus Adams
1

Verwenden Sie eine separate Tabelle zu speichern Umfrage Punktzahlen für unterschiedliche Fragen (unter der Annahme, dass q ' s sind da in Frage). So etwas wie folgenden
```
SurveyTable(SurveyId, ...)
SurveyRatings(SurveyId, QuestionId, Rating)
```
Danach können Sie die Abfrage ausführen, wie
```
SELECT avg(Rating) WHERE SurveyId=?
```
InformationsquelleAutor Babar

Verwenden:

SELECT AVG(x.answer)
  FROM (SELECT s.q7 AS answer
          FROM SURVEY s
        UNION ALL
        SELECT s.q33
          FROM SURVEY s
        UNION ALL    
       SELECT s.q38
         FROM SURVEY s
       ...
       UNION ALL
       SELECT s.q119
         FROM SURVEY s) x

Nicht verwenden UNION - Sie wollen Duplikate, wenn Sie vorhanden sind.

InformationsquelleAutor OMG Ponies

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.