hive02

查询练习

练习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

建表准备数据

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         |
+---------+------------+-------------+--------------+