Alle Geräte finden, die niemals einen OK status hatten

[creativeone]

Ich habe eine Tabelle wo Gerätetests eingetragen sind, die Tabelle ist vereinfacht so aufgebaut:

• id
• timestamp
• geraet_id
• status, vereinfacht ein enum mit OK oder NICHTOK

Da stehen dann die Rückmeldungen von den Tests drin. Optimalerweise sind alle OK, dann gibt es Geräte die generell OK sind aber nur manchmal nicht funktionieren und dann gibt es Geräte, die noch nie funktioniert haben.

Wie bekomm ich jetzt eine Liste der Geräte, die noch nie funktioniert haben, bei denen also status bei ALLEN Datensätzen auf NICHTOK ist?

Dachte an einen HAVING, aber mir fehlt die Fantasie wie der aussehen sollte?

 

[akretschmer]

vereinfachtes Beispiel:

edb=*# select * from creativeone ;
 geraet | okay 
--------+------
      1 | t
      1 | f
      2 | f
      2 | f
      3 | t
      3 | t
(6 rows)

edb=*# with temp as (select geraet, count(1) as gesamt, count(1) filter (where not okay) as fail from creativeone group by geraet) select * from temp where gesamt = fail;
 geraet | gesamt | fail 
--------+--------+------
      2 |      2 |    2
(1 row)

edb=*#

Having geht auch:

edb=*# select geraet, count(1) as gesamt, count(1) filter (where not okay) as fail from creativeone group by geraet having count(1) =  count(1) filter (where not okay) ;
 geraet | gesamt | fail 
--------+--------+------
      2 |      2 |    2
(1 row)

Prost!

 

[creativeone]

FILTER gibt es offenbar auf mysql nicht, allerdings habe ich jetzt gelesen, dass man FILTER durch CASE WHEN ersetezen kann. Danke trotzdem!

Mein damit gebauter select spuckt mir aber genau das verkehrte Ergebnis aus, nur Geräte die OK sind statt Geräte die NICHTOK sind. Wo hab ich da den Denkfehler?

SELECT geraete_id, count(1) as gesamt, count(CASE WHEN status='NICHTOK' THEN 1 ELSE 0 END) as fail
FROM creativeone
GROUP BY geraete_id
HAVING count(1) = count(CASE WHEN status='NICHTOK' THEN 1 ELSE 0 END)
LIMIT 5

Funktioniert auch dann nicht wenn ich schreibe HAVING gesamt = fail.

 

[akretschmer]

edb=*# with t as (select 1 as t union all select 0) select count(case when t = 1 then 1 else 0 end) from t ;
 count 
-------
     2
(1 row)

edb=*# with t as (select 1 as t union all select 0) select sum(case when t = 1 then 1 else 0 end) from t ;
 sum 
-----
   1
(1 row)

edb=*#

 

[creativeone]

@akretschmer du bist ein echter Freund von Denksportaufgaben, gell? Aber mit deinem Hinweis hab ichs richtig hin bekommen:

SELECT geraete_id, count(1) as gesamt, sum(CASE WHEN status='NICHTOK' THEN 1 ELSE 0 END) as fail
FROM creativeone
GROUP BY geraete_id
HAVING gesamt = fail

 

[akretschmer]

dein count() im ersten Versuch zählt sowohl die 0 als auch die 1. Böse Falle, gell?

 

[BerndB]

geht auch einfach mit IF NOT EXISTS.

MariaDB [bernd]> select * from tests;
+----+---------------------+-----------+--------+
| id | insert_at           | geraet_id | status |
+----+---------------------+-----------+--------+
|  1 | 2021-12-22 19:51:13 |         1 | nok    |
|  2 | 2021-12-22 19:41:59 |         2 | ok     |
|  3 | 2021-12-22 19:42:47 |         1 | nok    |
|  4 | 2021-12-22 19:51:18 |         1 | nok    |
|  5 | 2021-12-22 19:41:59 |         3 | nok    |
|  6 | 2021-12-22 19:41:59 |         2 | nok    |
+----+---------------------+-----------+--------+
6 rows in set (0.00 sec)

MariaDB [bernd]> SELECT * FROM (
    ->   SELECT DISTINCT geraet_id  FROM tests
    -> ) AS t1
    -> WHERE NOT EXISTS ( SELECT 1 FROM tests WHERE geraet_id = t1.`geraet_id` AND STATUS <> 'nok');
+-----------+
| geraet_id |
+-----------+
|         1 |
|         3 |
+-----------+
2 rows in set (0.04 sec)

MariaDB [bernd]>