【博学谷学习记录】超强总结，用心分享|HiveSQL面试题实战(二)|详细的步骤解析

第6题：电商购买金额统计实战

数据准备

/*
userid money paymenttime orderid
001,100.00,2017-10-01,123
001,200.00,2017-10-02,124
002,500.00,2017-10-01,125
001,100.00,2017-11-01,126
*/

请用sql写出所有用户中在今年10月份第一次购买商品的金额

with t0 as (select *, row_number() over (partition by userid order by paymenttime) rk
            from order2
            where month(paymenttime) = 10),
     t1 as (select * from t0 where rk = 1)
select *
from t1;

第7题：教育领域SQL实战

表结构

/*
BOOK
序号 字段名称 字段描述 字段类型
1 BOOK_ID 总编号 文本
2 SORT 分类号 文本
3 BOOK_NAME 书名 文本
4 WRITER 作者 文本
5 OUTPUT 出版单位 文本
6 PRICE 单价 数值（保留小数点后2位）

READER
序号 字段名称 字段描述 字段类型
1 READER_ID 借书证号 文本
2 COMPANY 单位 文本
3 NAME 姓名 文本
4 SEX 性别 文本
5 GRADE 职称 文本
6 ADDR 地址 文本

BORROW LOG
序号 字段名称 字段描述 字段类型
1 READER_ID 借书证号 文本
2 BOOK_ID 总编号 文本
3 BORROW_DATE 借书日期 日期
*/

（1）创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。

create table book
(
    BOOK_ID   string comment '总编号',
    SORT_ID   string comment '分类号',
    BOOK_NAME string comment '书名',
    WRITER    string comment '作者',
    OUTPUT    string comment '出版单位',
    PRICE     decimal(10, 2) comment '单价'
);
INSERT overwrite TABLE book
VALUES ('001', 'TP391', '信息处理', 'author1', '机械工业出版社', '20'),
       ('002', 'TP392', '数据库', 'author12', '科学出版社', '15'),
       ('003', 'TP393', '计算机网络', 'author3', '高等教育出版社', '29'),
       ('004', 'TP399', '微机原理', 'author4', '科学出版社', '39'),
       ('005', 'C931', '管理信息系统', 'author5', '机械工业出版社', '40'),
       ('006', 'C932', '运筹学', 'author6', '高等教育出版社', '55');

create table reader
(
    READER_ID string comment '借书证号',
    COMPANY   string comment '单位',
    NAME      string comment '姓名',
    SEX       string comment '性别',
    GRADE     string comment '职称',
    ADDR      string comment '地址'
);
INSERT overwrite TABLE reader
VALUES ('0001', '阿里巴巴', '李杰克', '男', 'vp', 'addr1'),
       ('0002', '百度', '孙罗宾', '男', 'vp', 'addr2'),
       ('0003', '腾讯', '王托尼', '男', 'vp', 'addr3'),
       ('0004', '京东', '张佳鹏', '男', 'cfo', 'addr4'),
       ('0005', '网易', '张三', '女', 'ceo', 'addr5'),
       ('0006', '搜狐', '李四', '女', 'ceo', 'addr6');

create table borrow_log
(
    READER_ID   string comment '借书证号',
    BOOK_ID     string comment '总编号',
    BORROW_DATE date comment '借书日期'
);
INSERT overwrite TABLE borrow_log
VALUES ('0001', '002', '2019-10-14'),
       ('0002', '001', '2019-10-13'),
       ('0002', '007', '2019-10-15'),
       ('0003', '005', '2019-09-14'),
       ('0004', '006', '2019-08-15'),
       ('0005', '003', '2019-10-10'),
       ('0006', '004', '2019-17-13');

（2）找出姓李的读者姓名（NAME）和所在单位（COMPANY）。

select NAME, COMPANY
from reader
where NAME like '李%';

（3）查找“高等教育出版社”的所有图书名称（BOOK_NAME）及单价（PRICE），结果按单价降序排序。

select BOOK_NAME, PRICE
from book
where OUTPUT = '高等教育出版社'
order by PRICE desc;

（4）查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），结果按出版单位（OUTPUT）和单价（PRICE）升序排序。

select SORT_ID, OUTPUT, PRICE
from book
where PRICE > 10
  and PRICE <= 20
order by OUTPUT, PRICE;

（5）查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。

select NAME, COMPANY
from reader r
         join borrow_log bl on r.READER_ID = bl.READER_ID;

（6）求”科学出版社”图书的最高单价、最低单价、平均单价。

select max(PRICE) max, min(PRICE) min, avg(PRICE) avg
from book
where OUTPUT = '科学出版社';

（7）找出当前至少借阅了2本图书（大于等于2本）的读者姓名及其所在单位。

with t0 as (select reader_id from borrow_log group by reader_id having count(READER_ID) >= 2),
     t1 as (select NAME, COMPANY
            from t0
                     join reader r on t0.READER_ID = r.READER_ID)
select *
from t1;

（8）考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_L0G_BAK中。

create table borrow_log_bak as
select *
from borrow_log;

（9）现在需要将原Oracle数据库中数据迁移至Hive仓库，请写出“图书”在Hive中的建表语句（Hive实现，提示：列分隔符|,数据表数据需要外部导入：分区分别以month_part、day_part 命名）

create table book_partition
(
    BOOK_ID   string comment '总编号',
    SORT_ID   string comment '分类号',
    BOOK_NAME string comment '书名',
    WRITER    string comment '作者',
    OUTPUT    string comment '出版单位',
    PRICE     decimal(10, 2) comment '单价'
) partitioned by (month_part string,day_part string) row format delimited fields terminated by '|';

（10）Hive中有表A，现在需要将表A的月分区 201505 中 user＿id为20000的user＿dinner字段更新为bonc8920，其他用户user＿dinner字段数据不变，请列出更新的方法步骤。（Hive实现，提示：Hlive中无update语法，请通过其他办法进行数据更新）

insert overwrite table A
select user_id, 'bonc8920' user_dinner
from A
where user_id = '2000'
union
select user_id, user_dinner
from A
where user_id != '2000';

第8题：服务日志SQL统计

数据准备

/*
时间                  接口          ip地址
2016-11-9 14:22 /api/user/login 110.23.5.33
2016-11-9 14:23 /api/user/detail 57.3.2.16
2016-11-9 15:59 /api/user/login 200.6.5.166
*/

CREATE TABLE server_log
(
    `date`    string,
    interface string,
    ip        string
);
INSERT INTO TABLE server_log
VALUES ('2016-11-09 11:22:05', '/api/user/login', '110.23.5.23'),
       ('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'),
       ('2016-11-09 11:14:23', '/api/user/login', '136.79.47.70'),
       ('2016-11-09 11:15:23', '/api/user/detail', '94.144.143.141'),
       ('2016-11-09 11:16:23', '/api/user/login', '197.161.8.206'),
       ('2016-11-09 12:14:23', '/api/user/detail', '240.227.107.145'),
       ('2016-11-09 13:14:23', '/api/user/login', '79.130.122.205'),
       ('2016-11-09 14:14:23', '/api/user/detail', '65.228.251.189'),
       ('2016-11-09 14:15:23', '/api/user/detail', '245.23.122.44'),
       ('2016-11-09 14:17:23', '/api/user/detail', '22.74.142.137'),
       ('2016-11-09 14:19:23', '/api/user/detail', '54.93.212.87'),
       ('2016-11-09 14:20:23', '/api/user/detail', '218.15.167.248'),
       ('2016-11-09 14:24:23', '/api/user/detail', '20.117.19.75'),
       ('2016-11-09 15:14:23', '/api/user/login', '183.162.66.97'),
       ('2016-11-09 16:14:23', '/api/user/login', '108.181.245.147'),
       ('2016-11-09 14:17:23', '/api/user/login', '22.74.142.137'),
       ('2016-11-09 14:19:23', '/api/user/login', '22.74.142.137');

求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址

with t0 as (select ip, interface, date_format(`date`, 'MM-dd HH:mm')
            from server_log
            where date_format(regexp_replace(`date`, '/', '-'), 'MM-dd HH') = '11-09 14'
              and interface = '/api/user/login')
select *
from t0;

第9题：充值日志SQL实战

数据准备

/*
有一个充值日志表credit_log，字段如下：
    `dist_id` int '区组id',
    `account` string '账号',
    `money` int '充值金额',`
    create_time` string '订单时间
*/

CREATE TABLE charge_log
(
    dist_id     string COMMENT '区组id',
    account     string COMMENT '账号',
    `money`     decimal(10, 2) COMMENT '充值金额',
    create_time string COMMENT '订单时间'
);

INSERT INTO TABLE charge_log
VALUES ('1', '11', 100006, '2019-01-02 13:00:01'),
       ('1', '22', 110000, '2019-01-02 13:00:02'),
       ('1', '33', 102000, '2019-01-02 13:00:03'),
       ('1', '44', 100300, '2019-01-02 13:00:04'),
       ('1', '55', 100040, '2019-01-02 13:00:05'),
       ('1', '66', 100005, '2019-01-02 13:00:06'),
       ('1', '77', 180000, '2019-01-03 13:00:07'),
       ('1', '88', 106000, '2019-01-02 13:00:08'),
       ('1', '99', 100400, '2019-01-02 13:00:09'),
       ('1', '12', 100030, '2019-01-02 13:00:10'),
       ('1', '13', 100003, '2019-01-02 13:00:20'),
       ('1', '14', 100020, '2019-01-02 13:00:30'),
       ('1', '15', 100500, '2019-01-02 13:00:40'),
       ('1', '16', 106000, '2019-01-02 13:00:50'),
       ('1', '17', 100800, '2019-01-02 13:00:59'),
       ('2', '18', 100800, '2019-01-02 13:00:11'),
       ('2', '19', 100030, '2019-01-02 13:00:12'),
       ('2', '10', 100000, '2019-01-02 13:00:13'),
       ('2', '45', 100010, '2019-01-02 13:00:14'),
       ('2', '78', 100070, '2019-01-02 13:00:15');
select *
from charge_log;

查询充值日志表2019年01月02号每个区组下充值额最大的账号，要求结果：区组id，账号，金额，充值时间

with t0 as (select * from charge_log where date_format(create_time, 'yyyy-MM-dd') = '2019-01-02'),
     t1 as (select *, row_number() over (partition by dist_id order by money desc ) rk from t0),
     t2 as (select * from t1 where rk = 1)
select *
from t2;

第10题：电商分组TopK实战

数据准备

/*
有一个账号表如下
    dist_id string '区组id',
    account string '账号',
    gold int '金币'
*/

CREATE TABLE account
(
    `dist_id` string COMMENT '区组id',
    `account` string COMMENT '账号',
    `gold`    int COMMENT '金币'
);
INSERT INTO TABLE account
VALUES ('1', '77', 18),
       ('1', '88', 106),
       ('1', '99', 10),
       ('1', '12', 13),
       ('1', '13', 14),
       ('1', '14', 25),
       ('1', '15', 36),
       ('1', '16', 12),
       ('1', '17', 158),
       ('2', '18', 12),
       ('2', '19', 44),
       ('2', '10', 66),
       ('2', '45', 80),
       ('2', '78', 98);
select *
from account;

查询各自区组的gold排名前十的账号

with t0 as (select *, row_number() over (partition by dist_id order by gold desc ) rk from account),
     t1 as (select * from t0 where rk <= 10)
select *
from t1;