SQL: Aufeinanderfolgende Bereiche in einer Sequenz erkennen

Aus DBWiki
Wechseln zu: Navigation, Suche

Zurück zur Übersicht



Vielfach stellt sich die Aufgabe, Datensätze, die durch eine Sortierung angeordnet sind, nach einem Wert oder Wertebereich auszufiltern. Zusätzlich lässt sich noch als Randbedingung definieren, dass der Bereich mindestens eine bestimmte Anzahl an Werten aufweisen muss, um berücksichtigt zu werden.


Es soll z.B. die Prozessaufzeichnung einer Produktion dazu herangezogen werden, um festzustellen, welche Produkte einer Qualitätsanforderung entsprechen. Es soll gelten, dass der Wert druck zwischen 1,28 und 1,44 mbar liegen muss, um das Produkt als gut einstufen zu können. Weiterhin ist es erforderlich, dass mindestens zwei aufeinanderfolgende Datensätze diesem Kriterium genügen. Wäre die letzte Forderung nicht gegeben, ist ein offensichtliches

WHERE  druck BETWEEN 1.28 AND 1.44

ausreichend. Um jedoch auch Nachbarwerte mit zu berücksichtigen, ist ein Self-Join nötig. Im Kriterium lässt sich dann die absolute Differenz der Spalte id, die 1 betragen muss, heranziehen, um einen Nachbardatensatz zu finden. Zusätzlich muss in beiden Instanzen der druck entsprechend der Vorgabe im zulässigen Bereich liegen.

SQL-Dialekte, die Window Funktionen unterstützen, können sich die row_number()-Funktion zunutze machen. Dabei bildet die Differenz aus Zeilennummer über das Sortierfeld id und dem Wert bzw. Wertebereich bei aufeinanderfolgenden Datensätzen dann immer denselben Zahlenwert. welcher sich in einer höheren Ebene durch die count()-Funktion zählen lässt und als Ausschlusskriterium verwendet werden kann.

Lösung mittels Self-Join

Hinweis für Access-Anwender

Um die folgenden Abfragen selbst testen zu können, ist die Tabelle dual erforderlich.


Als Ausgangslage verwenden wir diese Auswahlabfrage und nennen sie vw_produktion:

SELECT CLng(1) AS id, 1.35 AS druck FROM dual
UNION ALL
SELECT 2, 1.37 FROM dual
UNION ALL
SELECT 3, 1.45 FROM dual
UNION ALL
SELECT 4, 1.48 FROM dual
UNION ALL
SELECT 5, 1.51 FROM dual
UNION ALL
SELECT 6, 1.39 FROM dual
UNION ALL
SELECT 7, 1.53 FROM dual
UNION ALL
SELECT 8, 1.55 FROM dual
UNION ALL
SELECT 9, 1.62 FROM dual
UNION ALL
SELECT 10, 1.39 FROM dual
UNION ALL
SELECT 11, 1.32 FROM dual
UNION ALL
SELECT 12, 1.28 FROM dual

Folgende Abfrage führt dann zum gewünschten Ergebnis:

SELECT DISTINCT p0.id
FROM   vw_produktion AS p0,
       vw_produktion AS p1
WHERE  p0.druck BETWEEN 1.28 AND 1.44 AND
       p1.druck BETWEEN 1.28 AND 1.44 AND
       Abs( p0.id - p1.id ) = 1

Lösung mittels Window Funktion

WITH w AS (
    SELECT id,
           COUNT( * ) OVER (PARTITION BY grp) AS anz
    FROM   ( SELECT *,
                    ROW_NUMBER() OVER ( ORDER BY id )
                  - ROW_NUMBER() OVER ( PARTITION BY CASE WHEN druck BETWEEN 1.28 AND 1.44 THEN 1 END
                                        ORDER BY id ) AS grp
             FROM   vw_produktion ) AS p
    WHERE  druck BETWEEN 1.28 AND 1.44 )
SELECT id
FROM   w
WHERE  anz >= 2

Die Lösung ist in diesem DBFiddle umgesetzt.

id
1
2
10
11
12



Zurück zur Übersicht