wake-up-neo.com

GROUP BY + CASE-Anweisung

Ich habe eine Arbeitsabfrage, die Daten nach Hardwaremodell und Ergebnis gruppiert, aber das Problem ist, dass es viele "Ergebnisse" gibt. Ich habe versucht, das auf zu reduzieren "wenn result = 0 dann als 0 bleibt, sonst auf 1 setzen" . Das funktioniert im Allgemeinen, aber am Ende habe ich:

    day     |      name      | type | case | count
------------+----------------+------+------+-------
 2013-11-06 | modelA         |    1 |    0 |   972
 2013-11-06 | modelA         |    1 |    1 |    42
 2013-11-06 | modelA         |    1 |    1 |     2
 2013-11-06 | modelA         |    1 |    1 |    11
 2013-11-06 | modelB         |    1 |    0 |   456
 2013-11-06 | modelB         |    1 |    1 |    16
 2013-11-06 | modelB         |    1 |    1 |     8
 2013-11-06 | modelB         |    3 |    0 | 21518
 2013-11-06 | modelB         |    3 |    1 |     5
 2013-11-06 | modelB         |    3 |    1 |     7
 2013-11-06 | modelB         |    3 |    1 |   563

Anstelle des Aggregats versuche ich zu erreichen, wobei nur eine Zeile pro Typ/Fall kombiniert wird.

    day     |      name      | type | case | count
------------+----------------+------+------+-------
 2013-11-06 | modelA         |    1 |    0 |   972
 2013-11-06 | modelA         |    1 |    1 |    55
 2013-11-06 | modelB         |    1 |    0 |   456
 2013-11-06 | modelB         |    1 |    1 |    24
 2013-11-06 | modelB         |    3 |    0 | 21518
 2013-11-06 | modelB         |    3 |    1 |   575

Hier ist meine Frage:

select CURRENT_DATE-1 AS day, model.name, attempt.type, 
       CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END, 
       count(*) 
from attempt attempt, prod_hw_id prod_hw_id, model model
where time >= '2013-11-06 00:00:00'  
AND time < '2013-11-07 00:00:00'
AND attempt.hard_id = prod_hw_id.hard_id
AND prod_hw_id.model_id = model.model_id
group by model.name, attempt.type, attempt.result
order by model.name, attempt.type, attempt.result;

Irgendwelche Tipps, wie ich das erreichen kann, wären fantastisch.

Der Tag wird immer in der WHERE-Klausel definiert, so dass er nicht variiert. name, type, result(case) und count variieren. Kurz gesagt, ich möchte für jedes Modell nur eine Zeile pro "type + case" combo. Wie Sie in der ersten Ergebnismenge sehen können, habe ich 3 Zeilen für modelA, die type=1 und case=1 haben (da es viele "result" "Werte" gibt, die ich in 0 = 0 umgewandelt habe und alles andere = 1 ). Ich möchte, dass dies als 1 Zeile dargestellt wird, wobei die Anzahl wie in Beispieldatensatz 2 aggregiert ist.

sqlpostgresqlgroup-bycaseaggregate-functions
18
ssbsts

Ihre Abfrage würde bereits funktionieren - mit der Ausnahme, dass Sie Namenskonflikte erhalten oder nur die -Ausgabespalte (den Ausdruck CASE) mit der source-Spalteresult verwechseln, die einen anderen Inhalt hat.

...
GROUP BY model.name, attempt.type, attempt.result
...

Sie müssen anstelle der Quellspalte Ihren GROUP BY-Ausdruck CASE eingeben:

...
GROUP BY model.name, attempt.type
       , CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END
...

Oder geben Sie einen column-Alias ​​ an, der sich von einem beliebigen Spaltennamen in der FROM-Liste unterscheidet - oder diese Spalte hat Vorrang:

SELECT ...
     , CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END AS result1
...
GROUP BY model.name, attempt.type, result1
...

Der SQL-Standard ist in dieser Hinsicht ziemlich eigenartig. Das Handbuch hier zitieren:

Der Name einer Ausgabespalte kann verwendet werden, um auf den Wert der Spalte in .__ zu verweisen. ORDER BY- und GROUP BY-Klauseln, jedoch nicht in den WHERE- oder HAVING-Klauseln; dort müssen Sie stattdessen den Ausdruck ausschreiben.

Und:

Wenn ein ORDER BY-Ausdruck ein einfacher Name ist, der mit einer Ausgabe übereinstimmt Spaltenname und Name der Eingabespalte, ORDER BY interpretiert es als der Name der Ausgabespalte. Dies ist das Gegenteil der Wahl, dass GROUP BY wird in der gleichen Situation machen. Diese Inkonsistenz wird als .__ bezeichnet. kompatibel mit dem SQL-Standard.

Fett mein Schwerpunkt.

Diese Konflikte können durch die Verwendung von positional reference (Ordinalnummern) in GROUP BY und ORDER BY vermieden werden, wobei auf Elemente in der Liste SELECT von links nach rechts verwiesen wird. Siehe Lösung unten.
Der Nachteil ist, dass dies möglicherweise schwieriger zu lesen ist und anfällig für Änderungen in der Liste SELECT ist (man könnte vergessen, die Positionsreferenzen entsprechend anzupassen). 

Sie müssen jedoch nicht die Spalte day zur GROUP BY-Klausel hinzufügen, sofern sie einen konstanten Wert (CURRENT_DATE-1) enthält.

Umgeschrieben und mit der richtigen JOIN-Syntax und Positionsreferenzen vereinfacht, könnte es so aussehen:

SELECT m.name
     , a.type
     , CASE WHEN a.result = 0 THEN 0 ELSE 1 END AS result
     , CURRENT_DATE - 1 AS day
     , count(*) AS ct
FROM   attempt    a
JOIN   prod_hw_id p USING (hard_id)
JOIN   model      m USING (model_id)
WHERE  ts >= '2013-11-06 00:00:00'  
AND    ts <  '2013-11-07 00:00:00'
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

Beachten Sie auch, dass ich den Spaltennamen time meide. Das ist ein reserviertes Word und sollte niemals als Bezeichner verwendet werden. Außerdem ist Ihre "Zeit" offensichtlich eine timestamp ODER date , so dass dies ziemlich irreführend ist.

51
Erwin Brandstetter

versuchen Sie bitte Folgendes: Ersetzen Sie die Case-Anweisung durch die folgende 

Sum(CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END) as Count,
6
Malathi

Fügen Sie der GROUP BY die anderen beiden nicht COUNT-Spalten hinzu:

select CURRENT_DATE-1 AS day, 
model.name, 
attempt.type, 
CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END, 
count(*) 
from attempt attempt, prod_hw_id prod_hw_id, model model
where time >= '2013-11-06 00:00:00'  
AND time < '2013-11-07 00:00:00'
AND attempt.hard_id = prod_hw_id.hard_id
AND prod_hw_id.model_id = model.model_id
group by 1,2,3,4
order by model.name, attempt.type, attempt.result;
1
Filipe Silva

Für TSQL kapsle ich gerne Case-Statements in einer äußeren Anwendung. Dies verhindert, dass ich die case-Anweisung zweimal schreiben muss, ermöglicht den Verweis auf die case-Anweisung durch Alias ​​in zukünftigen Joins und vermeidet die Notwendigkeit von Positionsverweisen.

select oa.day, 
model.name, 
attempt.type, 
oa.result
COUNT(*) MyCount 
FROM attempt attempt, prod_hw_id prod_hw_id, model model
WHERE time >= '2013-11-06 00:00:00'  
AND time < '2013-11-07 00:00:00'
AND attempt.hard_id = prod_hw_id.hard_id
AND prod_hw_id.model_id = model.model_id
OUTER APPLY (
    SELECT CURRENT_DATE-1 AS day,
     CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END result
    ) oa    
group by oa.day, 
model.name, 
attempt.type, 
oa.result
order by model.name, attempt.type, oa.result;
0
Ryan O'Donnell