Блог

У меня есть таблица (DeviceOS2), и я хотел бы получить наиболее частое значение для каждого столбца (ОС и устройства) для каждого идентификатора.

 ID      OS      Device

123     OSX     Mac 
123     OSX     PC  
123     OSX     PC  
123     Android Tablet
  

Желаемый результат:

 ID      OS      Device

123     OSX     PC  
  

Однако мой код теперь дает мне следующее:

 ID       OS            Device

123      Android       Tablet
123      OSX           Mac
123      OSX           PC
  

Похоже, он подбирает каждую комбинацию.

Текущий код (T-SQL):

 Select 
ID,
OS,
Device

FROM(
Select 
ID,
OS,
Device
FROM DeviceOS2
Group By ID,OS,Device) a 
Group By ID,OS,Device
  

Попробуйте это:

 select top 1 with ties a.ID, a.OS,a.Device
from (
select d.ID, d.OS, d.Device, ROW_NUMBER () over (partition by d.OS, d.Device order by id) rnk
from DeviceOS2 d)a
order by a.rnk desc
  

Обновить

Если вам нужно наиболее частое значение для каждого идентификатора:

 select c.ID,c.OS,c.Device from (
select d.ID, d.OS, d.Device, ROW_NUMBER () over (partition by d.id, d.OS, d.Device order by id) rnk
from DeviceOS2 d)c
join 
(
select  a.ID,max(a.rnk) AS rnk
from (
select d.ID, d.OS, d.Device, ROW_NUMBER () over (partition by d.id, d.OS, d.Device order by id) rnk
from DeviceOS2 d)a
group by a.ID) a
on c.ID = a.ID and a.rnk = c.rnk
  

Вы могли бы использовать:

 SELECT TOP 1 WITH TIES *
FROM tab
ORDER BY COUNT(*) OVER(PARITIION BY ID,OS) DESC
  

Это называется режимом. Вы можете использовать оконные функции:

 select o.*
from (select os, device, count(*) as cnt,
             row_number() over (partition by os order by count(*) desc) as seqnum
      from DeviceOS2
      group by os, device
     ) o
where seqnum = 1;
  

Если вам нужна наиболее частая комбинация, используйте:

 select os, device, count(*) as cnt
from DeviceOS2
group by os, device
order by count(*) desc
fetch first 1 row only;
  

(или используйте select top (1) , если вы предпочитаете).

Редактировать:

Для вашего отредактированного вопроса:

 select o.*
from (select os, device, count(*) as cnt,
             row_number() over (partition by os order by count(*) desc) as seqnum
      from DeviceOS2
      group by os, device
     ) o
where seqnum = 1;
  

Если вам нужна наиболее частая комбинация, тогда запрос немного сложнее. Один метод — это две агрегации:

 select o.id,
       max(case case when o.seqnum = 1 then os end) as most_frequent_os,
      max(case case when d.seqnum = 1 then device end) as most_frequent_device
from (select id, os, count(*) as cnt,
             row_number() over (partition by id order by count(*) desc) as seqnum
      from DeviceOS2
      group by id, os
     ) o join
     (select id, device, count(*) as cnt,
             row_number() over (partition by id order by count(*) desc) as seqnum
      from DeviceOS2
      group by id, device
     ) d
     on d.id = o.id