Erst nach dreimaliger Erfüllung einer Bedingung hintereinander soll der Datensatz ausgegeben werden

Trekuel · 31 Oktober 2023

Guten Tag zusammen,

ich habe eine Frage bezüglich eines "Ausgabefilters".

In einer Tabelle habe ich Zeitstempel und diverse andere Werte, eine Spalte innerhalb dieser Tabelle ist meist DBNULL.

Ich möchte bezüglich dieser Spalte nur die Zeilen ausgegeben haben, wenn diese Spalte drei Mal (oder mehr als zwei Mal) hintereinander, einen Wert hat.

Beispiel bezogen auf einen Identifier:
select id, null_wert_spalte, identifier, datum
from testtable
where
identifier = 17
order by datum asc;

Ergebnis_
ID_______Null-wert-Spalte_________Identifier_______Datum
2_______ A_________________________17 ____ 2023-01-14
5 ________DBNULL _________________17 ____ 2023-02-14
7 _______DBNULL_________________ 17_____2023-02-18
12_______A ________________________17_____2023-03-01
23_______A ________________________ 17_____2023-03-05
25_______A _________________________17_____2023-03-08
235_____DBNULL_________________ 17_____2023-03-14
475_____A ________________________17______2023-04-25
500_____DBNULL ________________ 17_____ 2023-05-18

Ich möchte als Ergebnis lediglich die Zeile mit der ID 25 zurückgegeben haben, da hier die "Null_wert_spalte" drei mal hintereinander gefüllt ist.

Herzlichen Dank für jede Anregung!

akretschmer · 31 Oktober 2023

Code:

postgres=# select * from trekuel;
 id | null_spalte | sort_spalte 
----+-------------+-------------
  9 | a           |           1
 10 |             |           2
 11 |             |           3
 12 |             |           4
 13 | b           |           5
 14 |             |           6
 15 |             |           7
 16 | c           |           8
(8 rows)

postgres=# select *, case when null_spalte is null and lag(null_spalte) over (order by sort_spalte) is null and lag(null_spalte,2) over (order by sort_spalte) is null then true else false end from trekuel;
 id | null_spalte | sort_spalte | case 
----+-------------+-------------+------
  9 | a           |           1 | f
 10 |             |           2 | f
 11 |             |           3 | f
 12 |             |           4 | t
 13 | b           |           5 | f
 14 |             |           6 | f
 15 |             |           7 | f
 16 | c           |           8 | f
(8 rows)

postgres=# with foo as (select *, case when null_spalte is null and lag(null_spalte) over (order by sort_spalte) is null and lag(null_spalte,2) over (order by sort_spalte) is null then true else false end as x from trekuel) select id, null_spalte, sort_spalte from foo where x is true;
 id | null_spalte | sort_spalte 
----+-------------+-------------
 12 |             |           4
(1 row)

postgres=#

Du erkennst den Weg?

ukulele · 31 Oktober 2023

@akretschmer hat den Ansatz richtig allerdings prüft er auf drei hintereinander folgende NULL-Werte, nicht auf identische nicht-NULL-Werte.

Code:

SELECT    t.*
FROM    (

SELECT    (    CASE
            WHEN    null_wert_spalte = lag(null_wert_spalte) OVER (PARTITION BY identifier ORDER BY datum ASC)
            AND        null_wert_spalte = lag(null_wert_spalte,2) OVER (PARTITION BY identifier ORDER BY datum ASC)
            THEN    1
            ELSE    0
            END ) AS truefalse,
        *
FROM    testtable

        ) t
WHERE    t.truefalse = 1
AND        t.identifier = 17
ORDER BY t.datum ASC

Zusätzlich finde ich gehört da ein PARTITION rein, falls die WHERE-Bedingung auf identifier mal nicht gegeben ist.

akretschmer · 31 Oktober 2023

ukulele schrieb:
prüft er auf drei hintereinander folgende NULL-Werte, nicht auf identische nicht-NULL-Werte.

ah ja, mein Fehler, Danke.

Trekuel · 10 November 2023

ukulele schrieb:
@akretschmer hat den Ansatz richtig allerdings prüft er auf drei hintereinander folgende NULL-Werte, nicht auf identische nicht-NULL-Werte.

Code:

SELECT t.* FROM ( SELECT ( CASE WHEN null_wert_spalte = lag(null_wert_spalte) OVER (PARTITION BY identifier ORDER BY datum ASC) AND null_wert_spalte = lag(null_wert_spalte,2) OVER (PARTITION BY identifier ORDER BY datum ASC) THEN 1 ELSE 0 END ) AS truefalse, * FROM testtable ) t WHERE t.truefalse = 1 AND t.identifier = 17 ORDER BY t.datum ASC

Zusätzlich finde ich gehört da ein PARTITION rein, falls die WHERE-Bedingung auf identifier mal nicht gegeben ist.

Hallo ukulele,

super, das läuft fantastisch!
Jedoch nur, wenn null_wert_spalte immer den selben Wert hat. Der Wert dieser Spalte kann aber, neben "DBNULL", auch "A", "S" oder "D" sein. Und nur dann, wenn der Wert der Spalte drei Mal hintereinander "nicht null" ist, soll die Spalte ausgegeben werden.

Mit meinen mehr als bescheidenen Kenntnissen kann ich aber leider weder das Script von akretschmer, noch das von ukulele passend bearbeiten.

Darf ich noch einmal um Unterstützung bitten?
Herzlichen Dank!

akretschmer · 10 November 2023

Trekuel schrieb:
Jedoch nur, wenn null_wert_spalte immer den selben Wert hat. Der Wert dieser Spalte kann aber, neben "DBNULL", auch "A", "S" oder "D" sein. Und nur dann, wenn der Wert der Spalte drei Mal hintereinander "nicht null" ist, soll die Spalte ausgegeben werden.

schau dir meine Lösung noch mal genau an! Ich traue Dir zu, aus einem 'is null' ein 'is not null' hinzubekommen ...

Trekuel · 10 November 2023

Ich musste es, dank M$$QL, etwas umschreiben . .

Ich kopier hier mal "alles" rein:

SQL:

create table testtable (
t_id int,
t_spalte char(1),
t_identifier int,
t_datum date);

SQL:

-- Identifier = 17
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(2,    'A',17,'2023-01-14');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(5,   NULL,17,'2023-02-14');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(7,   NULL,17,'2023-02-18');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(12,   'A',17,'2023-03-01');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(23,   'A',17,'2023-03-05');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(25,   'A',17,'2023-03-08');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(235, NULL,17,'2023-03-14');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(475,  'A',17,'2023-04-25');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(500, NULL,17,'2023-05-18');
-- identifier = 25:

insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(20,    'A',25,'2023-01-14');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(50,   NULL,25,'2023-02-14');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(70,   NULL,25,'2023-02-18');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(120,   'A',25,'2023-03-01');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(230,   'A',25,'2023-03-05');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(250,   'A',25,'2023-03-08');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(2350, NULL,25,'2023-03-14');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(4750,  'A',25,'2023-04-25');
insert into testtable (t_id, t_spalte, t_identifier, t_datum) values(5000, NULL,25,'2023-05-18');

IF OBJECT_ID('tempdb.dbo.#trek', 'U') IS NOT NULL  DROP TABLE #trek; 


select *, case     when t_spalte is not null and
        lag(t_spalte)   over (order by t_spalte) is not null and
        lag(t_spalte,2) over (order by t_spalte) is not null

    then  'true '
    else 'false' end as x into #trek from testtable;
   
    select  *From #trek order by t_identifier, t_datum;

gibt mir 8 Zeilen zurück, das Script von ukulele ...

SQL:

IF OBJECT_ID('tempdb.dbo.#trek', 'U') IS NOT NULL  DROP TABLE #trek; 
select t_id, t_spalte, * from #trek where x = 'true'     order by t_datum
select *,
    case
        when t_spalte is not null and
        lag(t_spalte)   over (order by t_spalte) is not null and
        lag(t_spalte,2) over (order by t_spalte) is not null
        then
        'true'
        else
        'false '
    end as x into #trek from testtable;
  
    select t_id, t_spalte, t_spalte from #trek where x ='true';

die 2 Zeilen 25 (Identifier "A") und 250 (Identifier "S"), jedoch mur, wenn drei Mal "A" oder drei Mal "S" gefunden wird. Die Kombination "A" und "S" innerhalb eines Identifiers wird nicht gefunden...

akretschmer · 10 November 2023

ein 'S' kommt in Deinen Daten nirgends vor.

Trekuel · 10 November 2023

Man muss manchmal gedanklich nur weit genug vom Thema weg sein . . .

ich werde am Montag mal

SQL:

... case
        when t_spalte in ('A','S','D') and
        lag(t_spalte)   over (order by t_spalte) in ('A','S','D')and
        lag(t_spalte,2) over (order by t_spalte) in ('A','S','D')
        then ...

testen.

Trekuel · 10 November 2023

akretschmer schrieb:
ein 'S' kommt in Deinen Daten nirgends vor.

Ja, "nicht null" war in der Ursprungsfrage formuliert. . . mein Fehler. Aber das Posting direkt drüber zeigt, dass ich versuche, mitzudenken.

ukulele · 13 November 2023

Leider war auch Eingangs nicht ganz klar ob mit "drei mal hintereinander einen Wert", der selbe Wert oder ein beliebiger Wert gemeint war und ob DBNULL einem IS NULL entspricht daher war ich etwas verunsichert. Aber wenn ich dich richtig verstehe läuft es ja jetzt wie gewünscht.

Trekuel · 13 November 2023

Leider noch nicht ganz. Das SQL ...

SQL:

SELECT    t.* FROM (
    SELECT (
        CASE
            WHEN
                t_spalte = lag(t_spalte,1) OVER (PARTITION BY t_identifier ORDER BY t_datum ASC)
                AND
                t_spalte = lag(t_spalte,2) OVER (PARTITION BY t_identifier ORDER BY t_datum ASC)
            THEN    1
            ELSE     0
        
        END ) AS truefalse,  *
FROM    testtable
        ) t
WHERE    t.truefalse = 1 --and t_spalte in ('A','S','D')
--AND        t.t_identifier = 17
ORDER BY t.t_datum ASC;

... gibt mir die Zeiden der IDs 25 und 250 zurück, in denen sich das "A" (25) und das "S" (250) jeweils wiederholen.

Ändere ich den Datensatz ID 23 von "A" auf "S" ab, bekomme ich leider nur die Zeile mit der ID 250 zurück. Und die Konstruktion von Freitag, 17:06 Uhr war auch ein Schuss in den Ofen. Ich kann zwar Werte ausschließen, die er in t_spalte ignorieren soll, aber keine Werte(-Menge) vorgeben, auf welche das "lag" reagieren soll.

dabadepdu · 13 November 2023

Wenn Du mit Lag arbeitest, gibt es keine "Menge", das ist nur stumpf voriger, vorvoriger....
Wenn Du mit Mengen arbeiten willst, musst Du aus der Menge der Werte eine "Tabelle" machen, mal so als Idee.
Oder Du machst ein String oder Array Aggregat auf die drei Werte und den Vergleich darin. Das bietet sich an, wenn es Vergleichsoperatoren in MSSQL gibt, die auf Array, Json oder Stringketten einen Mengenvergleich erlauben.

Wenn Du Dein Case etwas aufbohrst und OR verwendest, sodass verschiedene Kombinationen möglich sind, ginge es vermutlich. Das macht aber nur Sinn, wenn die Werte S, A, .. hier im Thread nicht symbolisch für beliebige, viele Werte stehen.

Trekuel · 13 November 2023

Lösung:

SQL:

SELECT    t.* FROM (
    SELECT (
        IIF(t_spalte is not null
        and
        lag(t_spalte,1) OVER (PARTITION BY t_identifier ORDER BY t_datum ASC) is not null
        AND
        lag(t_spalte,2) OVER (PARTITION BY t_identifier ORDER BY t_datum ASC) is not null
        ,  1
        ,  0)
    ) AS truefalse,  *
FROM testtable
) t
WHERE    t.truefalse = 1
--AND        t.t_identifier = 17
ORDER BY t.t_datum ASC;

Mit herzlichem Dank an alle Beteiligten!

ukulele · 13 November 2023

Es gibt sehr viele Wege aber lag() ist hier sicherlich der beste Weg. Wenn es denn damit gar nicht zu machen wäre (hier geht es ja siehe #14 ), dann kann man auch noch mit CTE da rekurisiv drüber fahren.

Erst nach dreimaliger Erfüllung einer Bedingung hintereinander soll der Datensatz ausgegeben werden

Trekuel

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru

akretschmer

Datenbank-Guru

Trekuel

Benutzer

akretschmer

Datenbank-Guru

Trekuel

Benutzer

akretschmer

Datenbank-Guru

Trekuel

Benutzer

Trekuel

Benutzer

ukulele

Datenbank-Guru

Trekuel

Benutzer

dabadepdu

Datenbank-Guru

Trekuel

Benutzer

ukulele

Datenbank-Guru

Ähnliche Themen