MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > SQL
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 19.06.2017, 13:58   #1
sonic8
MOF Profi
MOF Profi
Standard Hinweis - Min/Max für aufeinanderfolgende Datensätze

Ein Zustandsprotokoll von Geräten enthält pro Datensatz die Informationen:
Gerät (Item), Zustand (State), Zeitpunkt (EventTime).
2017-06-19_14h41_13.png

Jetzt soll aus diesen Informationen per SQL eine Auswertung erstellt werden, aus der pro Tag die folgenden Fragen beantwortet werden.
  • Welche Zustände hatte ein Gerät im Laufe des Tages?
  • Wie lange war es mindestens in dem jeweiligen Zustand?
    (Mindestens heißt, dass Zeiträume zwischen zwei Zuständen ignoriert werden sollen, weil sie keinen Zustand unzweifelhaft zugeordnet werden können.)
  • Wann war es zuletzt in dem Zustand?
Zusätzlich zu den direkt aus den Daten hervorgehende Informationen, sollen folgende Annahme in die Auswertung einfließen.

Egal wann die erste Zustandsmeldung eines Gerätes protokolliert wurde, soll angenommen werden, dass der dort gemeldete Zustand bereits seit Beginn des Tages (00:00 Uhr) besteht.

Egal wann die letzte Zustandsmeldung eines Gerätes protokolliert wurde, soll angenommen werden, dass der dort gemeldete Zustand bis zum Ende des Tages (23:59 Uhr) weiter besteht.

Das Ergebnis soll dann ungefähr so aussehen:
2017-06-19_14h42_08.png
Das kritische Problem bei der Sache ist bei Item 1 der Wechsel der Zustände AAA->BBB->AAA. Damit kann für AAA nicht einfach MIN/MAX+GROUP BY über Item+State zur Ermittlung der Zeiten und darauf basierend der Dauer erfolgen.

Die Anlage _test_data.txt enthält SQL zur Erstellung eine Beispieltabelle und den Daten aus dem obigen Screenshot.

Das verwendete DBMS ist MySQL, ich habe das bewusst nicht angegeben, weil ich mit allem halbwegs standardnahem SQL zurechtkommen sollte.
Angehängte Dateien
Dateityp: txt _test_data.txt (1,4 KB, 1x aufgerufen)
sonic8 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.06.2017, 14:04   #2
sonic8
Threadstarter Threadstarter
MOF Profi
MOF Profi
Standard

Dies ist mein bisheriger Lösungsansatz. Die Problemstellung mit den substituierten Anfangs-/Endzeitpunkten pro Tag ist darin gelöst.
Aber eben nicht Berechnung der Dauer, wenn es Zustände mehrfach mit Unterbrechung gab.

Code:

SELECT  t.Item 
        ,t.State
        ,CASE WHEN NOT EXISTS (SELECT 'x' 
                                FROM test_data t2
                                WHERE DATE(t2.EventTime) = DATE(t.EventTime)
                                AND t2.EventTime < t.EventTime
                                AND t2.Item = t.Item
                                )
                  THEN CONVERT(CONCAT(DATE(t.EventTime) , ' 00:00'), DATETIME)
                  ELSE (SELECT MIN(EventTime)
                         FROM test_data t6
                        WHERE DATE(t6.EventTime) = DATE(t.EventTime)
                          AND t6.EventTime < t.EventTime
                          AND t6.Item = t.Item
                          AND NOT EXISTS (SELECT 'x' 
                                          FROM test_data t7
                                           WHERE DATE(t7.EventTime) = DATE(t.EventTime)
                                            AND t7.EventTime < t.EventTime
                                            AND t7.EventTime > t6.EventTime
                                            AND t7.Item = t.Item
                                            AND t7.state <> t.state
                                            )
                        ) 
        END 
      AS MinInGroup

        ,CASE WHEN NOT EXISTS (SELECT 'x' 
                            FROM test_data t3
                            WHERE DATE(t3.EventTime) = DATE(t.EventTime)
                            AND t3.EventTime > t.EventTime
                            AND t3.Item = t.Item
                            )
              THEN CONVERT(CONCAT(DATE(t.EventTime) , ' 23:59'), DATETIME)
              ELSE (SELECT MAX(EventTime)
                     FROM test_data t4
                    WHERE DATE(t4.EventTime) = DATE(t.EventTime)
                      AND t4.EventTime > t.EventTime
                      AND t4.Item = t.Item
                      AND NOT EXISTS (SELECT 'x' 
                                      FROM test_data t5
                                       WHERE DATE(t5.EventTime) = DATE(t.EventTime)
                                        AND t5.EventTime > t.EventTime
                                        AND t5.EventTime < t4.EventTime
                                        AND t5.Item = t.Item
                                        AND t5.state <> t.state
                                        )
                    )
            END 
      AS MaxInGroup
  FROM test_data t
  ORDER BY 1, 3
sonic8 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.06.2017, 16:35   #3
hcscherzer
MOF Guru
MOF Guru
Standard

Ich bin jetzt auch nicht ganz sicher, ob das mit den (möglichen) Unterbrechungen tatsächlich mit einer einzigen Auswahlabfrage zu lösen ist.

In Jet (Access) würde ich mir eine Function schreiben, die das iterativ mit einem RecordSet löst. In TSQL würde ich eine StoredProcedure anlegen, die das mit Hilfe eines Cursors ebenfalls iterativ löst.

Ist vielleicht nicht die eleganteste (und auch schnellste) Lösung aber für mich wäre es die effektivste.

Nun bin ich in MySQL nicht so fit als ich wüsste, ob dort solche iterativen Prozeduren möglich sind ... aber ein kurzer Blick auf einige Google-Resultate zeigt, dass es (mindestens in 5.7) sowohl die Möglichkeit gibt, Procedures zu programmieren als auch die Verwendung eines Cursor.

__________________

Freundlichen Gruß
Hans-Christian
-----------------------------------------
Oft erwünscht, selten beachtet: nach Erledigung des Problems den Thread als erledigt zu markieren
-----------------------------------------
Ich möchte nur Mitglied in einem Verein sein, der Leute wie mich nicht als Mitglied aufnimmt (Groucho Marx).
-----------------------------------------
Ab sofort regelmässig: MOF Stammtisch in Bremen. Näheres hier.
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.06.2017, 17:21   #4
sonic8
Threadstarter Threadstarter
MOF Profi
MOF Profi
Standard

Zitat: von hcscherzer Beitrag anzeigen

Ich bin jetzt auch nicht ganz sicher, ob das mit den (möglichen) Unterbrechungen tatsächlich mit einer einzigen Auswahlabfrage zu lösen ist.

Ich glaube mit mehreren, aufeinander aufbauenden Abfragen (DerivedTables/Subselects) innerhalb der der einen Auswahlabfrage könnte es gehen. - Ich glaube, ich habe eine solche Lösung gefunden.

Ich versuche jetzt meinen neuen Lösungsansatz aus dem vereinfachten Beispiel hier in die leider noch wesentlich komplexere Echtdatenstruktur zu integrieren und poste dann meine Lösung. - Wenn es denn wirklich eine ist.
sonic8 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.06.2017, 19:43   #5
sonic8
Threadstarter Threadstarter
MOF Profi
MOF Profi
Standard

Zitat: von hcscherzer Beitrag anzeigen

Ich bin jetzt auch nicht ganz sicher, ob das mit den (möglichen) Unterbrechungen tatsächlich mit einer einzigen Auswahlabfrage zu lösen ist.

Ja, ist es. Siehe unten...

Zitat: von hcscherzer Beitrag anzeigen

In Jet (Access) würde ich mir eine Function schreiben, die das iterativ mit einem RecordSet löst. In TSQL würde ich eine StoredProcedure anlegen, die das mit Hilfe eines Cursors ebenfalls iterativ löst.

Ist vielleicht nicht die eleganteste (und auch schnellste) Lösung aber für mich wäre es die effektivste.

Ich versuche möglichst immer solche Operationen in einem SQL-Statement zusammenzufassen. Das ist meist performanter.
Ich habe aber zunehmend ein Problem mit solch sehr komplexen SQL-Statements. - Sie sind schwer zu testen.
Es werden viele verschiedene Einzelausdrücke und Operationen zusammengefasst und schlagen sich in dem insgesamt korrekten oder manchmal eben nicht korrekten Ergebnis nieder. Wenn das Ergebnis nicht korrekt ist, ist die Fehlersuche mühsam und langwierig.
In der objektorientierten Programmierung setze ich zunehmend Unit Tests ein, um automatisch einzelne, kleine Operationen testen zu können. Das macht Spaß!
Vor diesem Hintergrund frage ich mich immer häufiger, ob nicht in manchen Fällen ein anderer Ansatz als super lange SQL-Statements aus dem Blickwinkel der Wartbarkeit nicht vielleicht besser wären.

So, jetzt wieder zum konkreten Problem.
Mein erster Lösungsansatz war gar nicht so schlecht. Ich habe das jetzt so erweitert, dass ich nicht nur für den ersten bzw. letzten Datensatz eines Tages Anfangs- und Endzeitpunkt substituiere, sondern für jeden Datensatz in einer "Zustandsgruppe" immer Anfangs- und Endzeitpunkt der jeweiligen Gruppe ermittele.
Damit kann ich dann auf der nächsten Ebene die Duplikate mit DISTINCT eliminieren und die Dauer (Duration) über ein TimeDiff ermittelt. In einer Weiteren Abfrageebene kann ich dann die Summe über die Dauer bilden und mit MAX den letzten Zeitpunkt eines Zustandes ermitteln. - Gelöst!

So hier jetzt das fertige Ergebnis:
(Es sind noch ein paar weiter Fehler korrigiert, auf die ich nicht eingehe, weil es nur Nebenschauplätze sind.)
(Ich habe das SQL-Statement auch nochmal als Datei angehängt, weil es im den Code-Fenster nur begrenzt lesbar ist.)

Code:

SELECT Item, State, DayDate, MAX(MaxInGroup) AS LastPointInTimeForState, SUM(Duration) AS DurationInMinutes
FROM (
	SELECT DISTINCT Item, State, DayDate, MinInGroup, MaxInGroup, time_to_sec(TIMEDIFF(MaxInGroup, MinInGroup) ) /60 AS Duration
	FROM (
		SELECT  t.Item 
				,t.State
				,DATE(t.EventTime) AS DayDate
				,CASE WHEN NOT EXISTS (SELECT 'x' 
										FROM test_data t2
										WHERE DATE(t2.EventTime) = DATE(t.EventTime)
										AND t2.EventTime < t.EventTime
										AND t2.Item = t.Item                               
										)
									OR (NOT EXISTS (SELECT 'x'
													   FROM test_data t10
													   WHERE t10.Item = t.Item
													   AND t10.State <> t.State
													   AND DATE(t10.EventTime) = DATE(t.EventTime)
													   AND t10.EventTime < t.EventTime 
														)
											)
						  THEN CONVERT(CONCAT(DATE(t.EventTime) , ' 00:00'), DATETIME)
						  ELSE (SELECT MIN(EventTime)
								 FROM test_data t6
								WHERE DATE(t6.EventTime) = DATE(t.EventTime)
								  AND t6.EventTime <= t.EventTime
								  AND t6.Item = t.Item
								  AND t6.State = t.State
								  AND NOT EXISTS (SELECT 'x' 
												  FROM test_data t7
												   WHERE DATE(t7.EventTime) = DATE(t.EventTime)
													AND t7.EventTime <= t.EventTime
													AND t7.EventTime > t6.EventTime
													AND t7.Item = t.Item
													AND t7.state <> t.state
													)
								) 
				END 
			  AS MinInGroup

				,CASE WHEN NOT EXISTS (SELECT 'x' 
									FROM test_data t3
									WHERE DATE(t3.EventTime) = DATE(t.EventTime)
									AND t3.EventTime > t.EventTime
									AND t3.Item = t.Item
									)
								OR (NOT EXISTS (SELECT 'x'
												   FROM test_data t11
												   WHERE t11.Item = t.Item
												   AND t11.State <> t.State
												   AND DATE(t11.EventTime) = DATE(t.EventTime)
												   AND t11.EventTime > t.EventTime 
													)
										)
					  THEN CONVERT(CONCAT(DATE(t.EventTime) , ' 23:59'), DATETIME)
					  ELSE (SELECT MAX(t4.EventTime)
							 FROM test_data t4
							WHERE DATE(t4.EventTime) = DATE(t.EventTime)
							  AND t4.EventTime >= t.EventTime
							  AND t4.Item = t.Item
							  AND t4.State = t.State
							  AND NOT EXISTS (SELECT 'x' 
											  FROM test_data t5
											   WHERE DATE(t5.EventTime) = DATE(t.EventTime)
												AND t5.EventTime >= t.EventTime
												AND t5.EventTime < t4.EventTime
												AND t5.Item = t.Item
												AND t5.state <> t.state
												)
							)
					END 
			  AS MaxInGroup
		  FROM test_data t
		) AS X
	) AS Y
GROUP BY Y.Item, Y.State, Y.DayDate
ORDER BY 1, 3
Angehängte Dateien
Dateityp: txt _states_per_day_report_extension.txt (2,7 KB, 3x aufgerufen)
sonic8 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 01:13 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.