Sql server подсчет экземпляров наиболее частых данных

Я не верю, что то, что мне нужно сделать, слишком сложно, Но, привет, сегодня утро понедельника, и мне бы пригодился совет. В основном я выполняю агрегированный запрос в SQL Server в следующем формате:

SELECT [Data1], COUNT(*), MAX([Data2])
FROM [Source]
GROUP BY [Data1]

Однако мне также нужно 4-е поле. Поле, в котором подсчитывается, сколько раз встречается значение MAX([Data2]).

Я приведу здесь небольшой пример:

|Data 1|Data 2|
|1     |x     |
|3     |p     |
|1     |z     |
|3     |f     |
|1     |x     |
|1     |b     |
|2     |h     |
|1     |o     |
|2     |h     |
|1     |x     |
|3     |f     |
|2     |h     |
|1     |z     |

Необходимо произвести вывод:

|Data1|Count|Max|Occurances|
|1    |7    |x  |3         |
|2    |3    |h  |3         |
|3    |3    |f  |2         |

Любая помощь будет оценена по достоинству.

1
0
62
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

Что-то вроде этого должно помочь -

;WITH CTE AS
(SELECT [Data1], COUNT(*) AS Data_Count, MAX([Data2]) AS Data_Max
FROM [Source]
GROUP BY [Data1])
SELECT [Data1],Data_Count,Data_Max,COUNT(Data_Max) AS Occurances
FROM CTE 
GROUP BY [Data1],Data_Count,Data_Max

Примечание: Я бы не стал использовать SQL Keywords в качестве имен / псевдонимов столбцов.

Вы можете попробовать использовать оконная функция с cte, чтобы это сделать.

используйте CTE, чтобы получить COUNTOccurances по столбцу [Data 1] и [Data 2]. затем используйте другой CTE2, сделайте порядок номеров строк по Occurances desc, что означает максимальное количество.

;with cte as (
SELECT [Data 1],
       [Data 2],
       COUNT(*) OVER (PARTITION BY [Data 1] ORDER BY [Data 1]) [count],
       COUNT(*) OVER (PARTITION BY [Data 2],[Data 1] ORDER BY [Data 2]) Occurances
FROM T
),cte2 as (
    SELECT  
        [Data 1], 
        [Data 2],
        [count],
        Occurances,row_number() over(partition by [Data 1] order by Occurances desc) rn
    FROM cte
)

select  [Data 1], 
        [Data 2],
        [count], 
        Occurances
from cte2
where rn = 1

sqlfiddle

Результат

Data 1  Data 2  count   Occurances
1       x         7     3
2       h         3     3
3       f         3     2
Ответ принят как подходящий

То, что вы ищете, имеет имя в статистике. Вам нужен режим и частота режима.

Я бы подошел к этому, используя два уровня агрегации:

select data1, sum(cnt) as cnt,
       max(case when seqnum = 1 then data2 end) as mode,
       max(cnt) as mode_cnt
from (select data1, data2, count(*) as cnt,
             row_number() over (partition by data1 order by count(*) desc) as seqnum
      from t
      group by data1, data2
     ) t
group by data1;

вы можете попробовать это.

DECLARE @Source AS TABLE([Data1] INT,[Data2] VARCHAR(5))
INSERT INTO @Source VALUES
(1, 'x'), 
(3, 'p'),
(1, 'z'),
(3, 'f'),
(1, 'x'),
(1, 'b'),
(2, 'h'),
(1, 'o'),
(2, 'h'),
(1, 'x'),
(3, 'f'),
(2, 'h'),
(1, 'z')

;WITH T AS (
SELECT [Data1] , 
    COUNT(*) OVER(PARTITION BY [Data1]) [Count], 
    [Data2],    
    COUNT(*) OVER(PARTITION BY [Data2]) Occurances
FROM @Source
)
SELECT TOP 1 WITH TIES [Data1], [Count], [Data2] [Max], Occurances
FROM T 
ORDER BY ROW_NUMBER() OVER (PARTITION BY [Data1] ORDER BY Occurances DESC) 

Результат:

Data1       Count       Max   Occurances
----------- ----------- ----- -----------
1           7           x     3
2           3           h     3
3           3           f     2

Другие вопросы по теме