Оператор GROUP BY + CASE

Question

У меня есть рабочий запрос, который группирует данные по аппаратной модели и результату, но проблема в том, что есть много "результатов". Я попытался уменьшить это до "если результат = 0, то оставить как 0, в противном случае установите его на 1". Обычно это работает, но в итоге я получаю:

    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

Вместо совокупности я пытаюсь достичь, где только 1 строка на тип / случай комбо.

    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

Вот мой запрос:

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;

Любые советы о том, как я могу добиться этого, были бы потрясающими.

День всегда будет определен в WHERE оговорка, поэтому она не изменится. name, type, result(case) а также count будет меняться. Короче говоря, для любой данной модели я хочу только 1 строку для каждой комбинации "тип + случай". Как вы можете видеть в первом наборе результатов, у меня есть 3 строки для modelA который имеет type=1 а также case=1 (потому что есть много "результирующих" значений, которые я превратил в 0=0 и все остальное = 1). Я хочу, чтобы это было представлено в виде 1 строки с количеством агрегированных данных, как в примере набора данных 2.

Answer 1

Ваш запрос уже будет работать - за исключением того, что вы сталкиваетесь с конфликтами имен или просто путаете выходной столбец (CASE выражение) с исходным столбцом result, который имеет разное содержание.

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

Вам нужно GROUP BY ваш CASE выражение вместо исходного столбца:

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

Или укажите псевдоним столбца, который отличается от любого имени столбца в FROM список - или этот столбец имеет приоритет:

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

Стандарт SQL довольно своеобразен в этом отношении. Цитирование руководства здесь:

Имя выходного столбца может использоваться для ссылки на значение столбца в ORDER BY а также GROUP BY пункты, но не в WHERE или же HAVING положения; вместо этого вы должны выписать выражение.

А также:

Если ORDER BY выражение - это простое имя, которое соответствует как имени выходного столбца, так и имени входного столбца, ORDER BY будет интерпретировать его как имя выходного столбца. Это противоположность выбора, который GROUP BY сделаю в той же ситуации. Это несоответствие сделано для совместимости со стандартом SQL.

Жирный акцент мой.

Этих конфликтов можно избежать, используя позиционные ссылки (порядковые номера) в GROUP BY а также ORDER BY, ссылаясь на элементы в SELECT список слева направо. Смотрите решение ниже.
Недостатком является то, что это может быть сложнее для чтения и уязвимы для редактирования в SELECT список (можно забыть адаптировать позиционные ссылки соответственно).

Но вам не нужно добавлять столбец day к GROUP BY предложение, если оно содержит постоянное значение (CURRENT_DATE-1).

Переписать и упростить с правильным синтаксисом JOIN и позиционными ссылками это может выглядеть так:

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;

Также обратите внимание, что я избегаю названия столбца time, Это зарезервированное слово и никогда не должно использоваться в качестве идентификатора. Кроме того, ваше "время", очевидно, является timestamp или же date так что это довольно обманчиво.

Answer 2

Можете ли вы попробовать это: замените регистр дел с приведенным ниже

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

Answer 3

Псевдонимы можно использовать только в том случае, если они были введены на предыдущем шаге. Так что псевдонимы вSELECT предложение может использоваться в ORDER BY но не GROUP BY пункт.

Ссылка: документация Microsoft T-SQL для дальнейшего чтения.

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Надеюсь это поможет.

Answer 4

Попробуйте добавить два других столбца без COUNT в 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;

Answer 5

Для TSQL мне нравится инкапсулировать операторы case во внешнем приложении. Это избавляет меня от необходимости записывать оператор case дважды, позволяет ссылаться на оператор case псевдонимом в будущих соединениях и устраняет необходимость в позиционных ссылках.

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;