SQLite Frage

HeX0R · **Registriert:** 10.09.2004 09:59

Ich bräuchte mal einen SQL Spezi.

Folgendes Szenario:
Ich habe eine Tabelle mit einem timestamp und einem float, das kontinuierlich wächst.
Nun möchte ich das Anwachsen dieses Floats pro Tage ausgeben.

Beispiel:
Tag 1 00:01 -> 0.0
Tag 1 01:00 -> 0.5
Tag 1 12:00 -> 1.0
Tag 1 23:59 -> 1.5
Tag 2 05:00 -> 2.0
Tag 2 11:00 -> 3.5
Tag 2 16:00 -> 4.5
Tag 2 23:59 -> 6.0
Tag 3 01:00 -> 7.0
[...]

Ergebnis soll sein:
Tag 1 -> 1.5
Tag 2 -> 4.5

Ich habe versch. Lösungsansätze, die aber alle irgendwie lahm und unelegant sind (in der Realität habe ich bis zu 1440 Werte pro Tag), daher die Frage inwieweit mir sqlite dabei sogar helfen könnte?

Sicro · **Registriert:** 11.08.2005 19:08

Mehr Informationen über die Daten-Struktur wäre denk ich hilfreich.

Besteht der Timestamp nur aus Stunden und Minuten (ohne Tag 1, Tag 2 usw.)?
Sind es immer 4 Datensätze pro Tag?
Kommt der Timestamp "23:59" bei jedem Tagesende vor?

HeX0R · **Registriert:** 10.09.2004 09:59

Sicro hat geschrieben:

Mehr Informationen über die Daten-Struktur wäre denk ich hilfreich.

Besteht der Timestamp nur aus Stunden und Minuten (ohne Tag 1, Tag 2 usw.)?
Sind es immer 4 Datensätze pro Tag?
Kommt der Timestamp "23:59" bei jedem Tagesende vor?

Zu 1: Nein, sonst hätte ich geschrieben ich habe 3 Felder: Tag / timestamp / Float.
Es ist ein typischer Timestamp, also Sekunden seit 1.1.1970 (in diesem Fall sogar ms) und beinhaltet natürlich auch Jahr/Monat/Tag
Zu 2: Nein, das steht aber oben schon
Zu 3: Nein, es ist abhängig von der Anzahl Werte pro Tag.
Die bis zu 1440 Werte wären minütliche Werte (60 x 24), dann natürlich inklusive 23:59, es können aber auch nur 24 Werte sein, also stündlich.
Die wären auch nicht synchronisiert, es muss also nicht 01:00, 02:00 sein, sondern könnte auch 01:17, 02:17 sein.

Meine Ansätze bisher:

Ansatz 1 (aktuell mache ich es so):
Ich gehe alle Werte rückwärts durch, merke mir den Ausgangswert beim Tageswechsel und ziehe den ersten des Tages davon ab.
Nachteil: Ziemlich langsam, da ich eine enorme Menge Daten hier habe
Ansatz 2:
Ich wähle immer nur den letzten Wert und den ersten Wert eines Tages aus und ziehe die voneinander ab.
Nachteil: Das werden ganz schön viele Selects und bei der Menge an Daten dürfte das noch langsamer sein (noch nicht getestet), wobei mich interessieren grundsätzlich immer nur 7 Tage!
Ansatz 3:
Ich wähle jeweils nur die Daten eines Tages aus und gehe wie in Ansatz 1 vor, um die Min/Max Werte zu erhalten.
Nachteil: Bei 7 Tagen, die mich interessieren, bräuchte ich 7 Selects.
Aus dem Bauch heraus wäre das mein Favorit, aber evtl. gibt es ja bessere Methoden?

Sicro · **Registriert:** 11.08.2005 19:08

HeX0R hat geschrieben:

Zu 1: Nein, sonst hätte ich geschrieben ich habe 3 Felder: Tag / timestamp / Float.
Es ist ein typischer Timestamp, also Sekunden seit 1.1.1970 (in diesem Fall sogar ms) und beinhaltet natürlich auch Jahr/Monat/Tag

Naja, unter Timestamp versteht man viele Schreibweisen ("01:00" ist ebenso ein Timestamp), weshalb die Frage, ob die Tagesangabe zum Timestamp dazugehört durchaus berechtigt ist.
Eine wichtige Information, die du nun genannt hast, ist, dass der Timestamp ein Unix-Timestamp - jedoch in Millisekunden - ist.

HeX0R hat geschrieben:

Ansatz 2:
[...]wobei mich interessieren grundsätzlich immer nur 7 Tage!

Ebenfalls eine wichtige Information, die zuvor unbekannt war.
Die Frage ist nun, welche 7 Tage - die letzten der Datenbank?

Ich habe dir schnell ein Code geschrieben:

Code:

UseSQLiteDatabase()

Filename$ = GetTemporaryDirectory() + "Test.sqlite"

If CreateFile(0, Filename$)
  Debug "Database file created"
  CloseFile(0)
EndIf

If OpenDatabase(0, Filename$, "", "")
  Debug "Connected to Test.sqlite"
  If DatabaseUpdate(0, "CREATE TABLE info (timestamp TEXT, floatnumber REAL);")
    Debug "Table created"
  EndIf
EndIf

Debug "----------"
Date = Date()
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  1,  0,  1, 0) * 1000) + "', '0.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  1,  1,  0, 0) * 1000) + "', '0.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  1, 12,  0, 0) * 1000) + "', '1.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  1, 23, 59, 0) * 1000) + "', '1.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  2,  5,  0, 0) * 1000) + "', '2.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  2, 11,  0, 0) * 1000) + "', '3.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  2, 16,  0, 0) * 1000) + "', '4.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  2, 23, 59, 0) * 1000) + "', '6.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  3,  1,  0, 0) * 1000) + "', '7.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  4,  0,  1, 0) * 1000) + "', '7.1');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  4,  1,  0, 0) * 1000) + "', '7.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  5, 12,  0, 0) * 1000) + "', '8.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  5, 23, 59, 0) * 1000) + "', '8.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  6,  5,  0, 0) * 1000) + "', '9.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  7, 11,  0, 0) * 1000) + "', '9.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  8, 16,  0, 0) * 1000) + "', '10.5');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date),  9, 23, 59, 0) * 1000) + "', '11.0');")
Debug DatabaseUpdate(0, "INSERT INTO info VALUES ('" + Str(Date(Year(Date), Month(Date), 10,  1,  0, 0) * 1000) + "', '12.0');")
Debug "----------"

If DatabaseQuery(0, "SELECT STRFTIME('%j',DATETIME(timestamp/1000,'unixepoch')) AS days, floatnumber FROM info GROUP BY days LIMIT 7")
  If NextDatabaseRow(0)
    
    Day = GetDatabaseLong(0, 0)
    Debug "Tag 1 ----- " + GetDatabaseString(0, 1)
    
    While NextDatabaseRow(0)
      Debug "Tag " + Str(GetDatabaseLong(0, 0) - Day + 1) + " ----- " + GetDatabaseString(0, 1)
    Wend
    
  EndIf
EndIf

CloseDatabase(0)

Debug-Ausgabe:

Code:

[16:35:17] Database file created
[16:35:17] Connected to Test.sqlite
[16:35:17] Table created
[16:35:17] ----------
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] 1
[16:35:17] ----------
[16:35:17] Tag 1 ----- 1.5
[16:35:17] Tag 2 ----- 6.0
[16:35:17] Tag 3 ----- 7.0
[16:35:17] Tag 4 ----- 7.5
[16:35:17] Tag 5 ----- 8.5
[16:35:17] Tag 6 ----- 9.0
[16:35:17] Tag 7 ----- 9.5

HeX0R · **Registriert:** 10.09.2004 09:59

Das sieht schon mal gut aus, vielen Dank soweit :allright:

Ich habe das mal leicht verändert:

Code:

i = 1
If DatabaseQuery(0, "SELECT STRFTIME('%Y%m%d',DATETIME(timestamp/1000,'unixepoch')) AS days, floatnumber FROM info WHERE days >= '20170301' GROUP BY days LIMIT 8")
   While NextDatabaseRow(0)
      If i = 1
         PrevDay.d = GetDatabaseDouble(0, 1)
         Debug "Previous Day overall: " + StrD(PrevDay)
      Else
         Debug "Tag " + Str(i - 1) + " ----- " + StrD(GetDatabaseDouble(0, 1) - PrevDay)
         PrevDay = GetDatabaseDouble(0, 1)
      EndIf
      i + 1
   Wend

EndIf

Nur %j kann ich nicht machen, weil da auch Daten mehrerer Jahre drin liegen können, dann würden sich die Jahrestage überschneiden und das Ergebnis verfälschen.
Das WHERE ... habe ich dazugebastelt, damit ich den Anfangspunkt leichter setzen kann, ginge natürlich auch mit der timestamp an sich.
LIMIT musste ich auf 8 erhöhen, damit ich den Wert des Tages vor dem, der mich als erstes interessiert bekomme.
Und jetzt gibt es auch das aus, was ich eigentlich haben wollte, den Tagesanstieg des floats.

Das einzige, was ich hier nicht verstehe:
Wenn ich die Werte über den Tag gruppiere, wieso kommt dann immer der letzte Tageswert heraus? Könnte doch theoretisch auch der erste genommen werden?
Es ist ja gut, dass es so ist, weil genau den letzten Tageswert brauche ich ja, aber den Grund sehe ich nicht.

Sicro · **Registriert:** 11.08.2005 19:08

HeX0R hat geschrieben:

Das einzige, was ich hier nicht verstehe:
Wenn ich die Werte über den Tag gruppiere, wieso kommt dann immer der letzte Tageswert heraus? Könnte doch theoretisch auch der erste genommen werden?
Es ist ja gut, dass es so ist, weil genau den letzten Tageswert brauche ich ja, aber den Grund sehe ich nicht.

Die Datenbank-Engine geht ALLE Datensätze durch, gruppiert sie und limitiert das Ergebnis dann auf 8 Datensätze.

Vielleicht wird es dir durch diesen Beispiel-Code verständlicher:

Code:

; Datensätze gruppieren
NewMap Groups$()
Groups$("01.03.2017") = "A1"
Groups$("01.03.2017") = "A2"
Groups$("02.03.2017") = "B1"
Groups$("03.03.2017") = "C1"
Groups$("03.03.2017") = "C2"

; Ausgabe
ForEach Groups$()
  Debug MapKey(Groups$()) + " -- " + Groups$()
Next

Wie sieht es mit der Geschwindigkeit aus? Das würde mich mal interessieren

HeX0R · **Registriert:** 10.09.2004 09:59

O.k. das mit dem Gruppieren habe ich jetzt auch verstanden, danke für das sehr einleuchtende Beispiel!

Geschwindigkeit ist für meine Zwecke nun perfekt.
Muss mal abwarten, wie das wird, wenn die Datenbank richtig fett wird.

Das Ganze läuft übrigens unter Android...

Kiffi · **Registriert:** 08.09.2004 08:21 **Wohnort:** Amphibios 9

HeX0R hat geschrieben:

Das Ganze läuft übrigens unter Android...

wie das?

Neugierig ... Peter

HeX0R · **Registriert:** 10.09.2004 09:59

Du wirst es wohl schon ahnen, mit Basic4Android. :mrgreen:

Kiffi · **Registriert:** 08.09.2004 08:21 **Wohnort:** Amphibios 9

HeX0R hat geschrieben:

Du wirst es wohl schon ahnen, mit Basic4Android. :mrgreen:

och schade, und ich dachte schon...

PureBoard

SQLite Frage

Wer ist online?