Как подсчитать и отобразить уникальные комбинации столбцов при использовании оператора case
Я хочу получить количество уникальных комбинаций столбцов и отобразить их в виде объединенной строки значений столбцов. Я использую оператор case для каждого столбца, чтобы преобразовать значения столбца в меньшее количество элементов. В конечном итоге отображается правильная строка конкатенации, но счетчик основан на значениях до того, как они были изменены с помощью операторов CASE. В этом примере я хочу посчитать марку автомобиля, купленного государством. Любые ошибки в SQL происходят из-за моих плохих навыков набора текста. SQL с фактическими значениями работает. Я пробовал использовать временную таблицу, но не смог заставить ее работать.
Ожидаемый результат. Счетчик представляет собой объединенные значения из операторов CASE.
Toyota in Utah 5
Ford in California 6
Фактические результаты — отображаются объединенные значения CASE, но строки представляют собой количество значений до преобразования регистра.
Purchase Total
Toyota in Utah 2
Toyota in Utah 3
Ford in California 2
Ford in California 4
Данные столбца
Item City
86 Corolla Salt Lake
86 Corolla Salt Lake
Toyota Corolla Salt Lake City
Toyota Corolla Salt Lake City
Toyota Corolla Salt Lake City
F150 Los Angeles
F150 Los Angeles
Ford Taurus San Francisco
Ford Taurus San Francisco
Ford Taurus San Francisco
Ford Taurus San Francisco
SQL
select concat(
CASE
WHEN Item LIKE ('%Corolla%') THEN 'Toyota'
WHEN Item LIKE ('%Taco%') THEN 'Toyota'
WHEN Item LIKE ('F%') THEN 'Ford'
ELSE Item
END, -- END AS 'Brand' gives me a syntax error
' in ',
CASE
WHEN City LIKE ("Salt Lake%") THEN 'Utah'
WHEN City LIKE ("Las%") THEN 'California'
WHEN City LIKE ("San%") THEN 'California'
ELSE City
END)
AS Purchase, count(*) as Total
FROM PurchaseOrders
GROUP BY Item, City
ORDER BY Purchase
Почему вы используете здесь LIKE? Это может привести к очень плохой производительности, и я не понимаю, зачем это нужно. Есть ли в вашей таблице дополнительные столбцы? Штат должен быть сохранен в отдельной колонке и не определяться по городу. Это следует срочно исправить. Кроме того, должна быть отдельная графа с маркой (Ford, Toyota). Разные товары, конечно, могут иметь одну и ту же марку. Даже если вы не можете это исправить, не используйте LIKE, а используйте предложение IN, например ...IN ('Salt Lake', 'Salt Lake City',...)...
Какие СУБД вы используете?
Ваша модель данных плохо спроектирована. Вам нужно сохранить состояние для каждого города. Например. Сан-Антонио находится в Техасе и Лас-Вегас в Неваде. А Firebird — это/был Понтиак.
Почему я использую LIKE. Фактические данные — это IP-адрес. Каждый IP-адрес находится в одном из множества различных центров обработки данных. Запрос предназначен для отображения связи между центрами обработки данных, а не отдельными IP-адресами (серверами). Я использую CASE и LIKE для назначения IP-адресов соответствующему центру обработки данных. Я мог бы заменить LIKE на = и все соответствующие IP-адреса. Я также смотрю только на данные за последние три минуты.
Как сказал Алрайди в моем предыдущем комментарии, у вас будет высокий риск получить неверные результаты, если вы не улучшите структуру своей БД, не добавите отдельные столбцы для штата и бренда и не избавитесь от этой LIKE ерунды.
Ответы 3
Изменять
GROUP BY Item, City
К
GROUP BY 1
Какие группы по столбцу номер 1.
Вы группировали по нескольким столбцам, которые ваше выражение отображало в одно и то же значение, но имело разные комбинации значений и поэтому возвращалось как отдельные строки.
Проблема в «группировке по». У вас должна быть возможность группировать по полю покупки, которое вы создаете:
select
concat(
CASE
WHEN Item LIKE ('%Corolla%') THEN 'Toyota'
WHEN Item LIKE ('%Taco%') THEN 'Toyota'
WHEN Item LIKE ('F%') THEN 'Ford'
ELSE Item
END,
' in ',
CASE
WHEN City LIKE ("Salt Lake%") THEN 'Utah'
WHEN City LIKE ("Las%") THEN 'California'
WHEN City LIKE ("San%") THEN 'California'
ELSE City
end
)AS Purchase,
count(*) as Total
FROM
PurchaseOrders
GROUP BY
Purchase
ORDER BY
Purchase
Это предположение, специфичное для продукта. В целом и в стандарте ISO/ANSI SQL это невозможно.
Я использую SQL Server 2014. Ему не нравится группа по покупке. Каждое выражение GROUP BY должно содержать хотя бы один столбец, который не является внешней ссылкой.
Поместите выражения случая покупки и т. д. в производную таблицу (т. е. подзапрос предложения FROM). ГРУППИРОВАТЬ ПО результату:
select Purchase, count(*)
from
(
select concat(
CASE
WHEN Item LIKE ('%Corolla%') THEN 'Toyota'
WHEN Item LIKE ('%Taco%') THEN 'Toyota'
WHEN Item LIKE ('F%') THEN 'Ford'
ELSE Item
END, -- END AS 'Brand' gives me a syntax error
' in ',
CASE
WHEN City LIKE ("Salt Lake%") THEN 'Utah'
WHEN City LIKE ("Las%") THEN 'California'
WHEN City LIKE ("San%") THEN 'California'
ELSE City
END) AS Purchase
FROM PurchaseOrders
) dt
GROUP BY Purchase
ORDER BY Purchase
Это сработало отлично. Тоже отличное объяснение.
Поместите все свое выражение лица в
group by. Это группировка по исходному столбцу (с тем же именем), а не по тому, что вы предполагали.