sql - 查找 sql 中最常见的值并按从高到低排序 (asc)

Question

我有一个简单的查询。

    select id, patient_id, diagnosis from dbo.diabetes_rx_tracker

group by id, patient_id, diagnosis

最常见的诊断代码需要出现在列表顶部并带有计数。

我尝试使用计数函数。

但它会将每个患者的所有值返回为 1，而不是相加。

select id, patient_id, count(diagnosis) from dbo.diabetes_rx_tracker

group by id, patient_id, diagnosis

在编辑 group by 语句时，我也不断抛出错误。

任何帮助将不胜感激。谢谢。

Answer 1

看来您聚合得不够。 GROUP 中的每个字段代表一个与其他字段一起聚合的字段。因此，这只会向您显示按 ID、按患者、按诊断的诊断。不是很有帮助。

要获取最常见的诊断周期，请从分组中删除 id 和 Patient_id:

select
  count(diagnosis)
from dbo.diabetes_rx_tracker 
group by 
  diagnosis 
order by 
  count(diagnosis) desc

要获取所有 ID 中最常见的代码，请从分组中删除 Patient_id 并选择:

select
  diagnosis, 
  id, 
  count(1) as CodeCount 
from dbo.diabetes_rx_tracker 
group by 
  diagnosis, 
  id 
order by 
  count(diagnosis) desc

更新:根据您的新标准，这就是您想要的。

select 
  id, 
  diagnosis, 
  ct 
from (
    select 
      row_number() OVER (PARTITION BY id ORDER BY count(diagnosis) desc ) as rn, 
      count(diagnosis) as ct, 
      diagnosis, 
      id
    from dbo.diabetes_rx_tracker 
    group by 
      id, 
      diagnosis
    ) ranked
where 
  rn = 1
order by
  CT desc