SQLite SQLite Select für View vereinfachen/optimieren

G

godi

Aktives Mitglied
Hallo,

ich habe folgendes (Test-) Schema (für SQLite):
SQL: 
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:
SQL: 
CREATE VIEW DatenOrdnerMapping  as
select om.OrdnerID, DatenID from OrdnerMapping om, Datein d where om.DateiID = d.DateiID

Und jetzt die Übersicht:
SQL: 
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
 
O

OlliL

Bekanntes Mitglied
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]
 
G

godi

Aktives Mitglied
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.
SQL: 
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.

SQL: 
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:
SQL: 
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
 
O

OlliL

Bekanntes Mitglied
Die Frage ist, willst du was performantes? ;)

SQL: 
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)

Code: 
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| 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:

Code: 
+----+-------------+------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+
| 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.
 
G

godi

Aktives Mitglied
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! :)
 
Ähnliche Java Themen
  Titel Forum Antworten Datum
T sqlite select Datenbankprogrammierung 12
T SQLite Alternativen zu SQlite, dateibasiert, leicht verschlüsselbar, Nutzerverwaltung Datenbankprogrammierung 21
B SQLite + jdbc + IntelliJ-Consumer = "No suitable driver found..." Datenbankprogrammierung 15
Maxim6394 JPA 3.2 & SQLite - LocalDateTime wird falsch geladen Datenbankprogrammierung 1
Maxim6394 EclipseLink + SQLite | Unable to acquire a connection from driver [null] Datenbankprogrammierung 6
J SQLite Abfrage fehlerhaft - komme nicht weiter - please help. Datenbankprogrammierung 3
thor_norsk SQLite Fehlermeldung Datenbankprogrammierung 4
N JDBC SQLITE und Cascading Datenbankprogrammierung 2
B SQlite Datenbank, trotz Statements wurden nicht alle Zeilen erzeugt? Datenbankprogrammierung 35
B SQLite Befehl bauen? Datenbankprogrammierung 4
D SQLite Datenbank in Android Studio (Java) durchsuchen Datenbankprogrammierung 3
thobren Projekt SQlite! Wie kann ich auf auf SQlite Daten zugreifen? Datenbankprogrammierung 4
Davee SQLite SQLite Datenbank lässt sich nicht auf anderen PCs öffnen Datenbankprogrammierung 8
B Wie kopieren ich eine Spalte von einer Tabelle in eine andere Tabelle SQLite durch java code? Datenbankprogrammierung 26
D SQLite Collections oder Arrays in SQLite abbilden Datenbankprogrammierung 7
N ORM für Sqlite Datenbankprogrammierung 4
M SQLite Datenbank mit SQLite Datenbankprogrammierung 7
N Sqlite DB mit Java wird auf Linuxsystem nicht gefunden Datenbankprogrammierung 9
N SQLite Datenbankprogrammierung 2
S Daten von SQLite Datenbank nutzen Datenbankprogrammierung 5
B SQLite Frage zu SQLite Datenbankverbindung Datenbankprogrammierung 7
E Sqlite-jdbc Mitliefern Datenbankprogrammierung 4
X Sqlite Fks Datenbankprogrammierung 4
C JDBC und SQLite Datenbank Datenbankprogrammierung 8
X SQLite SQLite Programm beendet/führt nicht weiter aus Datenbankprogrammierung 12
Sam96 SQLite mit JavaFX Datenbankprogrammierung 1
V SQLite Performance: 1 Datei mit einzelnen Einträgen gegenüber SQLite Datenbankprogrammierung 7
F Java SQLite Error Datenbankprogrammierung 19
F Sqlite cannot commit Datenbankprogrammierung 2
H SQLite Sqlite Datenbank direkt einbinden. Datenbankprogrammierung 5
U Dom Parser und SQLite füllen Datenbankprogrammierung 5
D SQLite Datenkbank auf WebServer möglich? Datenbankprogrammierung 4
M Datenbankausgabe .jsp per SQLite Datenbankprogrammierung 7
J SQLite Login Datenbank Datenbankprogrammierung 2
M SQLite Einstieg mit SQLite, wohin mit der DLL? Datenbankprogrammierung 7
M SQLite Speicherpfad Datenbankprogrammierung 0
G sqlite innerjoin Datenbankprogrammierung 5
G SQLite Daten aus SQLite DB in andere SQLite DB importieren Datenbankprogrammierung 4
R sqlite UPDATE wirkt nicht aus Java Datenbankprogrammierung 7
G SQLite SQLite Abfrage Datenbankprogrammierung 4
F SQLite-Extensions unter Java Datenbankprogrammierung 2
H SQLite mit DefaultTableModel synchronisieren Datenbankprogrammierung 5
D SQLite Statement nimmt keine Namen aus getter-Methoden Datenbankprogrammierung 11
L SQLite fügt nur den ersten Datensatz ein Datenbankprogrammierung 2
S SQLite Ausführbares Jar mit SQLite DB Datenbankprogrammierung 4
F [SQLite] Mehrere Datensätze einfügen Datenbankprogrammierung 12
H SQLite Datenkbank erstellen Datenbankprogrammierung 3
S Abfrage auf SQLite-DB Datenbankprogrammierung 2
Kasoki SQLite SQLite oder doch XML!? Datenbankprogrammierung 2
G SQLite Abfrage, ob in Tabelle X Spalte Y existiert Datenbankprogrammierung 4
G SQLJet (SQLite) - Mehrbenutzerzugriff auf Datenbank handhaben Datenbankprogrammierung 1
S SQLite in JAR Datenbankprogrammierung 8
J SQLite --> Java SDK Datenbankprogrammierung 7
P Datenbank für Java Anwendung wie SQLite ohne Installation Datenbankprogrammierung 4
P Sqlite API für JAVA ? Datenbankprogrammierung 9
feuervogel SQLite unter Linux mit Eclipse einrichten Datenbankprogrammierung 8
K SQLite Datenbankprogrammierung 5
S SQLite oder RDBMS als Datei(nicht Client/Server) Datenbankprogrammierung 5
I Hibernate / JPA - Spaltenname von Query (Select) bekommen Datenbankprogrammierung 6
OnDemand Select * from bringt keine Rückgabe Datenbankprogrammierung 49
OnDemand Select vs Update ins blaue, was ist teurer? Datenbankprogrammierung 11
M JPA: select all mit unterschiedlichem Tablename Datenbankprogrammierung 2
A MySQL Select und Insert in Java Datenbankprogrammierung 15
J JPA: Wie sieht der select aus? Datenbankprogrammierung 2
D geänderte SELECT Abfragen Datenbankprogrammierung 15
N SQLite Hibernate und Aufruf von Funktion SELECT last_insert_rowid() Datenbankprogrammierung 2
D Oracle NullPointerException bei select mit Null Values Datenbankprogrammierung 5
F MySQL Was bedeuten die Einzelnen Zeichen in Select Querrys? Datenbankprogrammierung 1
J SQL SELECT mit einem Array Datenbankprogrammierung 1
J SELECT Abfrage/Suche Datenbankprogrammierung 4
E Kann man in einer if-Bedingung auch SELECT-Statements überprüfen? Datenbankprogrammierung 23
M SQL-Exception trotz funktionierendem SELECT Datenbankprogrammierung 4
U PostgreSQL SELECT Statement Datenbankprogrammierung 5
L Select Anweisung wird falsch interpretiert Datenbankprogrammierung 3
C HSQLDB Platzhalter in SELECT Datenbankprogrammierung 6
I SELECT bei Datenbankverbindung Datenbankprogrammierung 6
C MySQL JPA - namedQuery (SELECT) -getResultList() - liefert falsches Ergebnis Datenbankprogrammierung 1
S select: alle ergebnisse ermitteln Datenbankprogrammierung 5
C SQL-Statement SELECT über 3 Tabellen Datenbankprogrammierung 5
H HSQLDB insert .... values("test1",select test2 from foo) Datenbankprogrammierung 2
D JDBC insert mit select abfrage Datenbankprogrammierung 5
W SELECT oder Programm-Logik Datenbankprogrammierung 10
J Hibernate Select auf Parameterliste Datenbankprogrammierung 3
T MySQL Dynamisch Suchen Select Datenbankprogrammierung 4
D ArrayList in Select Datenbankprogrammierung 12
N Select mit join Datenbankprogrammierung 3
F Oracle select sum() join select sum() Datenbankprogrammierung 9
B SELECT ja - INSERT nein (MySQL) Datenbankprogrammierung 3
R Oracle Performance bei SELECT mit vielen Reihen Datenbankprogrammierung 5
T MySQL Db select * und Insert schlagen fehl Datenbankprogrammierung 2
I Select-Statement optimieren Datenbankprogrammierung 14
M Variable in SQL SELECT * FROM ... WHERE mithilfe von PreparedStatement benutzen? Datenbankprogrammierung 28
R Derby/JavaDB Möglichkeit von einem Random Select! Datenbankprogrammierung 6
B Mehrfachschachtelung SELECT Datenbankprogrammierung 2
T MySQL Select: Zusammenfassen von Daten und bilden von Durchschnitt? Datenbankprogrammierung 4
GianaSisters MySQL Datenbank Select error Datenbankprogrammierung 5
R Select Statement als temporärer Table Datenbankprogrammierung 7
R Derby/JavaDB Select Statement Where bedingung will nicht ganz! Datenbankprogrammierung 4
R Derby/JavaDB Select TOP Statement geht nicht Datenbankprogrammierung 3
S MySQL Problem mit SELECT bzw encoding Datenbankprogrammierung 4

Ähnliche Java Themen

Aktuelle Jobs

Neue Themen


Oben