大数据第57天—Mysql练习题12道之十二-百度-杨大伟

--第12题 百度

--现在有三个表student（学生表）、course(课程表)、score（成绩单）,结构如下：

 

create table test_twelve_student
(
    id bigint comment '学号',
    name string comment '姓名',
    age bigint comment '年龄'
)
row format delimited fields terminated by '\t';

create table test_twelve_course
(
    cid string comment '课程号,001/002格式',
    cname string comment '课程名'
)
row format delimited fields terminated by '\t';

Create table test_twelve_score
(
    id bigint comment '学号',
    cid string comment '课程号',
    score bigint comment '成绩'
)
row format delimited fields terminated by '\t';

 

--插入数据
insert into table test_twelve_student values (1001,'wsl1',21);
insert into table test_twelve_student values (1002,'wsl2',22);
insert into table test_twelve_student values (1003,'wsl3',23);
insert into table test_twelve_student values (1004,'wsl4',24);
insert into table test_twelve_student values (1005,'wsl5',25);

insert into table test_twelve_course values ('001','math');
insert into table test_twelve_course values ('002','English');
insert into table test_twelve_course values ('003','Chinese');
insert into table test_twelve_course values ('004','music');

insert into table test_twelve_score values (1001,'004',10);
insert into table test_twelve_score values (1002,'003',21);
insert into table test_twelve_score values (1003,'002',32);
insert into table test_twelve_score values (1004,'001',43);
insert into table test_twelve_score values (1005,'003',54);
insert into table test_twelve_score values (1001,'002',65);
insert into table test_twelve_score values (1002,'004',76);
insert into table test_twelve_score values (1003,'002',77);
insert into table test_twelve_score values (1001,'004',48);
insert into table test_twelve_score values (1002,'003',39);

--其中score中的id、cid,分别是student、course中对应的列请根据上面的表结构,回答下面的问题
--1）请将本地文件（/home/users/test/20190301.csv）文件,加载到分区表score的20190301分区中,并覆盖之前的数据
load data local inpath '/home/users/test/20190301.csv' overwrite into table test_twelve_score partition(event_day='20190301');
--2）查出平均成绩大于60分的学生的姓名、年龄、平均成绩

select
    stu.name,
    stu.age,
    t1.avg_score
from
test_twelve_student stu
join
(
    select
        id,
        avg(score) avg_score
    from test_twelve_score
    group by id
) t1
on t1.id = stu.id
where avg_score > 60;
--3）查出没有'001'课程成绩的学生的姓名、年龄
select
    stu.name,
    stu.age
from
test_twelve_student stu
join
(
    select
        id
    from test_twelve_score
    where cid != 001
    group by id
) t1
on stu.id = t1.id;
--4）查出有'001'\'002'这两门课程下,成绩排名前3的学生的姓名、年龄
select
    stu.name,
    stu.age
from
(
    select
        id,
        cid,
        score,
        rank() over(partition by cid order by score desc) ran
    from 
    test_twelve_score
    where cid = 001 or cid = 002
) t1
join test_twelve_student stu
on t1.id = stu.id
where ran <= 3;


--5）创建新的表score_20190317,并存入score表中20190317分区的数据
create table score_20190317
as select * from test_twelve_score where dt = '20190317';
--6）如果上面的score_20190317score表中,uid存在数据倾斜,请进行优化,查出在20190101-20190317中,学生的姓名、年龄、课程、课程的平均成绩
select
    stu.name,
    stu.age,
    cou.cname,
    t1.avg_score

from
(
    select 
        id,
        cid,
        avg(score) avg_score
    from test_twelve_score
    group by id,cid
    where dt >= '20190101' and dt <= '20190317'
) t1
left join test_twelve_student stu on t1.id = stu.id
left join test_twelve_course cou on t1.cid = cou.cid
--7）描述一下union和union all的区别,以及在mysql和HQL中用法的不同之处？

union会对数据进行排序去重，union all不会排序去重。
HQL中要求union或union all操作时必须保证select 集合的结果相同个数的列，并且每个列的类型是一样的。

--8）简单描述一下lateral view语法在HQL中的应用场景,并写一个HQL实例
-- 比如一个学生表为：
-- 学号  姓名  年龄  成绩（语文|数学|英语）
-- 001   张三  16     90，80，95
-- 需要实现效果：
-- 学号  成绩
-- 001 90
-- 001 80
-- 001 95


create table student(
`id` string,
`name` string,
`age` int,
`scores` array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';


select
    id,
    score
from
student lateral view explode(scores) tmp_score as score;