Hive SQL题库（中级）

已于 2023-06-27 16:23:32 修改
阅读量534 收藏
点赞数
分类专栏： hive 文章标签： sql hive 数据库
于 2023-06-26 18:08:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dyangel2013/article/details/131402942
版权

第1章 环境准备

1.1 用户信息表

1）表结构

user_id(用户id)

gender(性别)

birthday(生日)

101

男

1990-01-01

102

女

1991-02-01

103

女

1992-03-01

104

男

1993-04-01

 2）建表语句

hive> 
DROP TABLE IF EXISTS user_info;
create table user_info(
    `user_id`  string COMMENT '用户id',
    `gender`   string COMMENT '性别',
    `birthday` string COMMENT '生日'
) COMMENT '用户信息表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

3）数据装载

hive> 
insert overwrite table user_info
values ('101', '男', '1990-01-01'),
       ('102', '女', '1991-02-01'),
       ('103', '女', '1992-03-01'),
       ('104', '男', '1993-04-01'),
       ('105', '女', '1994-05-01'),
       ('106', '男', '1995-06-01'),
       ('107', '女', '1996-07-01'),
       ('108', '男', '1997-08-01'),
       ('109', '女', '1998-09-01'),
       ('1010', '男', '1999-10-01');

1.2 商品信息表

1）表结构

sku_id

(商品id)

name

(商品名称)

category_id

(分类id)

from_date

(上架日期)

price

(商品价格)

1

xiaomi 10

1

2020-01-01

2000

6

洗碗机

2

2020-02-01

2000

9

自行车

3

2020-01-01

1000

2）建表语句

hive> 
DROP TABLE IF EXISTS sku_info;
CREATE TABLE sku_info(
    `sku_id`      string COMMENT '商品id',
    `name`        string COMMENT '商品名称',
    `category_id` string COMMENT '所属分类id',
    `from_date`   string COMMENT '上架日期',
    `price`       double COMMENT '商品单价'
) COMMENT '商品属性表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 3）数据装载

hive> 
insert overwrite table sku_info
values ('1', 'xiaomi 10', '1', '2020-01-01', 2000),
       ('2', '手机壳', '1', '2020-02-01', 10),
       ('3', 'apple 12', '1', '2020-03-01', 5000),
       ('4', 'xiaomi 13', '1', '2020-04-01', 6000),
       ('5', '破壁机', '2', '2020-01-01', 500),
       ('6', '洗碗机', '2', '2020-02-01', 2000),
       ('7', '热水壶', '2', '2020-03-01', 100),
       ('8', '微波炉', '2', '2020-04-01', 600),
       ('9', '自行车', '3', '2020-01-01', 1000),
       ('10', '帐篷', '3', '2020-02-01', 100),
       ('11', '烧烤架', '3', '2020-02-01', 50),
       ('12', '遮阳伞', '3', '2020-03-01', 20);

1.3 商品分类信息表

1）表结构

category_id(分类id)

category_name(分类名称)

1

数码

2

厨卫

3

户外

2）建表语句

hive> 
DROP TABLE IF EXISTS category_info;
create table category_info(
    `category_id`   string,
    `category_name` string
) COMMENT '品类表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 3）数据装载

hive> 
insert overwrite table category_info
values ('1','数码'),
       ('2','厨卫'),
       ('3','户外');

1.4 订单信息表

1）表结构

order_id

(订单id)

user_id

(用户id)

create_date

(下单日期)

total_amount

(订单金额)

1

101

2021-09-30

29000.00

10

103

2020-10-02

28000.00

2）建表语句

hive> 
DROP TABLE IF EXISTS order_info;
create table order_info(
    `order_id`     string COMMENT '订单id',
    `user_id`      string COMMENT '用户id',
    `create_date`  string COMMENT '下单日期',
    `total_amount` decimal(16, 2) COMMENT '订单总金额'
) COMMENT '订单表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 3）数据装载

hive> 
insert overwrite table order_info
values ('1', '101', '2021-09-27', 29000.00),
       ('2', '101', '2021-09-28', 70500.00),
       ('3', '101', '2021-09-29', 43300.00),
       ('4', '101', '2021-09-30', 860.00),
       ('5', '102', '2021-10-01', 46180.00),
       ('6', '102', '2021-10-01', 50000.00),
       ('7', '102', '2021-10-01', 75500.00),
       ('8', '102', '2021-10-02', 6170.00),
       ('9', '103', '2021-10-02', 18580.00),
       ('10', '103', '2021-10-02', 28000.00),
       ('11', '103', '2021-10-02', 23400.00),
       ('12', '103', '2021-10-03', 5910.00),
       ('13', '104', '2021-10-03', 13000.00),
       ('14', '104', '2021-10-03', 69500.00),
       ('15', '104', '2021-10-03', 2000.00),
       ('16', '104', '2021-10-03', 5380.00),
       ('17', '105', '2021-10-04', 6210.00),
       ('18', '105', '2021-10-04', 68000.00),
       ('19', '105', '2021-10-04', 43100.00),
       ('20', '105', '2021-10-04', 2790.00),
       ('21', '106', '2021-10-04', 9390.00),
       ('22', '106', '2021-10-05', 58000.00),
       ('23', '106', '2021-10-05', 46600.00),
       ('24', '106', '2021-10-05', 5160.00),
       ('25', '107', '2021-10-05', 55350.00),
       ('26', '107', '2021-10-05', 14500.00),
       ('27', '107', '2021-10-06', 47400.00),
       ('28', '107', '2021-10-06', 6900.00),
       ('29', '108', '2021-10-06', 56570.00),
       ('30', '108', '2021-10-06', 44500.00),
       ('31', '108', '2021-10-07', 50800.00),
       ('32', '108', '2021-10-07', 3900.00),
       ('33', '109', '2021-10-07', 41480.00),
       ('34', '109', '2021-10-07', 88000.00),
       ('35', '109', '2020-10-08', 15000.00),
       ('36', '109', '2020-10-08', 9020.00),
       ('37', '1010', '2020-10-08', 9260.00),
       ('38', '1010', '2020-10-08', 12000.00),
       ('39', '1010', '2020-10-08', 23900.00),
       ('40', '1010', '2020-10-08', 6790.00);

1.5 订单明细表

1）表结构

order_detail_id

(订单明细id)

order_id

(订单id)

sku_id

(商品id)

create_date

(下单日期)

price

(商品单价)

sku_num

(商品件数)

1

1

1

2021-09-30

2000.00

2

2

1

3

2021-09-30

5000.00

5

22

10

4

2020-10-02

6000.00

1

23

10

5

2020-10-02

500.00

24

24

10

6

2020-10-02

2000.00

5

2）建表语句

hive> 
DROP TABLE IF EXISTS order_detail;
CREATE TABLE order_detail
(
    `order_detail_id` string COMMENT '订单明细id',
    `order_id`        string COMMENT '订单id',
    `sku_id`          string COMMENT '商品id',
    `create_date`     string COMMENT '下单日期',
    `price`           decimal(16, 2) COMMENT '下单时的商品单价',
    `sku_num`         int COMMENT '下单商品件数'
) COMMENT '订单明细表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

3）数据装载

hive> 
INSERT overwrite table order_detail
values ('1', '1', '1', '2021-09-27', 2000.00, 2),
       ('2', '1', '3', '2021-09-27', 5000.00, 5),
       ('3', '2', '4', '2021-09-28', 6000.00, 9),
       ('4', '2', '5', '2021-09-28', 500.00, 33),
       ('5', '3', '7', '2021-09-29', 100.00, 37),
       ('6', '3', '8', '2021-09-29', 600.00, 46),
       ('7', '3', '9', '2021-09-29', 1000.00, 12),
       ('8', '4', '12', '2021-09-30', 20.00, 43),
       ('9', '5', '1', '2021-10-01', 2000.00, 8),
       ('10', '5', '2', '2021-10-01', 10.00, 18),
       ('11', '5', '3', '2021-10-01', 5000.00, 6),
       ('12', '6', '4', '2021-10-01', 6000.00, 8),
       ('13', '6', '6', '2021-10-01', 2000.00, 1),
       ('14', '7', '7', '2021-10-01', 100.00, 17),
       ('15', '7', '8', '2021-10-01', 600.00, 48),
       ('16', '7', '9', '2021-10-01', 1000.00, 45),
       ('17', '8', '10', '2021-10-02', 100.00, 48),
       ('18', '8', '11', '2021-10-02', 50.00, 15),
       ('19', '8', '12', '2021-10-02', 20.00, 31),
       ('20', '9', '1', '2021-09-30', 2000.00, 9),
       ('21', '9', '2', '2021-10-02', 10.00, 5800),
       ('22', '10', '4', '2021-10-02', 6000.00, 1),
       ('23', '10', '5', '2021-10-02', 500.00, 24),
       ('24', '10', '6', '2021-10-02', 2000.00, 5),
       ('25', '11', '8', '2021-10-02', 600.00, 39),
       ('26', '12', '10', '2021-10-03', 100.00, 47),
       ('27', '12', '11', '2021-10-03', 50.00, 19),
       ('28', '12', '12', '2021-10-03', 20.00, 13000),
       ('29', '13', '1', '2021-10-03', 2000.00, 4),
       ('30', '13', '3', '2021-10-03', 5000.00, 1),
       ('31', '14', '4', '2021-10-03', 6000.00, 5),
       ('32', '14', '5', '2021-10-03', 500.00, 47),
       ('33', '14', '6', '2021-10-03', 2000.00, 8),
       ('34', '15', '7', '2021-10-03', 100.00, 20),
       ('35', '16', '10', '2021-10-03', 100.00, 22),
       ('36', '16', '11', '2021-10-03', 50.00, 42),
       ('37', '16', '12', '2021-10-03', 20.00, 7400),
       ('38', '17', '1', '2021-10-04', 2000.00, 3),
       ('39', '17', '2', '2021-10-04', 10.00, 21),
       ('40', '18', '4', '2021-10-04', 6000.00, 8),
       ('41', '18', '5', '2021-10-04', 500.00, 28),
       ('42', '18', '6', '2021-10-04', 2000.00, 3),
       ('43', '19', '7', '2021-10-04', 100.00, 55),
       ('44', '19', '8', '2021-10-04', 600.00, 11),
       ('45', '19', '9', '2021-10-04', 1000.00, 31),
       ('46', '20', '11', '2021-10-04', 50.00, 45),
       ('47', '20', '12', '2021-10-04', 20.00, 27),
       ('48', '21', '1', '2021-10-04', 2000.00, 2),
       ('49', '21', '2', '2021-10-04', 10.00, 39),
       ('50', '21', '3', '2021-10-04', 5000.00, 1),
       ('51', '22', '4', '2021-10-05', 6000.00, 8),
       ('52', '22', '5', '2021-10-05', 500.00, 20),
       ('53', '23', '7', '2021-10-05', 100.00, 58),
       ('54', '23', '8', '2021-10-05', 600.00, 18),
       ('55', '23', '9', '2021-10-05', 1000.00, 30),
       ('56', '24', '10', '2021-10-05', 100.00, 27),
       ('57', '24', '11', '2021-10-05', 50.00, 28),
       ('58', '24', '12', '2021-10-05', 20.00, 53),
       ('59', '25', '1', '2021-10-05', 2000.00, 5),
       ('60', '25', '2', '2021-10-05', 10.00, 35),
       ('61', '25', '3', '2021-10-05', 5000.00, 9),
       ('62', '26', '4', '2021-10-05', 6000.00, 1),
       ('63', '26', '5', '2021-10-05', 500.00, 13),
       ('64', '26', '6', '2021-10-05', 2000.00, 1),
       ('65', '27', '7', '2021-10-06', 100.00, 30),
       ('66', '27', '8', '2021-10-06', 600.00, 19),
       ('67', '27', '9', '2021-10-06', 1000.00, 33),
       ('68', '28', '10', '2021-10-06', 100.00, 37),
       ('69', '28', '11', '2021-10-06', 50.00, 46),
       ('70', '28', '12', '2021-10-06', 20.00, 45),
       ('71', '29', '1', '2021-10-06', 2000.00, 8),
       ('72', '29', '2', '2021-10-06', 10.00, 57),
       ('73', '29', '3', '2021-10-06', 5000.00, 8),
       ('74', '30', '4', '2021-10-06', 6000.00, 3),
       ('75', '30', '5', '2021-10-06', 500.00, 33),
       ('76', '30', '6', '2021-10-06', 2000.00, 5),
       ('77', '31', '8', '2021-10-07', 600.00, 13),
       ('78', '31', '9', '2021-10-07', 1000.00, 43),
       ('79', '32', '10', '2021-10-07', 100.00, 24),
       ('80', '32', '11', '2021-10-07', 50.00, 30),
       ('81', '33', '1', '2021-10-07', 2000.00, 8),
       ('82', '33', '2', '2021-10-07', 10.00, 48),
       ('83', '33', '3', '2021-10-07', 5000.00, 5),
       ('84', '34', '4', '2021-10-07', 6000.00, 10),
       ('85', '34', '5', '2021-10-07', 500.00, 44),
       ('86', '34', '6', '2021-10-07', 2000.00, 3),
       ('87', '35', '8', '2020-10-08', 600.00, 25),
       ('88', '36', '10', '2020-10-08', 100.00, 57),
       ('89', '36', '11', '2020-10-08', 50.00, 44),
       ('90', '36', '12', '2020-10-08', 20.00, 56),
       ('91', '37', '1', '2020-10-08', 2000.00, 2),
       ('92', '37', '2', '2020-10-08', 10.00, 26),
       ('93', '37', '3', '2020-10-08', 5000.00, 1),
       ('94', '38', '6', '2020-10-08', 2000.00, 6),
       ('95', '39', '7', '2020-10-08', 100.00, 35),
       ('96', '39', '8', '2020-10-08', 600.00, 34),
       ('97', '40', '10', '2020-10-08', 100.00, 37),
       ('98', '40', '11', '2020-10-08', 50.00, 51),
       ('99', '40', '12', '2020-10-08', 20.00, 27);

1.6 登录明细表

1）表结构

user_id(用户id)

ip_address(ip地址)

login_ts(登录时间)

logout_ts(登出时间)

101

180.149.130.161

2021-09-21 08:00:00

2021-09-27 08:30:00

102

120.245.11.2

2021-09-22 09:00:00

2021-09-27 09:30:00

103

27.184.97.3

2021-09-23 10:00:00

2021-09-27 10:30:00

2）建表语句

hive> 
DROP TABLE IF EXISTS user_login_detail;
CREATE TABLE user_login_detail
(
    `user_id`    string comment '用户id',
    `ip_address` string comment 'ip地址',
    `login_ts`   string comment '登录时间',
    `logout_ts`  string comment '登出时间'
) COMMENT '用户登录明细表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

3）数据装载

hive> 
INSERT overwrite table user_login_detail
VALUES ('101', '180.149.130.161', '2021-09-21 08:00:00', '2021-09-27 08:30:00'),
       ('101', '180.149.130.161', '2021-09-27 08:00:00', '2021-09-27 08:30:00'),
       ('101', '180.149.130.161', '2021-09-28 09:00:00', '2021-09-28 09:10:00'),
       ('101', '180.149.130.161', '2021-09-29 13:30:00', '2021-09-29 13:50:00'),
       ('101', '180.149.130.161', '2021-09-30 20:00:00', '2021-09-30 20:10:00'),
       ('102', '120.245.11.2', '2021-09-22 09:00:00', '2021-09-27 09:30:00'),
       ('102', '120.245.11.2', '2021-10-01 08:00:00', '2021-10-01 08:30:00'),
       ('102', '180.149.130.174', '2021-10-01 07:50:00', '2021-10-01 08:20:00'),
       ('102', '120.245.11.2', '2021-10-02 08:00:00', '2021-10-02 08:30:00'),
       ('103', '27.184.97.3', '2021-09-23 10:00:00', '2021-09-27 10:30:00'),
       ('103', '27.184.97.3', '2021-10-03 07:50:00', '2021-10-03 09:20:00'),
       ('104', '27.184.97.34', '2021-09-24 11:00:00', '2021-09-27 11:30:00'),
       ('104', '27.184.97.34', '2021-10-03 07:50:00', '2021-10-03 08:20:00'),
       ('104', '27.184.97.34', '2021-10-03 08:50:00', '2021-10-03 10:20:00'),
       ('104', '120.245.11.89', '2021-10-03 08:40:00', '2021-10-03 10:30:00'),
       ('105', '119.180.192.212', '2021-10-04 09:10:00', '2021-10-04 09:30:00'),
       ('106', '119.180.192.66', '2021-10-04 08:40:00', '2021-10-04 10:30:00'),
       ('106', '119.180.192.66', '2021-10-05 21:50:00', '2021-10-05 22:40:00'),
       ('107', '219.134.104.7', '2021-09-25 12:00:00', '2021-09-27 12:30:00'),
       ('107', '219.134.104.7', '2021-10-05 22:00:00', '2021-10-05 23:00:00'),
       ('107', '219.134.104.7', '2021-10-06 09:10:00', '2021-10-06 10:20:00'),
       ('107', '27.184.97.46', '2021-10-06 09:00:00', '2021-10-06 10:00:00'),
       ('108', '101.227.131.22', '2021-10-06 09:00:00', '2021-10-06 10:00:00'),
       ('108', '101.227.131.22', '2021-10-06 22:00:00', '2021-10-06 23:00:00'),
       ('109', '101.227.131.29', '2021-09-26 13:00:00', '2021-09-27 13:30:00'),
       ('109', '101.227.131.29', '2021-10-06 08:50:00', '2021-10-06 10:20:00'),
       ('109', '101.227.131.29', '2021-10-08 09:00:00', '2021-10-08 09:10:00'),
       ('1010', '119.180.192.10', '2021-09-27 14:00:00', '2021-09-27 14:30:00'),
       ('1010', '119.180.192.10', '2021-10-09 08:50:00', '2021-10-09 10:20:00');

1.7 商品价格变更明细表

1）表结构

sku_id(商品id)

new_price(本次变更之后的价格)

change_date(变更日期)

1

1900.00

2021-09-25

1

2000.00

2021-09-26

2

80.00

2021-09-29

2

10.00

2021-09-30

2）建表语句

hive> 
DROP TABLE IF EXISTS sku_price_modify_detail;
CREATE TABLE sku_price_modify_detail
(
    `sku_id`      string comment '商品id',
    `new_price`   decimal(16, 2) comment '更改后的价格',
    `change_date` string comment '变动日期'
) COMMENT '商品价格变更明细表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

3）数据装载

hive> 
insert overwrite table sku_price_modify_detail
values ('1', 1900, '2021-09-25'),
       ('1', 2000, '2021-09-26'),
       ('2', 80, '2021-09-29'),
       ('2', 10, '2021-09-30'),
       ('3', 4999, '2021-09-25'),
       ('3', 5000, '2021-09-26'),
       ('4', 5600, '2021-09-26'),
       ('4', 6000, '2021-09-27'),
       ('5', 490, '2021-09-27'),
       ('5', 500, '2021-09-28'),
       ('6', 1988, '2021-09-30'),
       ('6', 2000, '2021-10-01'),
       ('7', 88, '2021-09-28'),
       ('7', 100, '2021-09-29'),
       ('8', 800, '2021-09-28'),
       ('8', 600, '2021-09-29'),
       ('9', 1100, '2021-09-27'),
       ('9', 1000, '2021-09-28'),
       ('10', 90, '2021-10-01'),
       ('10', 100, '2021-10-02'),
       ('11', 66, '2021-10-01'),
       ('11', 50, '2021-10-02'),
       ('12', 35, '2021-09-28'),
       ('12', 20, '2021-09-29');

1.8 配送信息表

1）表结构

delivery_id

（运单id）

order_id

（订单id）

user_id

（用户id）

order_date

（下单日期）

custom_date

（期望配送日期）

1

1

101

2021-09-27

2021-09-29

2

2

101

2021-09-28

2021-09-28

3

3

101

2021-09-29

2021-09-30

2）建表语句

hive> 
DROP TABLE IF EXISTS delivery_info;
CREATE TABLE delivery_info
(
    `delivery_id` string comment '配送单id',
    `order_id`    string comment '订单id',
    `user_id`     string comment '用户id',
    `order_date`  string comment '下单日期',
    `custom_date` string comment '期望配送日期'
) COMMENT '邮寄信息表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 3）数据装载

hive> 
insert overwrite table delivery_info
values ('1', '1', '101', '2021-09-27', '2021-09-29'),
       ('2', '2', '101', '2021-09-28', '2021-09-28'),
       ('3', '3', '101', '2021-09-29', '2021-09-30'),
       ('4', '4', '101', '2021-09-30', '2021-10-01'),
       ('5', '5', '102', '2021-10-01', '2021-10-01'),
       ('6', '6', '102', '2021-10-01', '2021-10-01'),
       ('7', '7', '102', '2021-10-01', '2021-10-03'),
       ('8', '8', '102', '2021-10-02', '2021-10-02'),
       ('9', '9', '103', '2021-10-02', '2021-10-03'),
       ('10', '10', '103', '2021-10-02', '2021-10-04'),
       ('11', '11', '103', '2021-10-02', '2021-10-02'),
       ('12', '12', '103', '2021-10-03', '2021-10-03'),
       ('13', '13', '104', '2021-10-03', '2021-10-04'),
       ('14', '14', '104', '2021-10-03', '2021-10-04'),
       ('15', '15', '104', '2021-10-03', '2021-10-03'),
       ('16', '16', '104', '2021-10-03', '2021-10-03'),
       ('17', '17', '105', '2021-10-04', '2021-10-04'),
       ('18', '18', '105', '2021-10-04', '2021-10-06'),
       ('19', '19', '105', '2021-10-04', '2021-10-06'),
       ('20', '20', '105', '2021-10-04', '2021-10-04'),
       ('21', '21', '106', '2021-10-04', '2021-10-04'),
       ('22', '22', '106', '2021-10-05', '2021-10-05'),
       ('23', '23', '106', '2021-10-05', '2021-10-05'),
       ('24', '24', '106', '2021-10-05', '2021-10-07'),
       ('25', '25', '107', '2021-10-05', '2021-10-05'),
       ('26', '26', '107', '2021-10-05', '2021-10-06'),
       ('27', '27', '107', '2021-10-06', '2021-10-06'),
       ('28', '28', '107', '2021-10-06', '2021-10-07'),
       ('29', '29', '108', '2021-10-06', '2021-10-06'),
       ('30', '30', '108', '2021-10-06', '2021-10-06'),
       ('31', '31', '108', '2021-10-07', '2021-10-09'),
       ('32', '32', '108', '2021-10-07', '2021-10-09'),
       ('33', '33', '109', '2021-10-07', '2021-10-08'),
       ('34', '34', '109', '2021-10-07', '2021-10-08'),
       ('35', '35', '109', '2021-10-08', '2021-10-10'),
       ('36', '36', '109', '2021-10-08', '2021-10-09'),
       ('37', '37', '1010', '2021-10-08', '2021-10-10'),
       ('38', '38', '1010', '2021-10-08', '2021-10-10'),
       ('39', '39', '1010', '2021-10-08', '2021-10-09'),
       ('40', '40', '1010', '2021-10-08', '2021-10-09');

1.9 好友关系表 

1）表结构

user1_id（用户1 id）

user2_id（用户2 id）

101

1010

101

108

101

106

注：表中一行数据中的两个user_id，表示两个用户互为好友。

2）建表语句

hive> 
DROP TABLE IF EXISTS friendship_info;
CREATE TABLE friendship_info(
    `user1_id` string comment '用户1id',
    `user2_id` string comment '用户2id'
) COMMENT '用户关系表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 3）数据装载

hive> 
insert overwrite table friendship_info
values ('101', '1010'),
       ('101', '108'),
       ('101', '106'),
       ('101', '104'),
       ('101', '102'),
       ('102', '1010'),
       ('102', '108'),
       ('102', '106'),
       ('102', '104'),
       ('102', '102'),
       ('103', '1010'),
       ('103', '108'),
       ('103', '106'),
       ('103', '104'),
       ('103', '102'),
       ('104', '1010'),
       ('104', '108'),
       ('104', '106'),
       ('104', '104'),
       ('104', '102'),
       ('105', '1010'),
       ('105', '108'),
       ('105', '106'),
       ('105', '104'),
       ('105', '102'),
       ('106', '1010'),
       ('106', '108'),
       ('106', '106'),
       ('106', '104'),
       ('106', '102'),
       ('107', '1010'),
       ('107', '108'),
       ('107', '106'),
       ('107', '104'),
       ('107', '102'),
       ('108', '1010'),
       ('108', '108'),
       ('108', '106'),
       ('108', '104'),
       ('108', '102'),
       ('109', '1010'),
       ('109', '108'),
       ('109', '106'),
       ('109', '104'),
       ('109', '102'),
       ('1010', '1010'),
       ('1010', '108'),
       ('1010', '106'),
       ('1010', '104'),
       ('1010', '102');

1.10 收藏信息表

1）表结构

user_id(用户id)

sku_id(商品id)

create_date(收藏日期)

101

3

2021-09-23

101

12

2021-09-23

101

6

2021-09-25

2）建表语句

hive> 
DROP TABLE IF EXISTS favor_info;
CREATE TABLE favor_info
(
    `user_id`     string comment '用户id',
    `sku_id`      string comment '商品id',
    `create_date` string comment '收藏日期'
) COMMENT '用户收藏表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 3）数据装载

hive> 
insert overwrite table favor_info
values ('101', '3', '2021-09-23'),
       ('101', '12', '2021-09-23'),
       ('101', '6', '2021-09-25'),
       ('101', '10', '2021-09-21'),
       ('101', '5', '2021-09-25'),
       ('102', '1', '2021-09-24'),
       ('102', '2', '2021-09-24'),
       ('102', '8', '2021-09-23'),
       ('102', '12', '2021-09-22'),
       ('102', '11', '2021-09-23'),
       ('102', '9', '2021-09-25'),
       ('102', '4', '2021-09-25'),
       ('102', '6', '2021-09-23'),
       ('102', '7', '2021-09-26'),
       ('103', '8', '2021-09-24'),
       ('103', '5', '2021-09-25'),
       ('103', '6', '2021-09-26'),
       ('103', '12', '2021-09-27'),
       ('103', '7', '2021-09-25'),
       ('103', '10', '2021-09-25'),
       ('103', '4', '2021-09-24'),
       ('103', '11', '2021-09-25'),
       ('103', '3', '2021-09-27'),
       ('104', '9', '2021-09-28'),
       ('104', '7', '2021-09-28'),
       ('104', '8', '2021-09-25'),
       ('104', '3', '2021-09-28'),
       ('104', '11', '2021-09-25'),
       ('104', '6', '2021-09-25'),
       ('104', '12', '2021-09-28'),
       ('105', '8', '2021-10-08'),
       ('105', '9', '2021-10-07'),
       ('105', '7', '2021-10-07'),
       ('105', '11', '2021-10-06'),
       ('105', '5', '2021-10-07'),
       ('105', '4', '2021-10-05'),
       ('105', '10', '2021-10-07'),
       ('106', '12', '2021-10-08'),
       ('106', '1', '2021-10-08'),
       ('106', '4', '2021-10-04'),
       ('106', '5', '2021-10-08'),
       ('106', '2', '2021-10-04'),
       ('106', '6', '2021-10-04'),
       ('106', '7', '2021-10-08'),
       ('107', '5', '2021-09-29'),
       ('107', '3', '2021-09-28'),
       ('107', '10', '2021-09-27'),
       ('108', '9', '2021-10-08'),
       ('108', '3', '2021-10-10'),
       ('108', '8', '2021-10-10'),
       ('108', '10', '2021-10-07'),
       ('108', '11', '2021-10-07'),
       ('109', '2', '2021-09-27'),
       ('109', '4', '2021-09-29'),
       ('109', '5', '2021-09-29'),
       ('109', '9', '2021-09-30'),
       ('109', '8', '2021-09-26'),
       ('1010', '2', '2021-09-29'),
       ('1010', '9', '2021-09-29'),
       ('1010', '1', '2021-10-01');

第2章 练习题

2.1 查询累积销量排名第二的商品

2.1.1 题目需求

查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。期望结果如下：

sku_id

2

2.1.2 代码实现


select sku_id
from (
         select sku_id
         from (
                  select sku_id, sum_num, rank() over (order by sum_num desc) rk
                  from (select sku_id, sum(sku_num) sum_num
                        from order_detail
                        group by sku_id) t1
                  order by sum_num desc) t2
         where t2.rk = 2) t3
         right join --为保证，没有第二名的情况下，返回null
    (
        select 1
    ) t4
                    on 1 = 1;

2.2 查询至少连续三天下单的用户

2.2.1 题目需求

查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：

user_id

101

 2.2.2 代码实现

select distinct user_id
from (
         select user_id
         from (
                  select user_id
                       , create_date
                       , date_sub(create_date, row_number() over (partition by user_id order by create_date)) flag
                  from (
                           select user_id
                                , create_date
                           from order_info
                           group by user_id, create_date
                       ) t1 -- 同一天可能多个用户下单，进行去重
              ) t2 -- 判断一串日期是否连续：若连续，用这个日期减去它的排名，会得到一个相同的结果
         group by user_id, flag
         having count(flag) >= 3 -- 连续下单大于等于三天
     ) t3;

 

2.3 查询各品类销售商品的种类数及销量最高的商品

2.3.1 题目需求

从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，期望结果如下：

category_id

(分类id)

category_name

(分类名称)

sku_id

(销量最好的商品id)

name

(商品名称)

order_num

（销量最好的商品销量）

order_cnt

(商品种类数量)

1

数码

2

手机壳

6044

4

2

厨卫

8

微波炉

253

4

3

户外

12

遮阳伞

20682

4

2.3.2 代码实现

答案一：


select t3.category_id, t4.category_name, t3.sku_id, t3.name, t3.order_num, t4.order_cnt
from (
         select t2.category_id,
                t2.sku_id,
                t2.name,
                t2.order_num
         from (select t1.category_id,
                      t1.sku_id,
                      t1.name,
                      t1.order_num,
                      row_number() over (partition by t1.category_id order by t1.order_num desc) rn
               from (
                        select sku.category_id, detail.sku_id, sku.name, sum(sku_num) order_num
                        from order_detail detail
                                 left join sku_info sku on detail.sku_id = sku.sku_id
                        group by sku.category_id, detail.sku_id, sku.name
                    ) t1
              ) t2
         where t2.rn = 1
     ) t3
         left join (
    select ci.category_id, ci.category_name, count(*) order_cnt
    from category_info ci
             left join sku_info si on ci.category_id = si.category_id
    group by ci.category_id, ci.category_name) t4 on t3.category_id = t4.category_id;

 答案二：

select category_id,
       category_name,
       sku_id,
       name,
       order_num,
       sku_cnt
from (
         select od.sku_id,
                sku.name,
                sku.category_id,
                cate.category_name,
                order_num,
                rank() over (partition by sku.category_id order by order_num desc) rk,
                count(distinct od.sku_id) over (partition by sku.category_id)      sku_cnt
         from (
                  select sku_id,
                         sum(sku_num) order_num
                  from order_detail
                  group by sku_id
              ) od
                  left join
              sku_info sku
              on od.sku_id = sku.sku_id
                  left join
              category_info cate
              on sku.category_id = cate.category_id
     ) t1
where rk = 1;

2.4 查询用户的累计消费金额及VIP等级

2.4.1 题目需求

从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。

用户vip等级根据累积消费金额计算，计算规则如下：

设累积消费总额为X，

若0=则vip等级为普通会员

若10000则vip等级为青铜会员

若30000则vip等级为白银会员

若50000则vip为黄金会员

若80000则vip等级为白金会员

若X>=100000,则vip等级为钻石会员

期望结果如下：

user_id

(用户id)

create_date

(下单日期)

sum_so_far

(截至每个下单日期的累计下单金额)

vip_level

(每个下单日期的VIP等级)

101

2021-09-27

29000

青铜会员

101

2021-09-28

99500

白金会员

101

2021-09-29

142800

钻石会员

101

2021-09-30

143660

钻石会员

1010

2020-10-08

51950

黄金会员

102

2021-10-01

171680

钻石会员

102

2021-10-02

177850

钻石会员

103

2021-10-02

69980

黄金会员

103

2021-10-03

75890

黄金会员

104

2021-10-03

89880

白金会员

105

2021-10-04

120100

钻石会员

106

2021-10-04

9390

普通会员

106

2021-10-05

119150

钻石会员

107

2021-10-05

69850

黄金会员

107

2021-10-06

124150

钻石会员

108

2021-10-06

101070

钻石会员

108

2021-10-07

155770

钻石会员

109

2020-10-08

24020

青铜会员

109

2021-10-07

153500

钻石会员

2.4.2 代码实现

select user_id,
       create_date,
       sum_so_far,
       case
           when sum_so_far >= 100000 then '钻石会员'
           when sum_so_far >= 80000 then '白金会员'
           when sum_so_far >= 50000 then '黄金会员'
           when sum_so_far >= 30000 then '白银会员'
           when sum_so_far >= 10000 then '青铜会员'
           when sum_so_far >= 0 then '普通会员'
           end vip_level
from (
         select user_id,
                create_date,
                sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far
         from (
                  select user_id,
                         create_date,
                         sum(total_amount) total_amount_per_day
                  from order_info
                  group by user_id, create_date
              ) t1
     ) t2;

2.5 查询首次下单后第二天连续下单的用户比率

2.5.1 题目需求

从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，期望结果如下：

percentage

60.0%

 

2.5.2 代码实现

答案一：

select concat(cast((select count(distinct user_id)
                    from (select user_id,
                                 create_date,
                                 datediff(create_date,
                                          first_value(create_date, false)
                                                      over (partition by user_id order by create_date)) date_diff
                          from order_info) t1
                    where date_diff = 1) / (select count(distinct user_id)
                                            from order_info) * 100 as varchar(4)), '%');

答案二：

select concat(round(sum(if(datediff(buy_date_second, buy_date_first) = 1, 1, 0)) / count(*) * 100, 1), '%') percentage
from (
         select user_id,
                min(create_date) buy_date_first,
                max(create_date) buy_date_second
         from (
                  select user_id,
                         create_date,
                         rank() over (partition by user_id order by create_date) rk
                  from (
                           select user_id,
                                  create_date
                           from order_info
                           group by user_id, create_date
                       ) t1
              ) t2
         where rk <= 2
         group by user_id
     ) t3;

 

2.14 向用户推荐朋友收藏的商品

2.14.1 题目需求

现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。期望结果如下：

1）部分结果展示

user_id（用户id）

sku_id（应向该用户推荐的商品id）

101

1

101

11

101

2

101

4

101

7

101

8

101

9

2）完整结果

user_id    sku_id
101,1
101,11
101,2
101,4
101,7
101,8
101,9
1010,10
1010,11
1010,12
1010,3
1010,4
1010,5
1010,6
1010,7
1010,8
102,10
102,3
102,5
103,1
103,2
103,9
104,1
104,10
104,2
104,4
104,5
105,1
105,12
105,2
105,3
105,6
106,10
106,11
106,3
106,8
106,9
107,1
107,11
107,12
107,2
107,4
107,6
107,7
107,8
107,9
108,1
108,12
108,2
108,4
108,5
108,6
108,7
109,1
109,10
109,11
109,12
109,3
109,6
109,7

 2.14.2 代码实现


select distinct  t1.user_id, friend_favor.sku_id
from (
         select user1_id user_id, user2_id friend_id
         from friendship_info
         union
         select user2_id user_id, user1_id friend_id
         from friendship_info
     ) t1
         left join favor_info friend_favor on t1.friend_id = friend_favor.user_id
         left join favor_info user_favor on t1.user_id = user_favor.user_id and friend_favor.sku_id = user_favor.sku_id
where user_favor.sku_id is null;

2.40 同时在线最多的人数

2.40.1 题目需求

根据用户登录明细表（user_login_detail），求出平台同时在线最多的人数。

结果如下：

Cn（人数）

7

2.40.2 代码实现

select max(cn) cn
from (select sum(flag) over (order by l_time ) cn
      from (
               select login_ts l_time, 1 flag
               from user_login_detail
               union
               select logout_ts l_time, -1 flag
               from user_login_detail
           ) t1
     ) t2;

 

分步解析：
-- 登录标记1 下线标记-1
select
  login_ts l_time,
  1 flag
from
  user_login_detail
union
select
  logout_ts l_time,
  -1 flag
from
  user_login_detail

-- 按照时间求和
select
  sum(flag)over(order by t1.l_time) sum_l_time
from
  (
    select
      login_ts l_time,
      1 flag
    from
      user_login_detail
    union
    select
      logout_ts l_time,
      -1 flag
    from
      user_login_detail
)t1  

-- 拿到最大值 就是同时在线最多人数
select
  max(sum_l_time)
from
  (
    select
      sum(flag)over(order by t1.l_time) sum_l_time
    from
      (
        select
          login_ts l_time,
          1 flag
        from
          user_login_detail
        union
        select
          logout_ts l_time,
          -1 flag
        from
          user_login_detail
    )t1 
)t2

施小赞
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive学习和习题集
11-02
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。 （2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。 （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。 （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。
Hive面试题
weixin_43833543的博客
11-28 1004
1、什么是Hive 是一个基于hadoop的数据仓库工具，可以将结构化数据映射成一张数据表，并提供类SQL的查询功能 2、Hive的意义（最初研发的原因） 背景：hadoop是个好东西，但是学习难度大，成本高，坡度陡。 意义（目的）：降低程序员使用hadoop的难度。降低学习成本。 3、Hive的内部组成模块，作用分别是什么 用户接口：包括CLI、JDBC/ODBC、WebGUI。其...
大数据技术之Hive SQL题库-初级
我从不打没有准备的仗！
03-20 3375
大数据技术之Hive SQL题库-初级
Hive QL 50道挑战题第四部分详解
Dream_aju的博客
09-28 322
一.数据准备 ---------------------表名和字段----------------- --学生表 --Student(s_id,s_name,s_birth,s_sex) 学生编号,学生姓名, 出生年月,学生性别 --课程表 --Course(c_id,c_name,t_id) 课程编号, 课程名称, 教师编号 --教师表 --Teacher(t_id,t_name) 教师编号,教师姓名 --成绩表 --Score(s_id,c_id,s_score) 学生编号,课程编号,分数 ​ ​
hive 创建表_hive基础建表（1）
weixin_39974409的博客
11-19 1650
1、如何在虚拟机centou os上进入hive的编辑页面，并对其进行设置？#2、hive建表1）在创建表之前首先另起窗口建立student.txtvi students.txt进入后将数据录入，insert录入建，esc退出建，：wq保存并退出。2）创建表#1.创建数据库 hive>create database myhive1; #2.使用数据库 hive>use myhive1;...
Hive SQL练习题库
最新发布
04-24
### Hive SQL练习题库知识点详解 #### 一、Hive SQL概述 Hive 是一个基于 Hadoop 的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，使不熟悉 MapReduce 的用户可以利用 SQL...
Hive SQL性能优化
02-26
### Hive SQL性能优化详解 #### 一、Hive SQL执行顺序及原理 了解Hive SQL的执行顺序，有助于我们写出更高效、更高质量的代码。Hive SQL的执行大致可以分为以下几个步骤： 1. **确定数据源**：首先确定查询的数据...
hiveSQL实战案例
05-26
在Hive SQL实战中，我们经常会遇到各种查询需求，这里我们将通过三个具体案例来探讨Hive SQL中的关键知识点，包括窗口函数的应用以及处理用户数据的技巧。 首先，我们来看窗⼝函数row_number(), rank()和dense_rank...
大数据之Hive SQL题库-初级
HelloWowofei的博客
07-22 215
007 陈坤 1999-04-09 男 48。013 蒋欣 1997-11-08 女 47。（4）首先要分组求出每个学生的平均成绩，筛选高于60分的，并反查出这批学生，统计出这些学生总的平均成绩。019 邓紫棋 1994-08-31 女 46。
hive练习数据和练习题及答案
01-05
hive练习数据和hive练习题包含了hive练习数据，hive数据的建表ddl和hive练习题，非常适合做hive练习，新手培训，快速入门等； 包含以下练习： hive的group by 和集合函数 hive的Order By/Sort By/Distribute By Join查询,join只支持等值连接 LEFT，RIGHT 和 FULL OUTER JOIN LEFT SEMI JOIN Hive当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写子查询语句。
保姆式教学Hive考试
qq_56618631的博客
07-13 468
考试冲冲冲
hive 50道经典练习
qq_35831052的博客
07-18 4673
数据 Student(Sid,Sname,Sage,Ssex)学生表 Sid：学号 Sname：学生姓名 Sbirth：学生生日 Ssex：学生性别 Course(Cid,Cname,T#)课程表 Cid：课程编号 Cname：课程名称 Tid：教师编号 SC(Sid,Cid,score)成绩表 Sid：学号 Cid：课程编号 score：成绩 Teacher(Tid,Tname)教师表 T...
Hive1
ZY12166的博客
06-29 301
Hive1 1、实现struct例子 (1)创建学生struct表 create table student (id int, info struct<name:string, age:int>) row format delimited fields terminated by ',' collection items te...
Hive案例 学生成绩表综合案例
Allen的博客
08-26 3002
首先给出各个表的数据 •表1 学生表 <学号,姓名,性别,年龄,系> - <Sno,Sname,Ssex,Sage,Sdepartment> 95001,李勇,男,20,CS 95002,刘晨,女,19,IS 95003,王敏,女,22,MA 95004,张立,男,19,IS 95005,刘刚,男,18,MA 95006,孙庆,男,23,CS •表2 课程表<课程ID...
Hive练习题目
夜雨寒霜
12-27 2272
----hive 基本操作  1-------------数据自己造  a表：(id int,name string) b表：(id int,job_id int,num int) c表：(job_id int,job string) --建表语句   --加载数据     ---------a表和b表进行链接操作，并观察结果-------------- --内连接      -- 左连接 lef...
hive试题
qq_34342386的博客
04-01 3778
1、代码select ceil(2.34)的结果是哪一个： A 2.3 B 2 C 3 D 2.4 2、float表示的数据类型是： A 整数型 B 字符性 C 布尔型 D 浮点型 3、以下表达式书写错误的是 A year('2015-12-31 12:21') B month(2015-10-31) C day('2015-12-11') D date_sub
Hive -- Hive面试题及答案（3）
TheBigBlue的博客
05-01 1323
向导小打卡面试题题目一：计算小打卡平台的圈主建了多少圈子，名下有多少用户题目二：处理产品版本号题目三、计算平台用户留存 小打卡面试题 题目一：计算小打卡平台的圈主建了多少圈子，名下有多少用户 已知，数据如下： tb_habit圈子表：数百万行数据 user_habit_relation用户与圈子关系表：亿行数据 需求：请用hive sql计算出如下结果（同一个圈主名下，同一个用户加多个圈子只计...
hive SQL基础经典练习题(上）
top8488.top大数据
12-03 2933
hive SQL基础经典练习题(上） hive SQL 入门容易，精通很难。基础的巩固相当重要，看起来简单易入门的SQL其实是千变万化的，能一次写正确SQL的工程师，亦然遵守二八法则。 编程环境：hive 创建练习题数据（一） 表1 COURSE (课程表) create table COURSE (`CNO` string comment '课程编号', `CN...
Hive SQL初学者练习题库：环境准备和数据准备
Hive SQL练习题库 Hive SQL练习题库是面向 Hive 初学者的练习资源，旨在帮助初学者熟悉 Hive 的基本概念和操作。下面是对该题库的详细解读和知识点总结： 环境准备 在开始学习 Hive 之前，需要准备好环境。Hive ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值