case when 条件判断函数 和 row_number 排序函数

日萌社

人工智能AI：Keras PyTorch MXNet TensorFlow PaddlePaddle 深度学习实战（不定时更新）

---

 

case when条件判断函数 和 row_number排序函数：
    1.表结构 desc employee;
        empid    int                                         
        deptid    int                                         
        sex    string                                      
        salary    double  

    2.表记录 select * from employee;
        empid  deptid    sex   salary    
        1       10      female  5500.0
        2       10      male    4500.0
        3       20      female  1900.0
        4       20      male    4800.0
        5       40      female  6500.0
        6       40      female  14500.0
        7       40      male    44500.0
        8       50      male    6500.0
        9       50      male    7500.0


    3.将员工按照薪资待遇划分等级 (小于5000为低等收入，5000-10000为中等收入，10000以上为高等收入)
      将员工按照性别打上标识 female女性为1, male男性为0
        select *,
        case 
        when salary < 5000 then "低等收入"  //小于5000为低等收入
        when salary>= 5000 and salary < 10000 then "中等收入"  //5000-10000为中等收入
        when salary > 10000 then "高等收入"    //10000以上为高等收入
        end as level, //end as 别名
        case sex
        when "female" then 1  //female女性为1
        when "male" then 0    //male男性为0    
        end as flag //end as 别名
        from employee;

      统计结果
        empid  deptid    sex   salary        sex
        1       10      female  5500.0  中等收入        1
        2       10      male    4500.0  低等收入        0
        3       20      female  1900.0  低等收入        1
        4       20      male    4800.0  低等收入        0
        5       40      female  6500.0  中等收入        1
        6       40      female  14500.0 高等收入        1
        7       40      male    44500.0 高等收入        0
        8       50      male    6500.0  中等收入        0
        9       50      male    7500.0  中等收入        0


    4.row_number()排序函数 统计每个部门薪资最高的员工信息（同一个部门的员工按照薪资进行降序排序）

        第一种写法：row_number() over(partition by 一个或多个分组列 order by 一个或多个排序列 asc/desc) as 别名  //如果不写asc/desc的话，默认为asc 
        第二种写法：row_number() over(distribute by 一个或多个分组列 sort by 一个或多个排序列 asc/desc) as 别名
        在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。


        select *,
        row_number() over(distribute by deptid sort by salary desc) rn from employee;
        //1.distribute by deptid sort by salary desc：按照部门deptid进行分组，每个分组内按照薪资即salary进行降序排序，即同一个部门的员工按照薪资进行降序排序
         //2.分组条件：distribute by deptid        排序条件：sort by salary desc
        //3.rn：为别名，代表每个分组中序号ID

      统计结果
        empid  deptid    sex    salary  rn
        1       10      female  5500.0  1
        2       10      male    4500.0  2
        4       20      male    4800.0  1
        3       20      female  1900.0  2
        7       40      male    44500.0 1
        6       40      female  14500.0 2
        5       40      female  6500.0  3
        9       50      male    7500.0  1
        8       50      male    6500.0  2
 
    5.直接取出rn的编号为1的记录，就是每个部门薪资最高的员工信息（获取出每个分组中薪资最高的员工信息，where条件为rn=1）
        select * 
        from (select *,
        row_number() over(distribute by deptid sort by salary desc) rn from employee) t where t.rn=1;
        //1.distribute by deptid sort by salary desc：按照部门deptid进行分组，每个分组内按照薪资即salary进行降序排序，即同一个部门的员工按照薪资进行降序排序
         //2.分组条件：distribute by deptid        排序条件：sort by salary desc
        //3.rn：为别名，代表每个分组中序号ID。
        //  t.rn=1：表示取每个分组中序号ID为1的数据

      统计结果
            empid  deptid    sex    salary  rn
         1       10      female  5500.0  1
        4       20      male    4800.0  1
        7       40      male    44500.0 1
        9       50      male    7500.0  1

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

CREATE TABLE 新表名 AS SELECT * from 已有表名
    依据已有表来创建新表，新表没有原表的唯一索引、索引，如果“select * from”语句无“where 条件”因此复制了源表的所有数据到目标表中
    1.只会复制表数据和表结构，不会有任何约束（例如：不会复制源表的唯一索引、索引；不会复制源表定义中的字段注释、字段的默认值等）。 
    2.当where 条件不成立时，只复制表结构，不复制任何数据。

   insert into 目标表名 select * from 已有表名：已有目标表，将已有表数据插入到目标表中