outer - group by case sql server 2008
Instrucción GROUP BY+CASE (4)
Intente agregar las otras dos columnas que no sean COUNT al GROUP BY:
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;
Tengo una consulta de trabajo que agrupa datos por modelo de hardware y un resultado, pero el problema es que hay muchos "resultados" . He intentado reducir eso a "si resultado = 0, entonces manténgalo como 0, de lo contrario, establézcalo en 1" . Esto generalmente funciona, pero termino teniendo:
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
En lugar del agregado que estoy tratando de lograr, solo 1 fila por combo de tipo / caso.
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
Aquí está mi consulta:
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;
Cualquier consejo sobre cómo puedo lograr esto sería increíble.
El día siempre se definirá en la cláusula WHERE , por lo que no variará. name, type, result(case) y el count variarán. En resumen, para cualquier modelo dado quiero solo 1 fila por combo "tipo + caso" . Como puede ver en el primer conjunto de resultados, tengo 3 filas para el modelA que tienen type=1 y case=1 (porque hay muchos valores de "resultado" que he convertido en 0 = 0 y cualquier otra cosa = 1 ). Quiero que se represente como 1 fila con el recuento agregado como en el conjunto de datos de ejemplo 2.
Para TSQL me gusta encapsular declaraciones de casos en una aplicación externa. Esto me impide tener que tener la declaración del caso escrita dos veces, permite la referencia a la declaración del caso por alias en futuras combinaciones y evita la necesidad de referencias posicionales.
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;
Su consulta ya funcionaría, excepto que se está ejecutando en conflictos de nombres o simplemente confunde la columna de salida (la expresión CASE ) con el result columna de origen , que tiene un contenido diferente.
...
GROUP BY model.name, attempt.type, attempt.result
...
Necesita GROUP BY su expresión CASE lugar de su columna fuente:
...
GROUP BY model.name, attempt.type
, CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END
...
O bien, proporcione un alias de columna que sea diferente de cualquier nombre de columna en la lista FROM , o de lo contrario esa columna tendrá prioridad:
SELECT ...
, CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END AS result1
...
GROUP BY model.name, attempt.type, result1
...
El estándar SQL es bastante peculiar a este respecto. Citando el manual aquí:
El nombre de una columna de salida se puede usar para referirse al valor de la columna en las cláusulas
ORDER BYyGROUP BY, pero no en las cláusulasWHEREoHAVING; Allí debes escribir la expresión en su lugar.
Y:
Si una expresión
ORDER BYes un nombre simple que coincide con un nombre de columna de salida y un nombre de columna de entrada,ORDER BYlo interpretará como el nombre de la columna de salida. Esto es lo opuesto a la elección queGROUP BYhará en la misma situación. Esta inconsistencia está hecha para ser compatible con el estándar SQL.
Énfasis en negrita mio
Estos conflictos se pueden evitar mediante el uso de referencias posicionales (números ordinales) en GROUP BY y ORDER BY , haciendo referencia a los elementos en la lista SELECT de izquierda a derecha. Ver solución a continuación.
El inconveniente es que esto puede ser más difícil de leer y vulnerable a las ediciones en la lista SELECT (uno podría olvidarse de adaptar las referencias posicionales en consecuencia).
Pero no tiene que agregar el day la columna a la cláusula GROUP BY , siempre que contenga un valor constante ( CURRENT_DATE-1 ).
Reescrito y simplificado con la sintaxis de JOIN adecuada y las referencias posicionales, podría tener este aspecto:
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;
También tenga en cuenta que estoy evitando el time nombre de columna. Esa es una palabra reservada y nunca debe usarse como identificador. Además, su "tiempo" obviamente es una timestamp o date , por lo que es bastante engañoso.
por favor, intente esto: reemplace la declaración del caso con la siguiente
Sum(CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END) as Count,