No creo que lo que necesito hacer sea demasiado complicado, pero bueno, es lunes por la mañana y podría usar un poco de consejo. Básicamente estoy haciendo una consulta agregada en SQL Server en el siguiente formato:

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

Sin embargo, también necesito un cuarto campo. Un campo que cuenta la cantidad de veces que aparece el valor MAX([Data2]).

Haré un ejemplo rápido aquí:

|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     |

Necesita producir la salida:

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

Cualquier ayuda sería apreciada.

1
Kron 10 sep. 2018 a las 11:04

4 respuestas

La mejor respuesta

Lo que buscas tiene un nombre en las estadísticas. Desea el modo y la frecuencia del modo.

Me acercaría a esto usando dos niveles de agregación:

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;
2
Gordon Linoff 10 sep. 2018 a las 08:51

Puedes probar esto.

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) 

Resultado:

Data1       Count       Max   Occurances
----------- ----------- ----- -----------
1           7           x     3
2           3           h     3
3           3           f     2
0
Serkan Arslan 10 sep. 2018 a las 09:12

Puede intentar utilizar función de ventana con cte para hacerlo.

Use CTE obtener COUNT Occurances por [Data 1] y [Data 2] columna. luego use otro CTE2 para ordenar los números de fila por Occurances desc, que significa la cantidad máxima.

;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

Resultado

Data 1  Data 2  count   Occurances
1       x         7     3
2       h         3     3
3       f         3     2
0
D-Shih 10 sep. 2018 a las 08:34

Algo como esto debería ayudar:

;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

Nota: evitaría usar SQL Keywords como nombres / alias de columna.

0
Abhishek 10 sep. 2018 a las 08:10