Группа SQL Server путем исключения некоторых значений
У меня есть такие записи, как показано ниже:
ID Val Amount
1 0 3
2 0 3
3 0 4
4 1 2
5 1 3
6 2 3
7 2 4
Я хочу сгруппировать эти данные по столбцу Val и получить сумму (количество), но не группировать те, у которых Val = 0.
Набор результатов, который мне нужен, выглядит следующим образом:
Val Amount
0 3
0 3
0 4
1 5
2 7
Я сделал это двумя способами, но ни один из них не кажется лучшим:
Первый заключается в использовании объединений, например, сначала есть те, у которых Val = 0, затем группируются те, у которых Val <> 0, и объединяются два набора результатов.
Второй немного лучше. Назовем данные, которые у нас есть в таблице, @Table:
WITH g AS
(
SELECT Val, Amount, CASE WHEN Val = '0' then Val + ID
else Val END A FROM @table
)
SELECT CASE WHEN A LIKE '0%' THEN 0 ELSE A END AS A, SUM(Amount)
FROM g
GROUP BY A
Это также работает, но необходимость объединения со столбцом идентификатора (или raw_number) и использование левой функции для его удаления - не лучшая практика.
Так что я ищу лучший подход, который будет лучше выглядеть и работать лучше.
Я работаю над SQL Server 2008, но я открыт для любых решений, требующих более новых версий.
Ответы 2
Используйте здесь союз. Верхняя часть приведенного ниже объединения находит совокупные количества значений, которые не равны нулю, а нижняя часть приносит записи с нулевым значением, а не агрегированные.
SELECT Val, SUM(Amount) AS Amount
FROM g
WHERE Val <> 0
GROUP BY Val
UNION ALL
SELECT Val, Amount
FROM g
WHERE Val = 0
ORDER BY Val;
Демо
Логически правильным здесь является объединение, потому что вы агрегируете часть таблицы, а не другую. Чтобы получить гладкий ответ, проверьте первый запрос, заданный @giorgos.
Самый короткий способ сделать это:
SELECT Val, SUM(Amount)
FROM mytable
GROUP BY Val, CASE WHEN Val = 0 THEN ID ELSE 0 END
Вы также можете сделать это с помощью оконных функций:
;WITH CTE AS (
SELECT ID, Val, Amount,
DENSE_RANK() OVER (PARTITION BY Val
ORDER BY CASE
WHEN Val = 0 THEN ID
ELSE 0
END) AS rank
FROM mytable
)
SELECT Val, SUM(Amount) AS total_amount
FROM CTE
GROUP BY Val, rank
Набор результатов, возвращаемый CTE:
ID Val Amount rank
--------------------
1 0 3 1
2 0 3 2
3 0 4 3
4 1 2 1
5 1 3 1
6 2 3 1
7 2 4 1
Таким образом, используя rank, вы можете различать значения 0 и остальных значений Val.
Вы можете использовать оба метода и посмотреть, как они соотносятся друг с другом с точки зрения производительности.
Ваш первый запрос должен быть принятым ответом, я думаю, +1. Но не второй.
Супер элегантный ответ, первый! Второй вариант, я полагаю, является обновлением моего подхода, и он тоже работает. Спасибо!
Я ищу решения без союзов, как я сказал в вопросе. Но рекомендуете ли вы использовать союзы и для повышения производительности?