T-sql GROUP BY с COUNT, а затем включить MAX из COUNT

Ответ 1

Механически существует несколько способов сделать это. Вы можете использовать переменную temp tables/table. Другой способ - вложенные запросы и/или CTE, как показано в @Aaron_Bertrand. Третий способ - использовать WINDOWED FUNCTIONS, такие как...

SELECT    CarName,
          COUNT(*) as theCount,
          MAX(Count(*)) OVER(PARTITION BY 'foo') as MaxPerGroup
FROM      dbo.tbl_Cars
GROUP BY CarName

A DISFAVORED (читать лишенный) четвертый способ использует ключевое слово COMPUTE как таковое...

SELECT   CarID, CarName, Count(*)
FROM     dbo.tbl_Cars
GROUP BY CarID, CarName 
COMPUTE MAX(Count(*))   

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

Быстрый

Теперь следующая проблема - это то, что "лучший/самый быстрый/самый легкий". Я сразу думаю о indexed view. Поскольку @Aaron мягко напомнил мне, индексированные представления имеют всевозможные ограничения. Вышеупомянутая стратегия, однако, позволяет вам создать индексный вид на SELECT... FROM..GROUP BY. Затем, выбрав из индексированного представления, примените предложение WINDOWED FUNCTION.

Не зная больше, однако, о вашем дизайне будет сложно, если кто-нибудь скажет вам, что лучше всего. Вы получите световые запросы с индексированного представления. Тем не менее, эта производительность идет по цене. Цена - затраты на обслуживание. Если базовая таблица является объектом большого количества операций вставки/обновления/удаления, обслуживание индексированного представления приведет к снижению производительности в других областях.

Если вы поделитесь немного больше о вашем случае использования и шаблонах доступа к данным, люди смогут поделиться более глубоким пониманием.

Тест MICRO PERFORMANCE

Итак, я сгенерировал небольшие данные script и посмотрел на номера профилировщика sql для производительности CTE против оконных функций. Это микро-тест, поэтому попробуйте некоторые реальные цифры в вашей системе под реальной нагрузкой.

Генерация данных:

Create table Cars ( CarID int identity (1,1) primary key, 
                    CarName varchar(20), 
                    value int)
GO
insert into Cars (CarName, value)
values  ('Buick', 100),
        ('Ford', 10),
        ('Buick', 300),     
        ('Buick', 100),
        ('Pontiac', 300),       
        ('Bmw', 100),
        ('Mecedes', 300),       
        ('Chevy', 300),     
        ('Buick', 100),
        ('Ford', 200);
GO 1000

Этот script создает 10 000 строк. Затем я запускал каждый из четырех следующих запросов несколько раз:

--just group by
select  CarName,COUNT(*) countThis
FROM    Cars
GROUP BY CarName        

--group by with compute (BAD BAD DEVELOPER!)
select  CarName,COUNT(*) countThis
FROM    Cars
GROUP BY CarName        
COMPUTE  MAX(Count(*));

-- windowed aggregates...
SELECT  CarName,
        COUNT(*) as theCount,
        MAX(Count(*)) OVER(PARTITION BY 'foo') as MaxInAnyGroup
FROM Cars
GROUP BY CarName        

--CTE version
;WITH x AS (
  SELECT   CarName,
           COUNT(*) AS Total
  FROM     Cars
  GROUP BY CarName
)
SELECT x.CarName, x.Total, x2.[Max Total]
FROM x CROSS JOIN (
  SELECT [Max Total] = MAX(Total) FROM x
) AS x2;

После выполнения вышеуказанных запросов я создал индексированное представление в запросе "только группа по" выше. Затем я выполнил запрос в индексированном представлении, который выполнил MAX(Count(*)) OVER(PARTITION BY 'foo'.

СРЕДНИЕ РЕЗУЛЬТАТЫ

Query                      CPU       Reads     Duration   
--------------------------------------------------------
Group By                   15        31        7 ms  
Group & Compute            15        31        7 ms
Windowed Functions         14        56        8 ms 
Common Table Exp.          16        62       15 ms
Windowed on Indexed View    0        24        0 ms

Очевидно, это микро-бенчмарк и только слегка поучительный, поэтому возьмите его за то, что он стоит.

Ответ 2

Здесь один из способов:

;WITH x AS
(
  SELECT   CarID
         , CarName
         , COUNT(*) AS Total
  FROM     dbo.tbl_Cars
  GROUP BY CarID, CarName
)
SELECT x.CarID, x.CarName, x.Total, x2.[Max Total]
FROM x CROSS JOIN
(
  SELECT [Max Total] = MAX(Total) FROM x
) AS x2;

Ответ 3

SQL Server 2008 R2 и более новые версии, вы можете использовать:

GROUP BY CarID, CarName WITH ROLLUP