大数据技术之高频面试题（十六）：手写HQL面试题

问题导读：
1、hive如何查询各自区组的money排名前十的账号？
2、如何用一条SQL语句查询出每门课都大于80分的学生姓名？
3、如何查询给出所有购入商品为两种或两种以上的购物人记录？
4、如何删除除了自动编号不同, 其他都相同的学生冗余信息？


上一篇：大数据技术之高频面试题（十五）：JUC和模拟考试

15.1 手写HQL 第1题
表结构：uid,subject_id,score
求：找出所有科目成绩都大于某一学科平均成绩的用户


15.2 手写HQL 第2题
我们有如下的用户访问数据


要求使用SQL统计出每个用户的累积访问次数，如下表所示：


15.3 手写HQL 第3题
有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
1）每个店铺的UV（访客数）
2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

15.4 手写HQL 第4题
已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)

15.5 手写HQL 第5题
有一个5000万的用户文件(user_id，name，age)，一个2亿记录的用户看电影的记录文件(user_id，url)，统计各年龄段观看电影的次数

15.6 手写HQL 第6题
有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
日期 用户 年龄
11,test_1,23
11,test_2,19
11,test_3,39
11,test_1,23
11,test_3,39
11,test_1,23
12,test_2,19
13,test_1,23

15.7 手写HQL 第7题
请用sql写出所有用户中在今年10月份第一次购买商品的金额，表ordertable字段（购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid）

15.8 手写HQL 第8题
有一个线上服务器访问日志格式如下（用sql答题）
时间                    接口                         ip地址
2016-11-09 11：22：05    /api/user/login                  110.23.5.33
2016-11-09 11：23：10    /api/user/detail                  57.3.2.16
.....
2016-11-09 23：59：40    /api/user/login                  200.6.5.166
求11月9号下午14点（14-15点），访问api/user/login接口的top10的ip地址

15.9 手写HQL 第9题
有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10）

CREATE TABIE `account` 
(
    `dist_id` int（11）
    DEFAULT NULL COMMENT '区组id'，
    `account` varchar（100）DEFAULT NULL COMMENT '账号' ,
    `gold` int（11）DEFAULT NULL COMMENT '金币' 
    PRIMARY KEY （`dist_id`，`account_id`），
）ENGINE=InnoDB DEFAULT CHARSET-utf8
复制代码

15.10 手写HQL 第10题
1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）
（1）会员表有字段memberid（会员id，主键）credits（积分）；
（2）销售表有字段memberid（会员id，外键）购买金额（MNAccount）；
（3）退货表中有字段memberid（会员id，外键）退货金额（RMNAccount）；
2）业务说明：
（1）销售表中的销售记录可以是会员购买，也可是非会员购买。（即销售表中的memberid可以为空）
（2）销售表中的一个会员可以有多条购买记录
（3）退货表中的退货记录可以是会员，也可是非会员4、一个会员可以有一条或多条退货记录
查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits）

15.11 手写HQL 第11题
现在有三个表student（学生表）、course(课程表)、score（成绩单），结构如下：

create table student
(
    id bigint comment ‘学号’，
    name string comment ‘姓名’,
    age bigint comment ‘年龄’
);
create table course
(
    cid string comment ‘课程号，001/002格式’,
    cname string comment ‘课程名’
);
Create table score
(
    Id bigint comment ‘学号’,
    cid string comment ‘课程号’,
    score bigint comment ‘成绩’
) partitioned by(event_day string)
复制代码

其中score中的id、cid，分别是student、course中对应的列请根据上面的表结构，回答下面的问题
1）请将本地文件（/home/users/test/20190301.csv）文件，加载到分区表score的20190301分区中，并覆盖之前的数据
2）查出平均成绩大于60分的学生的姓名、年龄、平均成绩
3）查出没有‘001’课程成绩的学生的姓名、年龄
4）查出有‘001’\’002’这两门课程下，成绩排名前3的学生的姓名、年龄
5）创建新的表score_20190317，并存入score表中20190317分区的数据
6）描述一下union和union all的区别，以及在mysql和HQL中用法的不同之处？
7）简单描述一下lateral view语法在HQL中的应用场景，并写一个HQL实例


15.12 手写HQL 第12题
1.用一条SQL语句查询出每门课都大于80分的学生姓名
name   kecheng   fenshu
张三    语文    81
张三    数学    75
李四    语文    76
李四    数学     90
王五    语文    81
王五    数学    100
王五    英语    90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
B：select name from table group by name having min(fenshu)>80

2. 学生表 如下:
自动编号   学号  姓名 课程编号 课程名称 分数
1     2005001 张三   0001   数学   69
2     2005002 李四   0001   数学   89
3     2005001 张三   0001   数学   69
删除除了自动编号不同, 其他都相同的学生冗余信息

答案:

delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)
复制代码

3.一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.

答：

select a.name, b.name
from team a, team b 
where a.name < b.name
复制代码

4.面试题：怎么把这样一个
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1  m2  m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

答案

select year, 
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year
复制代码

*********************************************************************

5.说明：复制表(只复制结构,源表名：a新表名：b)

SQL: select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容)
ORACLE:create table b
As
Select * from a where 1=2
复制代码

[<>（不等于）(SQL Server Compact)
比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。]

6.
原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句

select courseid, coursename ,score ,if(score>=60, "pass","fail")  as mark from course
复制代码

7.表名：购物信息

购物人      商品名称     数量
A            甲          2
B            乙          4
C            丙          1
A            丁          2
B            丙          5
……
复制代码

给出所有购入商品为两种或两种以上的购物人记录

答：

select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);
复制代码

8.

info 表
date result
2005-05-09 win
2005-05-09 lose 
2005-05-09 lose 
2005-05-09 lose 
2005-05-10 win 
2005-05-10 lose 
2005-05-10 lose 
复制代码

如果要生成下列结果, 该如何写sql语句?

   　　    win lose
2005-05-09  2   2 
2005-05-10  1   2 
复制代码

答案：

(1) select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date; 
(2) select a.date, a.result as win, b.result as lose 
　　from 
　　(select date, count(result) as result from info where result = "win" group by date) as a 
　　join 
　　(select date, count(result) as result from info where result = "lose" group by date) as b 
on a.date = b.date;
复制代码

最新经典文章，欢迎关注公众号