sql统计占比和统计数量

在工作中经常遇到统计占比的需求,有时候还要把没有值得统计为0,如何写sql呢?

下面写一个小例子,作为参考,方便以后查阅.

数据准备:

create table t_group( id NUMBER not null,  name VARCHAR2(100));
alter table t_group add primary key (id);  

create table t_score( id NUMBER not null, g_id NUMBER not null, score NUMBER);
alter table t_score add primary key (id);  

insert into t_group (id, name) values ('1', 'a');
insert into t_group (id, name) values ('2', 'b');
insert into t_group (id, name) values ('3', 'c');
insert into t_group (id, name) values ('4', 'd');
insert into t_group (id, name) values ('5', 'e');
commit;

insert into t_score (id,g_id, score) values ('1','1', '81');
insert into t_score (id,g_id, score) values ('2','1', '73');
insert into t_score (id,g_id, score) values ('3','2', '78');
insert into t_score (id,g_id, score) values ('4','3', '92');
commit;

/* 需求1.求每个gropu的得分在总得分中的占比 */

select tg.name,
       sum(ts1.score) / (select sum(ts.score) from t_score ts) * 100 as perc
  from t_score ts1 join t_group tg on ts1.g_id = tg.id
 group by tg.name

/* 需求2.统计所有group在总得分中的占比,要求得分为0的数据也统计到结果中,显示为数值0 */

--思路一:用case when 转换结果.
--最先想到的是直接在join上做文章,用right join,但这个显示的结果中占比为空而不是数值0   
select name,
       (case
         when perc is null then
          0
         else
          perc
       end) as perc
  from (select tg.name,
               sum(ts1.score) / (select sum(ts.score) from t_score ts) * 100 as perc
          from t_score ts1
         right join t_group tg
            on ts1.g_id = tg.id
         group by tg.name)
 order by name;
         
--思路二: 使用union all + 0 补充缺位 + group by
select tg1.name, sum(TT.perc)
  from (select ts1.g_id,
               sum(ts1.score) / (select sum(ts.score) from t_score ts) * 100 as perc
          from t_score ts1
         group by g_id
        union all
        select tg.id as g_id, 0 as perc from t_group tg) TT
  join t_group tg1
    on TT.g_id = tg1.id
 group by tg1.name
order by tg1.name;

/* 需求3.按得分统计出 优良中差等级的分别有多少条数据,没有数据的等级显示为0 */

select TT.lev, sum(TT.cou)
  from (select lev, count(lev) cou
          from (select (case
                         when score >= 90 then
                          '优'
                         when score >= 80 then
                          '良'
                         when score >= 70 then
                          '中'
                         else
                          '差'
                       end) as lev
                  from t_score) T
         group by lev
        union all (select '优' as lev, 0 as cou
                    from dual
                  union all
                  select '良' as lev, 0 as cou
                    from dual
                  union all
                  select '中' as lev, 0 as cou
                    from dual
                  union all
                  select '差' as lev, 0 as cou from dual)) TT
 group by TT.lev;