sql语言中group by和case when及sum(case when)的结合使用

我不是女神ヾ 2021-06-26 16:06 884阅读 0赞

## `group by`和`case when`结合使用 ##

如下示代码块，`group by` 按`case when`得到的列分组计算，且`select`时，`select` 中的`case when` 与`group by`中保持一致。

select case when substr(upper(depend_name),1,4)='JCW_' then lower(substr(depend_name,5,length(depend_name))) else lower(depend_name)end as table_name,
              sum( case when dt>=date_add(CURRENT_DATE(),-1) then 1 else 0 end) as tb_call_times1,    
              sum( case when dt>=date_add(CURRENT_DATE(),-8) then 1 else 0 end) as tb_call_times7, 
              count(1) as tb_call_times30,
              concat(max(dt),' 00:00:00') as access_time
        from odm.odm_inn_etl_sched_trigger_depend_s_d where dt>add_months(CURRENT_DATE(),-1) and enable=1
        group by case when substr(upper(depend_name),1,4)='JCW_' then lower(substr(depend_name,5,length(depend_name))) else lower(depend_name)end

## `sum`和`case when`结合使用 ##

在`sum()`中使用`case when`，非常简化语句，避免多次连接查询。

select depend_name,
                 sum( case when dt>=date_add(CURRENT_DATE(),-1) then 1 else 0 end) as tb_call_times1,    
                 sum( case when dt>=date_add(CURRENT_DATE(),-8) then 1 else 0 end) as tb_call_times7, 
                 count(1) as tb_call_times30,
                 concat(max(dt),' 00:00:00') as access_time
                from odm.odm_inn_etl_sched_trigger_depend_s_d where dt>add_months(CURRENT_DATE(),-1) and enable=1   
                group by depend_name

## 复习`case when`的使用 ##

`case when` 有两种用法，等值查询和非等值查询。

--等值查询
    select 
    case sex 
    when 1 then 'male'
    when 2 then 'female'
    else 'mixed'
    end as sexual,sex
    from tbl

select 
    case  
    when price>100 then 'expensive'
    when price<=100 then 'cheap'
    else '异常值'
    end as price，价格分类
    from tbl

--------------------

## 使用`group by`和`case when` 的两个例子 ##

### 例子1 ###

create table testScore    
    (       
       tname varchar(30) null,    
       ttype varchar(10) null,    
       tscor int null   
    );    
     
    insert into testScore values ('张三','语文',80);   
    insert into testScore values ('张三','数学',98);    
    insert into testScore values ('张三','英语',65);    
    insert into testScore values ('李四','语文',70);    
    insert into testScore values ('李四','数学',80);    
    insert into testScore values ('李四','英语',90);    
     
     
    select
        tname as '姓名' ,     
        max(case ttype when '语文' then tscor else 0 end) '语文',     
        max(case ttype when '数学' then tscor else 0 end) '数学',     
        max(case ttype when '英语' then tscor else 0 end) '英语'     
    from testScore     
    group by tname

结果为：

### 例子2 ###

select 
          tname as '姓名',
          case
              when ttype='数学' then '理科'
              else '文科'
          end as '科别',
          sum(tscor) as '总分'
    from testscore
    group by 
          tname,
          case
              when ttype='数学' then '理科'
              else '文科'
          end

结果为：

--------------------

## `Group By`和`Case When`的结合使用 ##

下面是数据源。  
![在这里插入图片描述][20210401163452460.png]  
图一

现在要按B和C来组合分类，分别计算每种组合情况下D和E的比值，并且按照横轴为C，纵轴为B的形式展示这些比值。期望的结果是下面的。  
![在这里插入图片描述][2021040116351231.png]  
图二

对应需要对两个字段进行组合分类，最容易想到的方式就是将B和C两个字段直接放到`Group By`后面就可以了，例如使用文章末尾贴出的SQL1语句，得到下图。  
![在这里插入图片描述][2021040116353082.png]  
图三

但是这样得到的结果是将B、C的组合平铺来展示的。如果想要的是B和C分别作为一个表格的横轴和纵轴来展示（如图二），需要怎么办呢？这个时候最容易想到的是用临时表，在临时表之上二次查找，如文章末尾的SQL2所示。那么就可以得到下面的结果。  
![在这里插入图片描述][20210401163550145.png]  
图四

SQL2解决了问题，但是思路比较粗狂，sql里面有没有高级的语法来实现这个功能呢？的确是有的，如SQL3所示，这里用到了`case when`。得到的结果如下。可以看到和SQL2得到的结果是一样的。

![在这里插入图片描述][20210401163601404.png]  
图五

但是其实`case when`貌似只能用在`sum`函数里面的时候，最后的`Group By`才对`sum`内部的数据才起作用。`avg`和`count`都不行，本质的原因不知道，但是`avg`和`count`都与计数有关，估计计数的函数内部是不敏感`Group By`后面的字段的。

sql列表

SQL1：

Select sum(D)/sum(E),B,C from test.newrain group by B,C

SQL2：

Select t1.C1,t2.C2,t1.Bi from
    (select sum(D)/sum(E) as C1, B as Bi from test.newrain where C=1 group by B)t1,
    (select sum(D)/sum(E) as C2, B as Bi from test.newrain  where C=2 group by B)t2
    where t1.Bi=t2.Bi;

SQL3：

SELECT 
    sum(case when C=1 then D else 0 end)/sum(case when C=1 then E else 0 end) as C1,
    sum(case when C=2 then D else 0 end)/sum(case when C=2 then E else 0 end) as C2,B
    from test.newrain
    group by B

--------------------

## `case when ,MAX(),group by` ##

### `case when`用与新增列 ###

![在这里插入图片描述][20210401165742450.png]  
可以看到，这样无法只保留一行语文、数学、英语的成绩

### 为了保留一行成绩信息，引入`group by` 分组 ###

![在这里插入图片描述][20210401165920764.png]

这样就会出现只检索分组字段的第一条记录，这样就抹掉了其他两门课的成绩。

### 引入`max()`函数，改变这个难题 ###

![在这里插入图片描述][20210401165956823.png]  
可以看出，每个人都取得了全部的成绩，因为在使用`case when`时，每个学生新增三列，只有一个是有值的，通过取最大值，分组后将最大值放到这一行记录上。

[20210401163452460.png]: /images/20210626/e172228eaf5f4ae88d52dc097e6fd47c.png
[2021040116351231.png]: /images/20210626/8e7359b593544115b46cb8751462beeb.png
[2021040116353082.png]: /images/20210626/4c5ad27be0dc4ee4aa2e61420d72ae66.png
[20210401163550145.png]: /images/20210626/7a8192d20aa94f8c8f591bd2db792e6a.png
[20210401163601404.png]: /images/20210626/22426f934f2343f1be8c05d88685bec8.png
[20210401165742450.png]: /images/20210626/95f15ca8718444aea320f325876cfb2d.png
[20210401165920764.png]: /images/20210626/b39ffe833ed34f15adc495b21e59d5bd.png
[20210401165956823.png]: /images/20210626/63d5567aeef6468c817aa11c43eb6a21.png