代码之家  ›  专栏  ›  技术社区  ›  David

如何使用与count distinct语句相关的百分比创建附加列

  •  1
  • David  · 技术社区  · 6 年前

    我试图在表格中查询每个不同的医学专业(例如肿瘤学家、儿科医生等),然后计算索赔的次数( claim_id )链接到它,我用这个:

    select distinct specialization, count(distinct claim_id) AS Claim_Totals
    from table1
    group by specialization
    order by Claim_Totals DESC
    

    不过,我还想增加一列,列出每个专业在表中所占的百分比(基于专业数量) 认领身份证 与之相关)。例如,如果总共有100项索赔,“心脏病专家”有25项索赔 认领身份证 与之相关的记录,“肿瘤学家”有15个,“普通外科医生”有10个,依此类推,我希望输出如下:

    specialization | Claims_Totals | PERCENTAGE
    ___________________________________________
    cardiologist       25               25%
    oncologist         15               15%
    general surgeon    10               10%
    
    2 回复  |  直到 6 年前
        1
  •  1
  •   Error_2646    6 年前

    你能做到吗?我不熟悉Barbaros的语法。如果这行得通的话,它会更简洁、更好。

    select specialization, count(distinct claim_id) AS Claim_Totals, count(distinct claim_id)/total_claims
    from table1 
    INNER JOIN ( SELECT COUNT(DISTINCT claim_id)*1.0000 total_claims AS total_claims 
                   FROM table1 ) TMP
       ON 1 = 1
    group by specialization
    order by Claim_Totals DESC
    
    
    select specialization, 
           count(distinct claim_id) AS claim_by_spec,
           count(distinct claim_id)/
           ( SELECT COUNT(DISTINCT claim_id)*1.0000
                   FROM table1 ) AS percentage_calc
    from table1 
    group by specialization
    order by Claim_Totals DESC
    
        2
  •  1
  •   Vamsi Prabhala    6 年前

    你可以用 sum(count(distinct)) over() 得到总体索赔,并在分母中使用它来得到百分比。

    select specialization
          ,count(distinct claim_id) AS Claim_Totals
          ,round(100*count(distinct claim_id)/sum(count(distinct claim_id)) over(),3) as percentage
    from table1
    group by specialization
    
        3
  •  0
  •   Barbaros Özhan    6 年前

    你可以用

    ,concat_ws('',count(distinct claim_id),'%') as percentage

    ,concat(count(distinct claim_id),'%') as percentage

    添加到选择列表的尾部

    顺便提一下 distinct 在此之前,选择列表中的专门化是多余的,因为它已经包含在group by列表中。

        4
  •  0
  •   Gordon Linoff    6 年前

    因为你正在使用 count(distinct) ,窗口函数就没那么有用了。你可以试试:

    select t1.specialization,
           count(distinct t1.claim_id) AS Claim_Totals,
           count(distinct t1.claim_id) / tt1.num_claims
    from table1 t1 cross join
         (select count(distinct claim_id) as num_claims
          from table1
         ) tt1
    group by t1.specialization
    order by Claim_Totals DESC