SQLite SQLite Select für View vereinfachen/optimieren

[godi]

Hallo,

ich habe folgendes (Test-) Schema (für SQLite):

ROP TABLE IF EXISTS "Datein";
CREATE TABLE "Datein" ("DateiID" INTEGER PRIMARY KEY  NOT NULL , "DatenID" INTEGER NOT NULL );
INSERT INTO "Datein" VALUES(1,2);
INSERT INTO "Datein" VALUES(2,3);
INSERT INTO "Datein" VALUES(3,5);
INSERT INTO "Datein" VALUES(4,1);
INSERT INTO "Datein" VALUES(5,4);
INSERT INTO "Datein" VALUES(6,8);
INSERT INTO "Datein" VALUES(7,7);
INSERT INTO "Datein" VALUES(8,6);
DROP TABLE IF EXISTS "Daten";
CREATE TABLE "Daten" ("DatenID" INTEGER PRIMARY KEY  NOT NULL , "Wert1" FLOAT, "Wert2" FLOAT, ZeitInSec);
INSERT INTO "Daten" VALUES(1,5,10,10);
INSERT INTO "Daten" VALUES(2,20,30,50);
INSERT INTO "Daten" VALUES(3,5,10,30);
INSERT INTO "Daten" VALUES(4,0,0,10);
INSERT INTO "Daten" VALUES(5,10,10,100);
INSERT INTO "Daten" VALUES(6,20,30,120);
INSERT INTO "Daten" VALUES(7,10,10,80);
INSERT INTO "Daten" VALUES(8,20,30,200);
DROP TABLE IF EXISTS "OrdnerMapping";
CREATE TABLE "OrdnerMapping" ("OrdnerID"  NOT NULL , "DateiID" INTEGER NOT NULL );
INSERT INTO "OrdnerMapping" VALUES(1,1);
INSERT INTO "OrdnerMapping" VALUES(1,3);
INSERT INTO "OrdnerMapping" VALUES(1,5);
INSERT INTO "OrdnerMapping" VALUES(1,7);
INSERT INTO "OrdnerMapping" VALUES(2,2);
INSERT INTO "OrdnerMapping" VALUES(2,4);
INSERT INTO "OrdnerMapping" VALUES(2,6);
INSERT INTO "OrdnerMapping" VALUES(2,8);

Nun möchte ich für jeden Ordner eine Übersicht erstellen wo aus den Daten der Wert1 summiert werden soll und über den Wert2 soll ein Durchschnitt berechnet werden.
Der Durchschnitt soll aber nicht einfach mit avg berechnet werden sondern Wert2 soll mit der Zeit gewichtet werden.
ZB:
Datensatz1: Wert2 = 10; Zeit = 5;
Datensatz2: Wert2 = 30; Zeit = 10;
Datensatz3: Wert2 = 0; Zeit = 20;
Die Gesamtzeit ist die Summe aller Zeit wo Wert2 größer ist als 0. => 15
=> Durchschnitt = 10*5/15 + 30*10/15 = 23.33

Dies will ich jetzt in SQL umsetzen und in einer View darstellen.

Dazu habe ich zuerst ein DatenOrdnerMapping erstellt:

CREATE VIEW DatenOrdnerMapping  as
select om.OrdnerID, DatenID from OrdnerMapping om, Datein d where om.DateiID = d.DateiID

Und jetzt die Übersicht:

CREATE VIEW Uebersicht as 
select dom.OrdnerID, sum(d.Wert1) summe, av.durchschnitt durchschnitt from DatenOrdnerMapping dom, Daten d,
(Select OrdnerID, sum(d.Wert2 * d.ZeitInSec)/sum(d.ZeitInSec) durchschnitt  from DatenOrdnerMapping dom, Daten d where d.Wert2>0 and d.DatenID IN(dom.DatenID) group by OrdnerID) as av
 where dom.OrdnerID = av.OrdnerID and d.DatenID IN(dom.DatenID) group by dom.OrdnerID

Soweit funktioniert dies auch aber kann die Übersicht einfacher erstellt werden?
Vorallem habe ich in der realen DB mehrere Durchschnittswerte pro Ordner so zu berechnen.
=> mehrere Subqueries => sehr lange query

godi

 

[OlliL]

Hi,

was hällst du davon?

[sql]
SELECT dom.OrdnerID
,SUM(d.Wert1) summe
,SUM(d.Wert2 * d.ZeitInSec)/SUM(d.ZeitInSec)
FROM DatenOrdnerMapping dom
,Daten d
WHERE d.DatenID = dom.DatenID
AND d.Wert2>0
GROUP BY dom.OrdnerID ;
[/sql]

 

[godi]

Hallo,

ich habe gestern noch ein wenig auf meiner "realen" Datenbank herumprobiert und habe es jetzt ähnlich zu deiner Lösung. 🙂
Jedoch habe ich es so gemacht, dass in der View DatenOrdnerMapping alle Daten angezeigt werden.

CREATE VIEW DatenOrdnerMapping  AS
SELECT om.OrdnerID, * FROM OrdnerMapping om, Datein d, Daten WHERE om.DateiID = d.DateiID and d.DatenID = Daten.DatenID

Dadurch habe ich mir in der Übersicht den Cross Join erspart => Aufruf der Übersicht wurde schneller.

Die Durchschnittsberechnung musste ich in einen left join auslagern, damit ich von mehreren Werten den Durchschnitt berechnen kann, auch wenn dieser null ergibt.

CREATE VIEW Uebersicht AS 
select * from ( 
select * from ( 

select OrdnerID, 
-- SUM, MAX
sum(Wert1) summe, max(Wert3) maximum
from DatenOrdnerMapping group by OrdnerID) as m left join 

-- average wert2
(Select OrdnerID OrdnerID2, sum(Wert2 * ZeitInSec)/sum(ZeitInSec) durchschnitt2 from DatenOrdnerMapping 
  where Wert2 > 0 group by OrdnerID) as av2

ON m.OrdnerID = OrdnerID2) as m left join

-- average wert2
(Select OrdnerID OrdnerID3, sum(Wert3 * ZeitInSec)/sum(ZeitInSec) durchschnitt3 from DatenOrdnerMapping 
  where Wert3 > 0 group by OrdnerID) as av3

ON m.OrdnerID = OrdnerID3

Hier nochmal die Daten mit 3 Werten:

DROP TABLE IF EXISTS "Daten";
CREATE TABLE "Daten" ("DatenID" INTEGER PRIMARY KEY  NOT NULL , "Wert1" FLOAT, "Wert2" FLOAT,  "Wert3" FLOAT, ZeitInSec);
INSERT INTO "Daten" VALUES(1,5,10,20,10);
INSERT INTO "Daten" VALUES(2,20,30,50,50);
INSERT INTO "Daten" VALUES(3,5,10,100,30);
INSERT INTO "Daten" VALUES(4,0,0,90,10);
INSERT INTO "Daten" VALUES(5,10,10,80,100);
INSERT INTO "Daten" VALUES(6,20,30,70,120);
INSERT INTO "Daten" VALUES(7,10,10,60,80);
INSERT INTO "Daten" VALUES(8,20,30,90,200);

Was mich jetzt noch interessieren würde:
Ist es möglich mit SQL Funktionen zu erstellen? (Wenn ja wie?)
Dann wäre es ja möglich die Durchschnittsberechnung in eine eigene Funktion zu Packen.
Die Übersicht würde ich auch gerne in eine eigene Funktion packen und der Funktion dann DatenOrdnerMapping übergeben. Somit könnte ich auch ein anderes DatenOrdnerMapping mit der selben View auswerten.

godi

 

[OlliL]

Die Frage ist, willst du was performantes? 😉

SELECT dom.OrdnerID
      ,sum(d.Wert1) summe
      ,CASE WHEN WERT2 > 0 THEN sum(d.Wert2 * d.ZeitInSec)/sum(d.ZeitInSec) END
      ,CASE WHEN WERT3 > 0 THEN sum(d.Wert3 * d.ZeitInSec)/sum(d.ZeitInSec) END
  FROM datein di
      ,OrdnerMapping dom
      ,Daten d
 WHERE dom.dateiid = di.dateiid
   AND di.datenid = d.datenid
 GROUP BY dom.OrdnerID ;

Man vergleiche zu Zugriffspfade unter (MySQL... an Ermangelung einer SQLite DB)

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
|  1 | SIMPLE      | dom   | ALL    | NULL          | NULL    | NULL    | NULL             |    8 | Using temporary; Using filesort |
|  1 | SIMPLE      | di    | eq_ref | PRIMARY       | PRIMARY | 4       | test.dom.DateiID |    1 |                                 |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | test.di.DatenID  |    1 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+

dein query:

+----+-------------+------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref             | rows | Extra                           |
+----+-------------+------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL            |    2 |                                 |
|  1 | PRIMARY     | <derived5> | ALL    | NULL          | NULL    | NULL    | NULL            |    2 |                                 |
|  5 | DERIVED     | om         | ALL    | NULL          | NULL    | NULL    | NULL            |    8 | Using temporary; Using filesort |
|  5 | DERIVED     | d          | eq_ref | PRIMARY       | PRIMARY | 4       | test.om.DateiID |    1 |                                 |
|  5 | DERIVED     | daten      | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.DatenID  |    1 | Using where                     |
|  2 | DERIVED     | <derived3> | ALL    | NULL          | NULL    | NULL    | NULL            |    2 |                                 |
|  2 | DERIVED     | <derived4> | ALL    | NULL          | NULL    | NULL    | NULL            |    2 |                                 |
|  4 | DERIVED     | om         | ALL    | NULL          | NULL    | NULL    | NULL            |    8 | Using temporary; Using filesort |
|  4 | DERIVED     | d          | eq_ref | PRIMARY       | PRIMARY | 4       | test.om.DateiID |    1 |                                 |
|  4 | DERIVED     | daten      | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.DatenID  |    1 | Using where                     |
|  3 | DERIVED     | om         | ALL    | NULL          | NULL    | NULL    | NULL            |    8 | Using temporary; Using filesort |
|  3 | DERIVED     | d          | eq_ref | PRIMARY       | PRIMARY | 4       | test.om.DateiID |    1 |                                 |
|  3 | DERIVED     | daten      | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.DatenID  |    1 |                                 |
+----+-------------+------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+

Aber... das ist halt MySQL. Irgendwo meine ich mal gelesen/gehört zu haben, das Joins unter SQLite grausam langsam sind... da muss man sich ggf. noch was ganz anderes überlegen.

 

[godi]

Na gut dann muss ich wohl noch mal optimieren und probieren. 😉
Für SQL / Datenbanken braucht man anscheinend viel Erfahrung 😉

Was sagen eigentlich die Zugriffspfade aus?
Ok, ich denke mal das meine query ziemlich langsam ist da derived und primary selected_types vorkommen und bei dir nur simple. 😉

Aber vielen Dank! 🙂