hive查询练习

矫情吗；* 2024-03-16 11:44 46阅读 0赞

### 练习1 ###

2022-08-07
    1,liuyan
    2,tangyan
    3,jinlian
    4,dalang
    5,ximenqing
    
    2022-08-08
    1,liuyan
    2,tangyan
    4,dalang
    6,wusong
    
    -- 创建分区表记录每天用户登陆信息
    create table tb_login
    (
        uid  int,
        name string
    ) partitioned by (dt string)
        row format delimited fields terminated by ",";
    
    load data local inpath '/root/2022-08-07' into table tb_login partition (dt = '2022-08-07');
    load data local inpath '/root/2022-08-08' into table tb_login partition (dt = '2022-08-08');
    
    
    select *
    from tb_login;
    
    -- 查询7号和8号都登陆人的信息
    select t1.uid, t1.name
    from (select * from tb_login where dt = '2022-08-07') t1
             join
             (select * from tb_login where dt = '2022-08-08') t2
             on t1.uid = t2.uid;
    
    -- 查询7号登陆 8号没登录人的信息
    select *
    from (select * from tb_login where dt = '2022-08-07') t1
             left join
             (select * from tb_login where dt = '2022-08-08') t2
             on t1.uid = t2.uid
    where t2.uid is null;
    -- 查询8号登陆 7号没登陆人的信息
    select t2.*
    from (select * from tb_login where dt = '2022-08-07') t1
             right join
             (select * from tb_login where dt = '2022-08-08') t2
             on t1.uid = t2.uid
    where t1.uid is null;
    -- 查询7号登陆 8号没登陆 和 8号登陆 7号没登陆人的信息
    select *
    from (select * from tb_login where dt = '2022-08-07') t1
             full join
             (select * from tb_login where dt = '2022-08-08') t2
             on t1.uid = t2.uid
    where t1.uid is null
       or t2.uid is null;

**练习2**

![d75fe2b8ec7fb7fbdb4b750a03a99505.png][]

**建表准备数据**

vi mark.txt
    liuyan,语文,100
    liuyan,数学,99
    liuyan,英语,100
    tangyan,语文,80
    tangyan,数学,98
    tangyan,英语,60
    
    create table stu_mark
    (
        sname   string,
        subject string,
        score   double
    ) row format delimited fields terminated by ",";
    
    load data local inpath '/root/mark.txt' into table stu_mark;
    
    select * from stu_mark;

-- 使用case when 查询每个人的语文成绩
    SELECT sname, case  subject WHEN '语文' THEN score else 0 END as `语文` FROM stu_mark ;
    
    -- 查询每个人的每科成绩 
    SELECT sname,
                   case  subject WHEN '语文' THEN score else 0 END as `语文`,
                   case  subject WHEN '数学' THEN score else 0 END as `数学`,
                   case  subject WHEN '英语' THEN score else 0 END as `英语`
    FROM stu_mark ;
    -- 得到最终结果
    SELECT sname,
                   max(case  subject WHEN '语文' THEN score else 0 END) as `语文`,
                   max(case  subject WHEN '数学' THEN score else 0 END) as `数学`,
                   max(case  subject WHEN '英语' THEN score else 0 END) as `英语`
    FROM stu_mark group by sname;

**练习3**

yg.txt
    uid,name,age,gender
    1,liuyan,23,M
    2,tangyan,33,F
    3,jinlian,28,M
    4,dalang,37,F
    5,ximenqing,17,M
    
    bm.txt
    bid,bname
    1,财务部
    2,销售部
    3,后勤部
    4,技术部
    
    gz.txt
    uid,jb,jj,tc,bid
    
    1,2000,3000,5000,1
    2,1000,4000,1000,2
    3,5000,1000,5000,1
    4,4000,300,7000,3
    
    -- 创建员工表
    create table yg
    (
        uid    int,
        name   string,
        age    int,
        gender string
    )
        row format delimited fields terminated by ',';
    -- 加载员工数据    
    load data local inpath '/root/yg.txt' into table yg;
    
    -- 创建部门表
    create table bm
    (
        bid   int,
        bname string
    )
        row format delimited fields terminated by ',';
    -- 加载部门数据    
    load data local inpath '/root/bm.txt' into table bm;
    -- 创建工资表
    create table gz
    (
        uid int,
        jb  double,
        jj  double,
        tc  double,
        bid int
    )
        row format delimited fields terminated by ',';
    -- 加载工资数据    
    load data local inpath '/root/gz.txt' into table gz;
    
    select * from yg;
    select * from gz;
    select * from bm;

1.查询每个部门的员工数 男员工个数 女员工个数 显示部门名称

-- 查询每个员工的 工号 姓名 性别 部门id 部门名称.
    select yg.uid,
           yg.name,
           yg.gender,
           bm.bid,
           bm.bname
    from yg
             inner join gz on yg.uid = gz.uid
             inner join bm on gz.bid = bm.bid;
    -- 查询每个部门的总人数  部门名称  部门总人数
    with t1 as (select yg.uid,
                       yg.name,
                       yg.gender,
                       bm.bname
                from yg
                         inner join gz on yg.uid = gz.uid
                         inner join bm on gz.bid = bm.bid)
    select t1.bname,
           count(bname) `总人数`
    from t1
    group by t1.bname;
    -- 查询每个部门的总人数  部门名称  部门总人数 男员工个数 女员工个数
    with t1 as (select yg.uid,
                       yg.name,
                       yg.gender,
                       bm.bname
                from yg
                         inner join gz on yg.uid = gz.uid
                         inner join bm on gz.bid = bm.bid)
    select t1.bname,
           count(bname)                  `总人数`,
           sum(`if`(gender = 'M', 1, 0)) `男`,
           sum(`if`(gender = 'F', 1, 0)) `女`
    from t1
    group by t1.bname;
    ------------------------------------------------------------------------------------------------
    
    -- 查询 每个部门的总人数  男员工个数 女员工个数 
    select bid,
           count(bid)                    total_num,
           sum(`if`(gender = 'M', 1, 0)) nan,
           sum(`if`(gender = 'F', 1, 0)) nv
    from yg
             inner join gz on yg.uid = gz.uid
    group by bid;
    
    -- 显示部门名称
    with t1 as (select bid,
                       count(bid)                    total_num,
                       sum(`if`(gender = 'M', 1, 0)) nan,
                       sum(`if`(gender = 'F', 1, 0)) nv
                from yg
                         inner join gz on yg.uid = gz.uid
                group by bid)
    select bm.bname, t1.*
    from t1
             inner join bm on t1.bid = bm.bid;
    
    +-----------+---------+---------------+---------+--------+
    | bm.bname  | t1.bid  | t1.total_num  | t1.nan  | t1.nv  |
    +-----------+---------+---------------+---------+--------+
    | 财务部     | 1       | 2             | 2       | 0      |
    | 销售部     | 2       | 1             | 0       | 1      |
    | 后勤部     | 3       | 1             | 0       | 1      |
    +-----------+---------+---------------+---------+--------+

2.查询每种性别的总工资

-- 查询每个员工的工资 工号 姓名 性别 基本工资 奖金 提成
    select yg.uid,
           yg.name,
           gz.jb,
           gz.jj,
           gz.tc
    from yg
             left join gz on yg.uid = gz.uid;
    -- 查询每个员工的总工资  工号 姓名 性别 总工资
    select yg.uid,
           yg.name,
           yg.gender,
           (gz.jb + gz.jj + gz.tc) sal
    from yg
             left join gz on yg.uid = gz.uid;
    -- 查询每种性别的总工资
    select yg.gender,
           sum(gz.jb + gz.jj + gz.tc) total_sal
    from yg
             left join gz on yg.uid = gz.uid
    group by gender;
    
    with t1 as (select yg.uid,
                       yg.name,
                       yg.gender,
                       (gz.jb + gz.jj + gz.tc) sal
                from yg
                         left join gz on yg.uid = gz.uid)
    select gender, sum(sal)
    from t1
    group by gender;
         
    +------------+------------+
    | yg.gender  | total_sal  |
    +------------+------------+
    | F          | 17300.0    |
    | M          | 21000.0    |
    +------------+------------+

3.查询每个员工的中文性别,年龄阶段

-- 查询每个员工信息  及 中文性别
    -- if
    select *, if(gender == 'M', '男', '女') as ch_gender
    from yg;
    -- case when 第一种格式
    select *,
           case gender
               when 'M' then '男'
               else '女' end as ch_gender
    from yg;
    -- case when 第二种格式
    select *,
           case
               when gender == 'M' then '男'
               else '女' end as ch_gender
    from yg;
    
    -- 查询每个员工信息 及 年龄阶段 
    -- if
    select *,
           if(age >= 10 and age <= 20, '10~20',
              if(age > 20 and age <= 30, '20~30', if(age > 30 and age <= 40, '30~40', 'other'))) as age_stage
    from yg;
    -- case when
    select *,
           case
               when age >= 10 and age < 20 then '10~20'
               when age >= 20 and age < 30 then '20~30'
               when age >= 30 and age < 40 then '30~40'
               else 'other'
               end as age_stage
    from yg;
    -- 查询每个员工信息  中文性别  年龄阶段
    select *,
           if(gender == 'M', '男', '女') as ch_gender,
           case
               when age >= 10 and age < 20 then '10~20'
               when age >= 20 and age < 30 then '20~30'
               when age >= 30 and age < 40 then '30~40'
               else 'other'
               end                     as age_stage
    from yg;
    
    with t1 as (select *, if(gender == 'M', '男', '女') as ch_gender from yg)
    select *,
           case
               when age >= 10 and age < 20 then '10~20'
               when age >= 20 and age < 30 then '20~30'
               when age >= 30 and age < 40 then '30~40'
               else 'other'
               end as age_stage
    from t1;

4.查询每个年龄段的总工资

-- 年龄段  总工资
    select t1.age_stage,
           sum(gz.jb + gz.jj + gz.tc)
    from (select *,
                 case
                     when age >= 10 and age < 20 then '10~20'
                     when age >= 20 and age < 30 then '20~30'
                     when age >= 30 and age < 40 then '30~40'
                     else 'other'
                     end as age_stage
          from yg) t1
             left join gz
                       on t1.uid = gz.uid
    group by t1.age_stage;
    
    -- cte
    with t1 as (select *,
                       case
                           when age >= 10 and age < 20 then '10~20'
                           when age >= 20 and age < 30 then '20~30'
                           when age >= 30 and age < 40 then '30~40'
                           else 'other'
                           end as age_stage
                from yg)
    select t1.age_stage, sum(gz.jb + gz.jj + gz.tc)
    from t1
             left join gz
                       on t1.uid = gz.uid
    group by t1.age_stage;
    
    +---------------+----------+
    | t1.age_stage  |   _c1    |
    +---------------+----------+
    | 10~20         | NULL     |
    | 20~30         | 21000.0  |
    | 30~40         | 17300.0  |
    +---------------+----------+

5 求每个人名字工资组成部分中占比最高的工资类型

-- 查询工资表中 每个人  基本工资 奖金 提成 中的最高工资是多少
    select *,
           greatest(jb, jj, tc) max_sal
    from gz;
    
    -- 查询工资表中 每个人  基本工资 奖金 提成 最多的 是哪种类型
    select *,
           greatest(jb, jj, tc) max_sal,
           case
               when greatest(jb, jj, tc) == jb then 'jb'
               when greatest(jb, jj, tc) == jj then 'jj'
               when greatest(jb, jj, tc) == tc then 'tc'
               end as           max_type
    from gz;
    
    select *,
           greatest(jb, jj, tc) max_sal,
           case greatest(jb, jj, tc)
               when jb then 'jb'
               when jj then 'jj'
               when tc then 'tc'
               end as           max_type
    from gz;
    
    --  查询每个员工的 工号  姓名  最高工资类型及多少
    with t1 as (select *,
                       greatest(jb, jj, tc) max_sal,
                       case
                           when greatest(jb, jj, tc) == jb then 'jb'
                           when greatest(jb, jj, tc) == jj then 'jj'
                           when greatest(jb, jj, tc) == tc then 'tc'
                           end as           max_type
                from gz)
    select yg.uid,
           yg.name,
           t1.max_sal,
           t1.max_type
    from yg
             left join t1
                       on yg.uid = t1.uid;
    
    +---------+------------+-------------+--------------+
    | yg.uid  |  yg.name   | t1.max_sal  | t1.max_type  |
    +---------+------------+-------------+--------------+
    | 1       | liuyan     | 5000.0      | tc           |
    | 2       | tangyan    | 4000.0      | jj           |
    | 3       | jinlian    | 5000.0      | jb           |
    | 4       | dalang     | 7000.0      | tc           |
    | 5       | ximenqing  | NULL        | NULL         |
    +---------+------------+-------------+--------------+

[d75fe2b8ec7fb7fbdb4b750a03a99505.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/f40f940a197d40cd9b171fdf9b90cf55.png