大数据技术之Hive SQL题库-中级

第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

(商品价格)

xiaomi 10

2020-01-01

2000

洗碗机

2020-02-01

2000

自行车

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(分类名称)

数码

厨卫

户外

2）建表语句

hive>

DROP TABLE IF EXISTS category_info;
create table category_info(
    `category_id`   string comment'分类id',
    `category_name` string comment'分类名称',
) 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

(订单金额)

101

2021-09-30

29000.00

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

(商品件数)

2021-09-30

2000.00

2021-09-30

5000.00

2020-10-02

6000.00

2020-10-02

500.00

2020-10-02

2000.00

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(变更日期)

1900.00

2021-09-25

2000.00

2021-09-26

80.00

2021-09-29

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

（期望配送日期）

101

2021-09-27

2021-09-29

101

2021-09-28

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

2021-09-23

101

2021-09-23

101

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.1.2 代码实现

hive>

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

骚戴理解：这里我一开始是想用排序加limit才找，但是我不知道怎么实现“如果不存在返回null，如果存在多个排名第二的商品则需要全部返回”这个效果，我想到了连接，但是还是不会哈哈，这里是通过right join select 1来实现“如果不存在返回null”！

需要注意的几个点：

group by sku_id这句我一开始都没想到，因为同一个商品在同一天可能会被下单很多次，所以这里需要分组
dense_rank() over (order by order_num desc) rk这里我尝试写成下面这样，但是报错！说明这样写不行，我还想着把这两个select合成一个，结果也不行！所以需要在写一个select去用where筛选

select 1的返回结果就是1，就一条记录，这里通过和select 1进行right join来实现“如果不存在返回null，如果存在多个排名第二的商品则需要全部返回”这个效果，假如左边查询的sku_id是null，那么null和select 1去join的结果就是null，因为最终查询的是sku_id
下面的t1-t4我一开始是没有加的，因为我觉得都没有用到，不需要加也可以，结果报错！

查漏补缺：dense_rank() over是用来排名的，有三种排名的方式，分别是rank() over()、row_number() over()、dense_rank() over() 。

例如下面用的rank() over()函数，在列值重复的情况下，也就是score有两个65，这里两个65并列排在第3名，然后57排在第5名了，没有第4名

例如下面使用dense_rank() over函数，score有两个65，这里两个65并列排在第3名，然后57排在第4名了，跟rank() over()对比发现rank() over()只要重复了排名就会在下一个排名那里跳过去，而rank() over()不会跳，不管重复多少都会按顺序来排名，也就是不会有缺少某个排名的情况

例如下面使用row_number() over()函数，score有两个80，这里两个80分别排名为2和3名，跟上面两个对比发现，上面两个函数是有重复就并列多少名，这个函数就重复也会继续排序，很明显，这不公平哈哈

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

2.2.1 题目需求

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

user_id

10158分45秒

2.2.2 代码实现

hive> 
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;

骚戴解法
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;

骚戴理解：最里面的select的group by是为了去重，因为同一个用户同一天可能下单多次，date_sub(create_date,row_number() over (partition by user_id order by create_date)) flag这个语句最难理解，这里的date_sub函数是日期相减的意思，row_number() over (partition by user_id order by create_date)是一个开窗函数，作用就是按照user_id分组，按照create_date进行排序并且排名，拆开理解是很容易理解的，但是它是怎么实现“最少连续3天”这个效果的呢？

  select user_id, 
         create_date,
          row_number() over (partition by user_id order by create_date) rn,
         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;

这里为了更好的理解，我把它拆开并且查询出来，看看到底是什么东西？下面就是上面的hql运行的结果！下面的rn列就是一个排名

这里理解date_sub(create_date,row_number() over (partition by user_id order by create_date)) flag的核心在于要明白如果create_date是连续的，那么create_date减掉rn的值就是一样的！例如上面的101可以看出flag都是一样的。所以只要flag是相同的那么就说明create_date是连续的日期，然后再写一个select去子查询，最后通过下面两句来筛选出最少连续3天下单的用户。注意这里的分组是有flag的！

GROUP by user_id,flag

HAVING COUNT(flag) >=3

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

2.3.1 题目需求

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

category_id

(分类id)

category_name

(分类名称)

sku_id

(销量最好的商品id)

name

(商品名称)

order_num

（销量最好的商品销量）

sku_cnt

(商品种类数量)

数码

手机壳

6044

厨卫

微波炉

253

户外

遮阳伞

20682

2.3.2 代码实现

hive>

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(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;

骚戴理解：

这个题目用到了sku_info表、category_info表、order_detail表
特别注意在多表联查的时候select后面的字段如果是在多个表里出现过的那就必须要通过表名.字段名的方式，例如od.sku_id，不然它不知道你要用哪个表的sku_id
这里sum(sku_num) order_num我一开始写错了，用的count函数而不是sum函数
rank() over (partition by sku.category_id order by order_num desc) rk这里用的rank()over()，所以会跳，具体的看上面的查漏补缺介绍
count( od.sku_id) over (partition by sku.category_id) sku_cnt这语句相当于下面的

select 
    count(sku_id)
from sku_info
group by category_id;

其实就是实现“统计各品类销售出的商品种类数”这个效果，这种写法很妙，特别是在group by的限制情况下使用，非常nice！

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

2.4.1 题目需求

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

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

设累积消费总额为X，

若0=<X<10000,则vip等级为普通会员

若10000<=X<30000,则vip等级为青铜会员

若30000<=X<50000,则vip等级为白银会员

若50000<=X<80000,则vip为黄金会员

若80000<=X<100000,则vip等级为白金会员

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

期望结果如下：

user_id

(用户id)

create_date

(下单日期)

sum_so_far

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

vip_level

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

101

2021-09-27

29000.00

青铜会员

101

2021-09-28

99500.00

白金会员

101

2021-09-29

142800.00

钻石会员

101

2021-09-30

143660.00

钻石会员

102

2021-10-01

171680.00

钻石会员

102

2021-10-02

177850.00

钻石会员

103

2021-10-02

69980.00

黄金会员

103

2021-10-03

75890.00

黄金会员

104

2021-10-03

89880.00

白金会员

105

2021-10-04

120100.00

钻石会员

106

2021-10-04

9390.00

普通会员

106

2021-10-05

119150.00

钻石会员

107

2021-10-05

69850.00

黄金会员

107

2021-10-06

124150.00

钻石会员

108

2021-10-06

101070.00

钻石会员

108

2021-10-07

155770.00

钻石会员

109

2021-10-07

129480.00

钻石会员

109

2021-10-08

153500.00

钻石会员

1010

2021-10-08

51950.00

黄金会员

2.4.2 代码实现

hive>

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;

骚戴理解：

这个题目我知道用case-when-then-end，但是我一开始是想这么写，结果报错！这个题目的解法是从最大的判断when sum_so_far >= 100000开始写，这里要结合题意，很巧妙，然后还要注意这里when-then一条后面是没有逗号的！

这里我没有用到第二层select子查询，所以我查询出来的是每个用户每一天的消费金额，就是单独的一天的消费额度，我没有看懂题目意思。重大理解下面的这个语句：

sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far这个语句就是用来实现“统计每个用户截止其每个下单日期的累积消费金额”这个效果，这句的意思就是以user_id分组，并且通过create_date排序，然后求和每一天的消费金额，也就是把当前的消费金额加上之前的所有天的消费金额的总金额就是总消费金额！

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

2.5.1 题目需求

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

percentage

60.0%

2.5.2 代码实现

hive>

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;

骚戴理解：

最里面的select是通过user_id, create_date分组来确定唯一的一条用户下单的数据
“首次下单后第二天仍然下单”是通过 rank() over (partition by user_id order by create_date) rk和where rk <= 2来实现的，也就是先按create_date升序排名，然后再筛选出前面两天的日期，也就是第一次下单和第二次下单的时间，用户后面判断是否是连续的两天
这里巧妙的用最大值max(create_date) buy_date_second和最小值min(create_date) buy_date_first来锁定死首次下单的日期和第二次下单的日期，这一步骤是因为后面的datediff函数要用到！
一开始我不能理解为什么第二个select要用group by user_id分组，因为我一开始把group by user_id当做第三个select的条件了，所以一直没有能够理解。这里如果不分组就会报错！这个group by user_id是为了把筛选出来的两条下单记录给并列在一起！
concat(round(sum(if(datediff(buy_date_second, buy_date_first) = 1, 1, 0)) / count() * 100, 1), '%')是这个Hql里的灵魂所在，datediff函数就是求两个日期相差多少天，下面的语句表示相差一天，datediff(buy_date_second, buy_date_first) = 1，也就是首次下单和第二次下单是连续的，“首次下单后第二天仍然下单”的实现。if是判断如果首次下单和第二次下单是连续的，那就返回1，否则返回0，然后通过sum对所有if的返回值求和，这样得到的就是首次下单和第二次下单是连续的用户数量，sum求和后除以总数count() 再乘100得到的就是所占比，这里的count（*）没有去重是因为在第二个select里就通过group by user_id达到了去重的效果。round函数是四舍五入，round函数的第二个参数为1是保留一位小数，最后用concat函数拼接一个%得到一个百分数！

2.6 每个商品销售首年的年份、销售数量和销售金额

2.6.1 题目需求

从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

期望结果如下：

sku_id

（商品id）

year

（销售首年年份）

order_num

（首年销量）

order_amount

（首年销售金额）

2.6.2 代码实现

hive>

select sku_id,
       year(create_date) year,
       sum(sku_num) order_num,
       sum(price*sku_num) order_amount
from (
         select order_id,
                sku_id,
                price,
                sku_num,
                create_date,
                rank() over (partition by sku_id order by year(create_date)) rk
         from order_detail
     ) t1
where rk = 1
group by sku_id,year(create_date);

骚戴理解：这个题目不是很难，rank() over (partition by sku_id order by year(create_date)) rk这个排名不是第一次见了，但是order by year(create_date)这里的用到了year函数是第一见，我没想到这两个可以用在一起，所以这里就是通过这语句和where rk = 1来实现“首年”这个效果，然后要注意这里的分组是group by sku_id,year(create_date)，注意这里不能用别名，也就是group by sku_id,year；这会报错！这里的sum(pricesku_num)我一开始是想直接写成order_numprice，但是会报错，因为分组里没有price这个字段！

2.7 筛选去年总销量小于100的商品

2.7.1 题目需求

从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月（30天）的商品，期望结果如下：

sku_id

（商品id）

name

（商品名称）

order_num

（销量）

xiaomi 10

apple 12

xiaomi 13

洗碗机

2.7.2 代码实现

hive>

select t1.sku_id,
       name,
       order_num
from (
         select sku_id,
                sum(sku_num) order_num
         from order_detail
         where year(create_date) = '2021'
           and sku_id in (
             select sku_id
             from sku_info
             where datediff('2022-01-10', from_date) > 30
         )
         group by sku_id
         having sum(sku_num) < 100
     ) t1
         left join
     sku_info t2
     on t1.sku_id = t2.sku_id;

骚戴理解：

这里要用到sku_info表和order_detail表
“假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品”是通过下面语句实现的

where datediff('2022-01-10', from_date) > 30

“筛选出去年总销量小于100的商品及其销量”这里我一开始想错了，我以为是要实现2022-01-10的前一年，也就是2021-01-10到2022-01-10的总销量小于100的商品及其销量，其实只要通过year(create_date) = '2021'来实现就好了

2.8 查询每日新用户数

2.8.1 题目需求

从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。期望结果如下：

login_date_first（日期）

user_count（新增用户数）

2021-09-21

2021-09-22

2021-09-23

2021-09-24

2021-09-25

2021-09-26

2021-09-27

2021-10-04

2021-10-06

2.8.2 代码实现

hive>

select
    login_date_first,
    count(*) user_count
from
(
    select
        user_id,
        min(date_format(login_ts,'yyyy-MM-dd')) login_date_first
    from user_login_detail
    group by user_id
)t1
group by login_date_first;

骚戴理解：这个题目的思路很简单，先求出每个用户的首次登录的时间，然后在以这个时间分组统计用户的数量就是那一天的新增用户数量，实现“一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户”，这里要注意对日期进行格式化！因为数据里的日期是2021-09-21 08:00:00这样的格式，为什么要格式化呢？下面就是没有格式化跑出来的结果，可以看到红色画出来的部分应该是合在一起的，但是由于时间不一样，即使日期一样那么在分组的时候也会被分为两组！！！

2.9 统计每个商品的销量最高的日期

2.9.1 题目需求

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。期望结果如下：

sku_id（商品id）

create_date（销量最高的日期）

sum_num（销量）

2.9.2 代码实现

hive>

select sku_id,
       create_date,
       sum_num
from (
         select sku_id,
                create_date,
                sum_num,
                row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn
         from (
                  select sku_id,
                         create_date,
                         sum(sku_num) sum_num
                  from order_detail
                  group by sku_id, create_date
              ) t1
     ) t2
where rn = 1;

骚戴理解：

这里“如果有同一商品多日销量并列的情况，取其中的最小日期“实现是通过下面

row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn

巧妙的实现的，更准确的说是通过order by sum_num desc,create_date asc实现的！

这里我一开始是用的rank() over()函数，结果跟row_number() over()函数是一样的，这两个的区别就在于rank() over()函数会跳，row_number() over()函数不会跳，不懂就看下面的文章！

文章参考：https://www.cnblogs.com/scwbky/p/9558203.html

需要用到的表：order_detail表

2.10 查询销售件数高于品类平均数的商品

2.10.1 题目需求

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品，期望结果如下：

2.10.2 代码实现

hive>

select sku_id,
       name,
       sum_num,
       cate_avg_num
from (
         select od.sku_id,
                category_id,
                name,
                sum_num,
                avg(sum_num) over (partition by category_id) cate_avg_num
         from (
                  select sku_id,
                         sum(sku_num) sum_num
                  from order_detail
                  group by sku_id
              ) od
              left join sku_info sku 
              on od.sku_id = sku.sku_id) t1
where sum_num > cate_avg_num;

骚戴理解：

这里我之前一直想给查询的数据排序，比如按sku_id排序，但是一直都没用，到现在才找到原因，那就是因为sku_id是string类型，所以对于字符串来说1是最小的，有时候才会出现1,10,2,3这样的排序结果
解题思路：在order_detail表中求每个商品的下单总数--->把上一步的结果表和sku_info表做left join连接，求出分类的平均值--->筛选出累积销售件数高于其所属品类平均数的商品
解题核心：avg(sum_num) over (partition by category_id) cate_avg_num语句和来where sum_num > cate_avg_num语句来实现“查询累积销售件数高于其所属品类平均数的商品"，这里我一开始是想通过传统的group by来实现分组，后面发现这样写就只能查询分类id，不能查询sku_id，所以以后遇到这样的情况要学会考虑用窗口函数！！！
需要用到的表：order_detail表和sku_info表

2.11 用户注册、登录、下单综合统计

2.11.1 题目需求

从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。期望结果如下：

user_id

(用户id)

register_date

(注册日期)

total_login_count

(累积登录次数)

login_count_2021

(2021年登录次数)

order_count_2021

(2021年下单次数)

order_amount_2021

(2021年订单金额)

2.11.2 代码实现

hive>

select login.user_id,
       register_date,
       total_login_count,
       login_count_2021,
       order_count_2021,
       order_amount_2021
from (
         select user_id,
                min(date_format(login_ts, 'yyyy-MM-dd')) register_date,
                count(1) total_login_count,
                count(if(year(login_ts) = '2021', 1, null)) login_count_2021
         from user_login_detail
         group by user_id
     ) login
         join
     (
         select user_id,
                count(order_id) order_count_2021,
                sum(total_amount) order_amount_2021
         from order_info
         where year(create_date) = '2021'
         group by user_id
     ) oi
     on login.user_id = oi.user_id;

骚戴理解：

count(1) 和count（*）是一个效果，不过count（1）更好一点
count(if(year(login_ts) = '2021', 1, null)) 语句很妙，我想的是传统的where方法来实现
min(date_format(login_ts, 'yyyy-MM-dd'))这里记得格式化
这里两个结果表是通过join来连接，我一开始想的是left join
所需要的表：order_info表和user_login_detail表

2.12 查询指定日期的全部商品价格

2.12.1 题目需求

从商品价格修改明细表（sku_price_modify_detail）中查询2021-10-01的全部商品的价格，假设所有商品初始价格默认都是99。期望结果如下：

sku_id（商品id）

price（商品价格）

2000.00

10.00

5000.00

6000.00

500.00

2000.00

100.00

600.00

1000.00

90.00

66.00

20.00

2.12.2 代码实现

hive>

select sku_info.sku_id,
       nvl(new_price, 99) price
from sku_info
         left join
     (
         select sku_id,
                new_price
         from (
                  select sku_id,
                         new_price,
                         change_date,
                         row_number() over (partition by sku_id order by change_date desc) rn
                  from sku_price_modify_detail
                  where change_date <= '2021-10-01'
              ) t1
         where rn = 1
     ) t2
     on sku_info.sku_id = t2.sku_id;

骚戴理解：

这里首先要看懂题目的意思，sku_price_modify_detail表记录的就是商品价格变化的记录，那一个商品可能会价格变了很多次，所以要求出小于等于2021-10-01的所有日期里面的最大值，因为这个值是最新的价格值，当然还有可能价格没有改过，一直都是99，所以这个表里没有记录
“求出小于等于2021-10-01的所有日期里面的最大值”我一开始是想着分组后求最大值，这样的话select就只能查询sku_id，能用窗口函数解决优先使用窗口函数！
“如果价格没有变，那么返回初始值99”是通过nvl(new_price, 99) 和left join来实现的，这个函数的意思是如果new_price为null，那么返回99，如果不为null，返回new_price本身的值
所需要的表：sku_price_modify_detail表和sku_info表

2.13 即时订单比例

2.13.1 题目需求

订单配送中，如果期望配送日期和下单日期相同，称为即时订单，如果期望配送日期和下单日期不同，称为计划订单。

请从配送信息表（delivery_info）中求出每个用户的首单（用户的第一个订单）中即时订单的比例，保留两位小数，以小数形式显示。期望结果如下：

percentage

0.6

2.13.2 代码实现

hive>

select
    round(sum(if(order_date=custom_date,1,0))/count(*),2) percentage
from
(
    select
        delivery_id,
        user_id,
        order_date,
        custom_date,
        row_number() over (partition by user_id order by order_date) rn
    from delivery_info
)t1
where rn=1;

骚戴理解：还是那句话，摆脱mysql的惯有思维！也就是使用传统的group by来实现的都优先考虑窗口函数，round(sum(if(order_date=custom_date,1,0))/count(),2)我一开始想的就是用where来实现，但是round(sum(if(order_date=custom_date,1,0))/count(),2)这样写要容易很多，简洁很多！

需要用到的表：delivery_info表

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

2.14.1 题目需求

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

1）部分结果展示

user_id（用户id）

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

101

2）完整结果

user_id sku_id

101 2

101 4

101 7

101 9

101 8

101 11

101 1

102 3

102 5

102 10

103 2

103 1

103 9

104 1

104 4

104 10

104 5

104 2

105 1

105 2

105 6

105 12

105 3

106 11

106 10

106 8

106 9

106 3

107 11

107 7

107 4

107 9

107 12

107 1

107 8

107 6

107 2

108 2

108 6

108 12

108 1

108 7

108 4

108 5

109 6

109 10

109 7

109 1

109 12

109 3

109 11

1010 4

1010 10

1010 6

1010 12

1010 11

1010 8

1010 3

1010 5

1010 7

2.14.2 代码实现

hive>

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,
        user1_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.15 查询所有用户的连续登录两天及以上的日期区间

2.15.1 题目需求

从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。期望结果如下：

user_id(用户id)

start_date(开始日期)

end_date(结束日期)

101

2021-09-27

2021-09-30

102

2021-10-01

2021-10-02

106

2021-10-04

2021-10-05

107

2021-10-05

2021-10-06

2.15.2 代码实现

hive>

select user_id,
       min(login_date) start_date,
       max(login_date) end_date
from (
         select user_id,
                login_date,
                date_sub(login_date, rn) flag
         from (
                  select user_id,
                         login_date,
                         row_number() over (partition by user_id order by login_date) rn
                  from (
                           select user_id,
                                  date_format(login_ts, 'yyyy-MM-dd') login_date
                           from user_login_detail
                           group by user_id, date_format(login_ts, 'yyyy-MM-dd')
                       ) t1
              ) t2
     ) t3
group by user_id, flag
having count(*) >= 2;

骚戴解法
SELECT 
    user_id ,
    min(date_format(login_ts,'yyyy-MM-dd')) start_date,
    max(date_format(login_ts,'yyyy-MM-dd')) end_date
FROM (
SELECT 
    user_id ,
    login_ts ,
    row_number() over(PARTITION by user_id order by login_ts) rn,
    date_sub(login_ts,row_number() over(PARTITION by user_id order by login_ts)) flag
FROM user_login_detail
)t 
GROUP BY user_id ,flag
HAVING count(flag) >1

骚戴理解：这个题目在消化了第二题后，就可以做出来了，突然就不难了哈哈，以下是需要用到的表

2.16 男性和女性每日的购物总金额统计

2.16.1 题目需求

从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。期望结果如下：

create_date

（日期）

total_amount_male

（男性用户总金额）

total_amount_female

（女性用户总金额）

2021-09-27

29000.00

0.00

2021-09-28

70500.00

0.00

2021-09-29

43300.00

0.00

2021-09-30

860.00

0.00

2021-10-01

0.00

171680.00

2021-10-02

0.00

76150.00

2021-10-03

89880.00

5910.00

2021-10-04

9390.00

120100.00

2021-10-05

109760.00

69850.00

2021-10-06

101070.00

54300.00

2021-10-07

54700.00

129480.00

2021-10-08

51950.00

24020.00

2.16.2 代码实现

hive>

select create_date,
       sum(if(gender = '男', total_amount, 0)) total_amount_male,
       sum(if(gender = '女', total_amount, 0)) total_amount_female
from order_info oi
         left join
     user_info ui
     on oi.user_id = ui.user_id
group by create_date;

骚戴理解：这题目不难，我没有摆脱惯性思维，没想到用sum(if(gender = '男', total_amount, 0))，理一下思路，首先左连接--->根据下单日期分组--->通过if函数判断是男是女--->sum来统计总数

所需要的两个表：order_info表和user_info表

*2.17 订单金额趋势分析

2.17.1 题目需求

查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。期望结果如下：

create_date

（日期）

total_3d

（最近3日订单金额总和）

avg_ad

（最近3日订单金额日平均值）

2021-09-27

29000.00

2021-09-28

99500.00

49750.00

2021-09-29

142800.00

47600.00

2021-09-30

114660.00

38220.00

2021-10-01

215840.00

71946.67

2021-10-02

248690.00

82896.67

2021-10-03

343620.00

114540.00

2021-10-04

301430.00

100476.67

2021-10-05

404890.00

134963.33

2021-10-06

464470.00

154823.33

2021-10-07

519160.00

173053.33

2021-10-08

415520.00

138506.67

2.17.2 代码实现

hive>

select create_date,
       round(sum(total_amount_by_day) over (order by create_date rows between 2 preceding and current row ),2) total_3d,
       round(avg(total_amount_by_day) over (order by create_date rows between 2 preceding and current row ), 2) avg_3d
from (
         select create_date,
                sum(total_amount) total_amount_by_day
         from order_info
         group by create_date
     ) t1;

骚戴理解：这里我理解的近三天是连续的三天，上面的hql是有问题的，它只能统计所有数据都是连续的情况，例如第一条是2020-10-08，他就不是连续的，那么统计的2021-09-27的总数就应该是29000，但它把2020-10-08的数据也加进来了，这是有问题的，平均值也有问题，所以2021-09-27和2021-09-28的总数和平均值都是错的，但是这样的通过行来求近三天内的思路可以学习一下，order by create_date rows between 2 preceding and current row表示按create_date排序，并且获取排序后的前两行到当前行的数据，通过这个来实现“最近3天内”

2.18 购买过商品1和商品2但是没有购买商品3的顾客

2.18.1 题目需求

从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户，期望结果如下：

user_id

103

105

2.18.2 代码实现

hive>

select user_id
from (
         select user_id,
                collect_set(sku_id) skus
         from order_detail od
                  left join
              order_info oi
              on od.order_id = oi.order_id
         group by user_id
     ) t1
where array_contains(skus, '1')
  and array_contains(skus, '2')
  and !array_contains(skus, '3');

骚戴理解：理清思路，把两个表left join并且按用户id分组--->每个用户下单的商品id收集成一个集合--->通过array_contains来判断是否包含对应的商品id

这里两个亮点：collect_set函数的作用是收集并形成set集合，结果去重，这里用sku_id会有重复的，所以要去重，array_contains来判断集合里有没有这个元素，有就返回true

需要用到的表：order_detail表和order_info表

2.19 统计每日商品1和商品2销量的差值

2.19.1 题目需求

从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量），期望结果如下：

create_date

diff

2021-09-27

2021-10-01

-10

2021-10-02

-49

2021-10-03

2021-10-04

-55

2021-10-05

-30

2021-10-06

-49

2021-10-07

-40

2021-10-08

-24

2.19.2 代码实现

hive>

select create_date,
       sum(if(sku_id = '1', sku_num, 0)) - sum(if(sku_id = '2', sku_num, 0)) diff
from order_detail
where sku_id in ('1', '2')
group by create_date;

骚戴理解：这个题目我一开始是这样写的，但是会报错!

所需要的表：order_detail表

2.20 查询出每个用户的最近三笔订单

2.20.1 题目需求

从订单信息表（order_info）中查询出每个用户的最近三笔订单，期望结果如下：

user_id

order_id

create_date

101

2021-09-28

101

2021-09-29

101

2021-09-30

102

2021-10-01

102

2021-10-01

102

2021-10-02

103

2021-10-02

103

2021-10-02

103

2021-10-03

104

2021-10-03

104

2021-10-03

104

2021-10-03

105

2021-10-04

105

2021-10-04

105

2021-10-04

106

2021-10-05

106

2021-10-05

106

2021-10-05

107

2021-10-05

107

2021-10-06

107

2021-10-06

108

2021-10-06

108

2021-10-07

108

2021-10-07

109

2021-10-07

109

2021-10-08

109

2021-10-08

1010

2021-10-08

1010

2021-10-08

2.20.2 代码实现

hive>

select user_id,
       order_id,
       create_date
from (
         select user_id
              , order_id
              , create_date
              , row_number() over (partition by user_id order by create_date desc) rk
         from order_info
     ) t1
where rk <= 3;

骚戴理解：“最近三笔订单”是通过排名后求最大的日期的前三天，也就是通过row_number() over (partition by user_id order by create_date desc) rk 和where rk <= 3;来实现

需要用到的表：order_info表

2.21 查询每个用户登录日期的最大空档期

2.21.1 题目需求

从登录明细表（user_login_detail）中查询每个用户两个登录日期（以login_ts为准）之间的最大的空档期。统计最大空档期时，用户最后一次登录至今的空档也要考虑在内，假设今天为2021-10-10。期望结果如下：

user_id（用户id）

max_diff（最大空档期）

101

102

103

104

105

106

107

108

109

1010

2.21.2 代码实现

hive>

select
    user_id,
    max(diff) max_diff
from
(
    select
        user_id,
        datediff(next_login_date,login_date) diff
    from
    (
        select
            user_id,
            login_date,
            lead(login_date,1,'2021-10-10') over(partition by user_id order by login_date) next_login_date
        from
        (
            select
                user_id,
                date_format(login_ts,'yyyy-MM-dd') login_date
            from user_login_detail
            group by user_id,date_format(login_ts,'yyyy-MM-dd')
        )t1
    )t2
)t3
group by user_id;

骚戴理解：这个题目有点难度，先理清思路，首先对日期进行格式化并且以用户id和格式化后的日期进行分组--->通过lead（） over（）函数来查询下一个登录日期--->通过datediff函数求差值--->通过max取最大值

group by user_id,date_format(login_ts,'yyyy-MM-dd')这里分组是因为要去重，因为同一天内同一个用户可能登录多次
lead(login_date,1,'2021-10-10') over(partition by user_id order by login_date) 是核心，用来实现求下一次的日期，这个语句要从后往前看，先看over(partition by user_id order by login_date) 这个无非就是分组加排序，并不陌生，值得一提的是这个partition by有时候可以不写！然后再看lead(login_date,1,'2021-10-10')的意思是求login_date的下一次的日期，如果没有下一次就用2021-10-10来作为下一次日期，例如下面红色画出来的部分

这个题目我一开始是这样写的

我一开始想用date_sub函数来求两个日期只差，但是报错

可以看出这个date_sub函数不能用于string类型的日期，当然它这个日期设置为string也不合理，至于这各报错为什么显示的是date_add函数我也不知道，然后我使用datediff函数就可以了

我一开始没有写最后的这个group by user_id，然后报错如下

这里报语法错误是因为select后面用到了max函数，所以必须分组！而且从语义来讲也要分组，因为会有重复的，如下所示，可以看到101有三条重复的1，所以需要分组！

需要用到的表:user_login_detail表

查漏补缺：https://blog.csdn.net/xc_zhou/article/details/128208682

*2.22 查询相同时刻多地登陆的用户

2.22.1 题目需求

从登录明细表（user_login_detail）中查询在相同时刻，多地登陆（ip_address不同）的用户，期望结果如下：

user_id(用户id)

101

102

104

107

2.22.2 代码实现

hive>

select
  distinct t2.user_id
from
  (
   select
     t1.user_id,
     if(t1.max_logout is null ,2,if(t1.max_logout<t1.login_ts,1,0)) flag
   from
     (
      select
        user_id,
        login_ts,
        logout_ts,
        max(logout_ts)over(partition by user_id order by login_ts rows between unbounded preceding and 1 preceding) max_logout
      from
        user_login_detail
   )t1
)t2
where
  t2.flag=0

2.23 销售额完成任务指标的商品

2.23.1 题目需求

商家要求每个商品每个月需要售卖出一定的销售总额

假设1号商品销售总额大于21000，2号商品销售总额大于10000，其余商品没有要求

请写出SQL从订单详情表中（order_detail）查询连续两个月销售总额大于等于任务总额的商品

结果如下：

sku_id（商品id）

2.23.2 代码实现及步骤

hive>

-- 求出1号商品和 2号商品每个月的购买总额并过滤掉没有满足指标的商品

select
  sku_id,
  concat(substring(create_date,0,7),'-01') ymd,
  sum(price*sku_num)  sku_sum
from
  order_detail
where 
  sku_id=1 or sku_id=2
group by
  sku_id,substring(create_date,0,7)
having
  (sku_id=1 and sku_sum>=21000) or (sku_id=2 and sku_sum>=10000)

-- 判断是否为连续两个月
select
  distinct t3.sku_id
from
  (
    select
      t2.sku_id,
      count(*)over(partition by t2.sku_id,t2.rymd) cn
    from
      (
        select
          t1.sku_id,
          add_months(t1.ymd,-row_number()over(partition by t1.sku_id order by t1.ymd)) rymd
        from
          (
           select
             sku_id,
             concat(substring(create_date,0,7),'-01') ymd,
             sum(price*sku_num)  sku_sum
           from
             order_detail
           where 
             sku_id=1 or sku_id=2
           group by
             sku_id,substring(create_date,0,7)
           having
             (sku_id=1 and sku_sum>=21000) or (sku_id=2 and sku_sum>=10000)
        )t1
    )t2
)t3
where 
  t3.cn>=2

2.24 根据商品销售情况进行商品分类

2.24.1 题目需求

从订单详情表中（order_detail）对销售件数对商品进行分类，0-5000为冷门商品，5001-19999位一般商品，20000往上为热门商品，并求出不同类别商品的数量

结果如下：

Category（类型）

Cn（数量）

一般商品

冷门商品

热门商品

2.24.2 代码实现

hive>

select
  t2.category,
  count(*) cn
from
  (
    select
      t1.sku_id,
      case 
      when  t1.sku_sum >=0 and t1.sku_sum<=5000 then '冷门商品'
      when  t1.sku_sum >=5001 and t1.sku_sum<=19999 then '一般商品'
      when  t1.sku_sum >=20000 then '热门商品'
      end  category
    from
      (
        select
          sku_id,
          sum(sku_num)  sku_sum
        from
          order_detail
        group by
          sku_id
    )t1
)t2
group by
  t2.category

2.25 各品类销量前三的所有商品

2.25.1 题目需求

从订单详情表中（order_detail）和商品（sku_info）中查询各个品类销售数量前三的商品。如果该品类小于三个商品，则输出所有的商品销量。

结果如下：

Sku_id（商品id）

Category_id（品类id）

2.25.2 代码实现

hive>

select
  t2.sku_id,
  t2.category_id
from
  (
    select
      t1.sku_id,
      si.category_id,
      rank()over(partition by category_id order by t1.sku_sum desc) rk
    from
      (
        select 
          sku_id,
          sum(sku_num) sku_sum
        from
          order_detail
        group by
          sku_id
    )t1
    join
      sku_info si
    on
      t1.sku_id=si.sku_id
    )t2
where 
  t2.rk<=3;

2.26 各品类中商品价格的中位数

2.26.1 题目需求

从商品（sku_info）中球中位数如果是偶数则输出中间两个值的平均值，如果是奇数，则输出中间数即可。

结果如下：

Category_id（品类id）

Medprice（中位数）

3500.0

1250.0

510.0

2.26.2 代码实现

hive>

--求个每个品类价格排序商品数量以及打上奇偶数的标签
select
  sku_id,
  category_id,
  price,
  row_number()over(partition by category_id order by price desc) rk,
  count(*)over(partition by category_id) cn,
  count(*)over(partition by category_id)%2 falg
from
  sku_info  t1

--求出偶数品类的中位数
select
  distinct t1.category_id,
  avg(t1.price)over(partition by t1.category_id) medprice
from
  (
select
  sku_id,
  category_id,
  price,
  row_number()over(partition by category_id order by price desc) rk,
  count(*)over(partition by category_id) cn,
  count(*)over(partition by category_id)%2 falg
from
  sku_info
)t1
where 
  t1.falg=0 and (t1.rk=cn/2  or t1.rk=cn/2+1)

--求出奇数品类的中位数
select
  t1.category_id,
  t1.price
from
  (
    select
      sku_id,
      category_id,
      price,
      row_number()over(partition by category_id order by price desc) rk,
      count(*)over(partition by category_id) cn,
      count(*)over(partition by category_id)%2 falg
    from
      sku_info
)t1
where 
  t1.falg=1 and t1.rk=round(cn/2)

-- 竖向拼接
select
  distinct t1.category_id,
  avg(t1.price)over(partition by t1.category_id) medprice
from
  (
select
  sku_id,
  category_id,
  price,
  row_number()over(partition by category_id order by price desc) rk,
  count(*)over(partition by category_id) cn,
  count(*)over(partition by category_id)%2 falg
from
  sku_info
)t1
where 
  t1.falg=0 and (t1.rk=cn/2  or t1.rk=cn/2+1)
 
union
 
select
  t1.category_id,
  t1.price/1
from
  (
    select
      sku_id,
      category_id,
      price,
      row_number()over(partition by category_id order by price desc) rk,
      count(*)over(partition by category_id) cn,
      count(*)over(partition by category_id)%2 falg
    from
      sku_info
)t1
where 
  t1.falg=1 and t1.rk=round(cn/2)

2.27 找出销售额连续3天超过100的商品

2.27.1 题目需求

从订单详情表（order_detail）中找出销售额连续3天超过100的商品

结果如下：

Sku_id（商品id）

2.27.2 代码实现

hive>

-- 每个商品每天的销售总额
select
  sku_id,
  create_date,
  sum(price*sku_num) sku_sum
from
  order_detail
group by
  sku_id,create_date
having 
  sku_sum>=100

--  判断连续三天以上
select
  distinct t3.sku_id
from
  (
    select
      t2.sku_id,
      count(*)over(partition by t2.sku_id,t2.date_drk) cdrk
    from
      (
        select
          t1.sku_id,
          t1.create_date,
          date_sub(t1.create_date,rank()over(partition by t1.sku_id order by t1.create_date)) date_drk
        from
          (
            select
              sku_id,
              create_date,
              sum(price*sku_num) sku_sum
            from
              order_detail
            group by
              sku_id,create_date
            having 
              sku_sum>=100
        )t1
    )t2
)t3
where
  t3.cdrk>=3

2.28 查询有新注册用户的当天的新用户数量、新用户的第一天留存率

2.28.1 题目需求

从用户登录明细表（user_login_detail）中首次登录算作当天新增，第二天也登录了算作一日留存

结果如下：

first_login（注册时间）

Retention（留存率）

2021-09-21

0.0

2021-09-22

0.0

2021-09-23

0.0

2021-09-24

0.0

2021-09-25

0.0

2021-09-26

0.0

2021-09-27

0.0

2021-10-04

0.5

2021-10-06

0.0

2.28.2 代码实现

hive>

-- 每个用户首次登录时间 和 第二天是否登录 并看每天新增和留存数量 
select
  t1.first_login,
  count(t1.user_id) register,
  count(t2.user_id) remain_1
from
  (
   select
     user_id,
     date_format(min(login_ts),'yyyy-MM-dd')   first_login
   from
     user_login_detail
   group by
     user_id
   )t1
left join
  user_login_detail t2
on
  t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,'yyyy-MM-dd'),t1.first_login)=1
group by
  t1.first_login

-- 新增数量和留存率
select
  t3.first_login,
  t3.register,
  t3.remain_1/t3.register retention
from
  (
   select
     t1.first_login,
     count(t1.user_id) register,
     count(t2.user_id) remain_1
   from
     (
      select
        user_id,
        date_format(min(login_ts),'yyyy-MM-dd')   first_login
      from
        user_login_detail
      group by
        user_id
      )t1
   left join
     user_login_detail t2
   on
     t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,'yyyy-MM-dd'),t1.first_login)=1
   group by
     t1.first_login
)t3

2.29 求出商品连续售卖的时间区间

2.29.1 题目需求

从订单详情表（order_detail）中，求出商品连续售卖的时间区间

结果如下（截取部分）：

Sku_id（商品id）

Start_date（起始时间）

End_date（结束时间）

2021-09-27

2021-09-30

2021-10-01

2021-10-03

2021-10-08

2021-10-02

2021-10-03

2021-10-05

2021-10-08

2021-10-02

2021-10-08

2021-09-30

2021-10-02

2021-10-06

2021-10-08

2.29.2 代码实现

hive>

-- 每个商品售卖的日期以及拿到按排序后日期的差值
select
  sku_id,
  create_date,
  date_sub(create_date,rank()over(partition by sku_id order by create_date)) ddrk
from
  order_detail
group by
  sku_id,create_date

-- 拿到每次售卖的区间
select
  distinct
  sku_id,
  first_value(t1.create_date)over(partition by t1.sku_id,t1.ddrk order by t1.create_date  rows between unbounded preceding and unbounded following) start_date,
  last_value(t1.create_date)over(partition by t1.sku_id,t1.ddrk order by t1.create_date  rows between unbounded preceding and unbounded following) end_date
from
  (
   select
     sku_id,
     create_date,
     date_sub(create_date,rank()over(partition by sku_id order by create_date)) ddrk
   from
     order_detail
   group by
     sku_id,create_date
)t1

2.30 登录次数及交易次数统计

2.30.1 题目需求

分别从登陆明细表（user_login_detail）和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数

结果如下（截取部分）：

User_id

（用户id）

Login_date

（登录时间）

login_count

（登陆次数）

Order_count

（交易次数）

101

2021-09-21

101

2021-09-27

101

2021-09-28

101

2021-09-29

101

2021-09-30

1010

2021-09-27

1010

2021-10-09

102

2021-09-22

102

2021-10-01

2.30.2 代码实现

hive>

-- 拿到每个用户每天的登录次数
select
  user_id,
  date_format(login_ts,'yyyy-MM-dd') login_date,
  count(*) login_count
from
  user_login_detail
group by
  user_id,date_format(login_ts,'yyyy-MM-dd')

-- 拿到每个用户每天的交易次数
select
  t1.user_id,
  t1.login_date,
  collect_set(t1.login_count)[0] login_count ,
  count(di.user_id) order_count
from
  (
   select
     user_id,
     date_format(login_ts,'yyyy-MM-dd') login_date,
     count(*) login_count
   from
     user_login_detail
   group by
     user_id,date_format(login_ts,'yyyy-MM-dd')
)t1
left join
  delivery_info di
on
  t1.user_id=di.user_id and t1.login_date=di.order_date
group by
  t1.user_id,t1.login_date

2.31 按年度列出每个商品销售总额

2.31.1 题目需求

从订单明细表（order_detail）中列出每个商品每个年度的购买总额

结果如下（截取部分）：

Sku_id（商品id）

Year_date（年份）

Sku_sum（销售总额）

2021

102000.00

2021

29900.00

2021

16000.00

2021

413640.00

2021

60440.00

2021

180000.00

2021

318000.00

2021

121000.00

2021

64000.00

2021

25200.00

2021

151800.00

2021

194000.00

2.31.2 代码实现

hive> 
select
  sku_id,
  year(create_date) year_date,
  sum(price*sku_num) sku_sum
from
  order_detail
group by
  sku_id,year(create_date)

2.32. 某周内每件商品每天销售情况

2.32.1 题目需求

从订单详情表（order_detail）中查询2021年9月27号-2021年10月3号这一周所有商品每天销售情况。

结果如下：

Sku_id

（商品id）

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

20400

5800

2.32.2 代码实现

hive>

select
  sku_id,
  sum(if(dayofweek(create_date)=2,sku_num,0)) Monday,
  sum(if(dayofweek(create_date)=3,sku_num,0)) Tuesday,
  sum(if(dayofweek(create_date)=4,sku_num,0)) Wednesday,
  sum(if(dayofweek(create_date)=5,sku_num,0)) Thursday,
  sum(if(dayofweek(create_date)=6,sku_num,0)) Friday,
  sum(if(dayofweek(create_date)=7,sku_num,0)) Saturday,
  sum(if(dayofweek(create_date)=1,sku_num,0)) Sunday
from
  order_detail
where
  create_date>='2021-09-27' and create_date<='2021-10-03'
group by
  sku_id

2.33 查看每件商品的售价涨幅情况

2.33.1 题目需求

从商品价格变更明细表（sku_price_modify_detail），得到最近一次价格的涨幅情况，并按照涨幅升序排序。

结果如下：

Sku_id（商品id）

Price_change（涨幅）

-200.00

-100.00

-70.00

-16.00

-15.00

1.00

10.00

12.00

100.00

400.00

2.33.2 代码实现

hive>

-- 对每个商品按照修改日期倒序排序 并求出差值
select
  sku_id,
  new_price-lead(new_price,1,0)over(partition by sku_id order by change_date desc) price_change,
  rank()over(partition by sku_id order by change_date desc) rk
from
  sku_price_modify_detail   t1

-- 最近一次修改的价格
select
  t1.sku_id,
  t1.price_change
from
  (
    select
      sku_id,
      new_price-lead(new_price,1,0)over(partition by sku_id order by change_date desc) price_change,
      rank()over(partition by sku_id order by change_date desc) rk
    from
      sku_price_modify_detail
)t1
where 
  rk=1
order by
  t1.price_change

2.34 销售订单首购和次购分析

2.34.1 题目需求

通过商品信息表（sku_info）订单信息表（order_info）订单明细表（order_detail）分析如果有一个用户成功下单两个及两个以上的购买成功的手机订单（购买商品为xiaomi 10，apple 12，小米13）那么输出这个用户的id及第一次成功购买手机的日期和第二次成功购买手机的日期，以及购买手机成功的次数。

结果如下：

User_id

（用户id）

First_date

（首次时间）

Last_value

（末次时间）

（购买次数）

101

2021-09-27

2021-09-28

1010

2021-10-08

102

2021-10-01

103

2021-09-30

2021-10-02

104

2021-10-03

105

2021-10-04

106

2021-10-04

2021-10-05

107

2021-10-05

108

2021-10-06

109

2021-10-07

2.34.2 代码实现

hive>

select
  distinct oi.user_id,
  first_value(od.create_date)over(partition by oi.user_id order by od.create_date rows between unbounded preceding and unbounded following ) first_date,
  last_value(od.create_date)over(partition by oi.user_id order by od.create_date rows between unbounded preceding and unbounded following ) last_date,
  count(*)over(partition by oi.user_id order by od.create_date rows between unbounded preceding and unbounded following) cn
from
   order_info oi
join
   order_detail od
on
  oi.order_id=od.order_id
join
  sku_info si
on
  od.sku_id=si.sku_id
where
  si.name in('xiaomi 10','apple 12','xiaomi 13')

2.35 同期商品售卖分析表

2.35.1 题目需求

从订单明细表（order_detail）中。

求出同一个商品在2021年和2022年中同一个月的售卖情况对比。

结果如下（截取部分）：

Sku_id

（商品id）

Month

（月份）

2020_skusum

（2020销售量）

2021_skusum

（2021销售量）

205

225

20556

6018

2.35.2 代码实现

hive>

select
  if(t1.sku_id is null,t2.sku_id,t1.sku_id),
  month(if(t1.ym is null,t2.ym,t1.ym)) ,
  if(t1.sku_sum is null ,0 ,t1.sku_sum) 2020_skusum,
  if(t2.sku_sum is null ,0 ,t2.sku_sum) 2020_skusum
from
  (
    select
      sku_id,
      concat(date_format(create_date,'yyyy-MM'),'-01') ym,
      sum(sku_num) sku_sum
    from
      order_detail
    where
      year(create_date)=2020
    group by
      sku_id,date_format(create_date,'yyyy-MM')
)t1
full join
  (
    select
      sku_id,
      concat(date_format(create_date,'yyyy-MM'),'-01')  ym,
      sum(sku_num) sku_sum
    from
      order_detail
    where
      year(create_date)=2021
    group by
      sku_id,date_format(create_date,'yyyy-MM')
)t2
on
  t1.sku_id=t2.sku_id and month(t1.ym) = month(t2.ym)

2.36 国庆期间每个品类的商品的收藏量和购买量

2.36.1 题目需求

从订单明细表（order_detail）和收藏信息表（favor_info）统计2021国庆期间，每个商品总收藏量和购买量

结果如下：

Sku_id

Sku_sum（购买量）

Favor_cn（收藏量）

205

225

20556

6018

209

180

148

182

2.36.2 代码实现

hive>

select
  t1.sku_id,
  t1.sku_sum,
  t2.favor_cn
from
  (
    select
      sku_id,
      sum(sku_num) sku_sum
    from
      order_detail
    where
      create_date>='2021-10-01' and create_date<='2021-10-07'
    group by 
      sku_id
)t1
join
  (
    select
      sku_id,
      count(*) favor_cn
    from
      favor_info
    where
      create_date>='2021-10-01' and create_date<='2021-10-07'
    group by 
      sku_id
)t2
on 
  t1.sku_id=t2.sku_id

2.37 统计活跃间隔对用户分级结果

2.37.1 题目需求

用户等级：

忠实用户：近7天活跃且非新用户

新晋用户：近7天新增

沉睡用户：近7天未活跃但是在7天前活跃

流失用户：近30天未活跃但是在30天前活跃

假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数

结果如下：

Level（用户等级）

Cn（用户数量)

忠实用户

新增用户

沉睡用户

2.37.2 代码实现

hive>

select
  t2.level,
  count(*)
from
  (
    select
      uld.user_id,
      case
         when (date_format(max(uld.login_ts),'yyyy-MM-dd') <=date_sub(today, 30))
             then '流失用户'-- 最近登录时间三十天前
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') <=date_sub(today, 7) and date_format(max(uld.login_ts),'yyyy-MM-dd') >=date_sub(today, 7))
             then '忠实用户' -- 最早登陆时间是七天前,并且最近七天登录过
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') >=date_sub(today, 7))
             then '新增用户' -- 最早登录时间是七天内
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') <= date_sub(today, 7) and date_format(max(uld.login_ts),'yyyy-MM-dd') <= date_sub(today, 7))
             then '沉睡用户'-- 最早登陆时间是七天前,最大登录时间也是七天前
       end level
    from
      user_login_detail  uld
    join
      (
        select 
          date_format(max(login_ts),'yyyy-MM-dd') today 
        from
          user_login_detail
    )t1
    on
      1=1
    group by 
      uld.user_id,t1.today
    )t2
group by
  t2.level

2.38 连续签到领金币数

2.38.1 题目需求

用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。

每连续签到7天重新累积签到天数。

从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序

结果如下：

User_id（用户id）

Sum_coin_cn（金币总数)

101

109

107

102

106

104

103

1010

108

105

2.38.2 代码实现

hive>

-- 求连续并标志是连续的第几天
select
  t1.user_id,
  t1.login_date,
  date_sub(t1.login_date,t1.rk) login_date_rk,
  count(*)over(partition by t1.user_id, date_sub(t1.login_date,t1.rk) order by t1.login_date) counti_cn
from
  (
   select
     user_id,
     date_format(login_ts,'yyyy-MM-dd') login_date,
     rank()over(partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
   from
     user_login_detail
   group by
     user_id,date_format(login_ts,'yyyy-MM-dd')
)t1

--求出金币数量，以及签到奖励的金币数量
select
  t2.user_id,
  max(t2.counti_cn)+sum(if(t2.counti_cn%3=0,2,0))+sum(if(t2.counti_cn%7=0,6,0)) coin_cn
from
  (
select
  t1.user_id,
     t1.login_date,
     date_sub(t1.login_date,t1.rk) login_date_rk,
     count(*)over(partition by t1.user_id, date_sub(t1.login_date,t1.rk) order by t1.login_date) counti_cn
   from
     (
      select
        user_id,
        date_format(login_ts,'yyyy-MM-dd') login_date,
        rank()over(partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
      from
        user_login_detail
      group by
        user_id,date_format(login_ts,'yyyy-MM-dd')
   )t1
)t2
group by
  t2.user_id,t2.login_date_rk

-- 求出每个用户的金币总数
select
  t3.user_id,
  sum(t3.coin_cn) sum_coin_cn
from 
  (
    select
      t2.user_id,
      max(t2.counti_cn)+sum(if(t2.counti_cn%3=0,2,0))+sum(if(t2.counti_cn%7=0,6,0)) coin_cn
    from
      (
    select
      t1.user_id,
         t1.login_date,
         date_sub(t1.login_date,t1.rk) login_date_rk,
         count(*)over(partition by t1.user_id, date_sub(t1.login_date,t1.rk) order by t1.login_date) counti_cn
       from
         (
          select
            user_id,
            date_format(login_ts,'yyyy-MM-dd') login_date,
            rank()over(partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
          from
            user_login_detail
          group by
            user_id,date_format(login_ts,'yyyy-MM-dd')
       )t1
    )t2
    group by
      t2.user_id,t2.login_date_rk
    )t3
group by
  t3.user_id
order by
  sum_coin_cn desc

2.39 国庆期间的7日动销率和滞销率

2.39.1 题目需求

动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。

滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/ 已上架总商品数）。

只要当天任一店铺有任何商品的销量就输出该天的结果

从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率

结果如下（截取部分）：

Category_id

（品类id）

1号

（动销)

1号

（滞销)

2号

（动销)

2号

（滞销)

3号

（动销)

3号

（滞销)

1.0

0.0

0.5

0.75

0.25

0.75

0.25

0.75

0.25

0.75

0.25

0.75

0.25

0.75

0.25

2.39.2 代码实现

hive>

-- 国庆每一天 每个商品品类有多少商品被销售了
select
  t1.category_id,
  sum(if(t1.create_date='2021-10-01',1,0)) `第1天`,
  sum(if(t1.create_date='2021-10-02',1,0)) `第2天`,
  sum(if(t1.create_date='2021-10-03',1,0)) `第3天`,
  sum(if(t1.create_date='2021-10-04',1,0)) `第4天`,
  sum(if(t1.create_date='2021-10-05',1,0)) `第5天`,
  sum(if(t1.create_date='2021-10-06',1,0)) `第6天`,
  sum(if(t1.create_date='2021-10-07',1,0)) `第7天`
from
  (
    select 
      distinct 
      si.category_id,
      od.create_date,
      si.name
    from 
      order_detail od
    join
      sku_info si
    on
      od.sku_id=si.sku_id
    where
      od.create_date>='2021-10-01' and od.create_date<='2021-10-07'
)t1
group by
  t1.category_id

-- 每一天的动销率 和 滞销率
select
  t2.category_id,
  t2.`第1天`/t3.cn,
  1-t2.`第1天`/t3.cn,
  t2.`第2天`/t3.cn,
  1-t2.`第2天`/t3.cn,
  t2.`第3天`/t3.cn,
  1-t2.`第3天`/t3.cn,
  t2.`第4天`/t3.cn,
  1-t2.`第4天`/t3.cn,
  t2.`第5天`/t3.cn,
  1-t2.`第5天`/t3.cn,
  t2.`第6天`/t3.cn,
  1-t2.`第6天`/t3.cn,
  t2.`第7天`/t3.cn,
  1-t2.`第7天`/t3.cn
from
  (
   select
     t1.category_id,
     sum(if(t1.create_date='2021-10-01',1,0)) `第1天`,
     sum(if(t1.create_date='2021-10-02',1,0)) `第2天`,
     sum(if(t1.create_date='2021-10-03',1,0)) `第3天`,
     sum(if(t1.create_date='2021-10-04',1,0)) `第4天`,
     sum(if(t1.create_date='2021-10-05',1,0)) `第5天`,
     sum(if(t1.create_date='2021-10-06',1,0)) `第6天`,
     sum(if(t1.create_date='2021-10-07',1,0)) `第7天`
   from
     (
       select 
         distinct 
         si.category_id,
         od.create_date,
         si.name
       from 
         order_detail od
       join
         sku_info si
       on
         od.sku_id=si.sku_id
       where
         od.create_date>='2021-10-01' and od.create_date<='2021-10-07'
   )t1
   group by
     t1.category_id
   )t2
join
  (
    select
      category_id,
      count(*) cn
    from
      sku_info
    group by
      category_id
    )t3
on 
  t2.category_id=t3.category_id

2.40 同时在线最多的人数

2.40.1 题目需求

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

结果如下：

Cn（人数）

2.40.2 代码实现

hive>

-- 登录标记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

标签：大数据 hive sql

本文转载自: https://blog.csdn.net/qq_50954361/article/details/129706711
版权归原作者骚戴所有，如有侵权，请联系我们删除。

大数据技术之Hive SQL题库-中级

第1章 环境准备

1.1 用户信息表

1.2 商品信息表

1.3 商品分类信息表

1.4 订单信息表

1.5 订单明细表

1.6 登录明细表

1.7 商品价格变更明细表

1.8 配送信息表

1.9 好友关系表

1.10 收藏信息表

第2章 练习题

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

2.1.1 题目需求

2.1.2 代码实现

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

2.2.1 题目需求

2.2.2 代码实现

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

2.3.1 题目需求

2.3.2 代码实现

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

2.4.1 题目需求

2.4.2 代码实现

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

2.5.1 题目需求

2.5.2 代码实现

2.6 每个商品销售首年的年份、销售数量和销售金额

2.6.1 题目需求

2.6.2 代码实现

2.7 筛选去年总销量小于100的商品

2.7.1 题目需求

2.7.2 代码实现

2.8 查询每日新用户数

2.8.1 题目需求

2.8.2 代码实现

2.9 统计每个商品的销量最高的日期

2.9.1 题目需求

2.9.2 代码实现

2.10 查询销售件数高于品类平均数的商品

2.10.1 题目需求

2.10.2 代码实现

2.11 用户注册、登录、下单综合统计

2.11.1 题目需求

2.11.2 代码实现

2.12 查询指定日期的全部商品价格

2.12.1 题目需求

2.12.2 代码实现

2.13 即时订单比例

2.13.1 题目需求

2.13.2 代码实现

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

2.14.1 题目需求

2.14.2 代码实现

2.15 查询所有用户的连续登录两天及以上的日期区间

2.15.1 题目需求

2.15.2 代码实现

2.16 男性和女性每日的购物总金额统计

2.16.1 题目需求

2.16.2 代码实现

*2.17 订单金额趋势分析

2.17.1 题目需求

2.17.2 代码实现

2.18 购买过商品1和商品2但是没有购买商品3的顾客

2.18.1 题目需求

2.18.2 代码实现

2.19 统计每日商品1和商品2销量的差值

2.19.1 题目需求

2.19.2 代码实现

2.20 查询出每个用户的最近三笔订单

2.20.1 题目需求

2.20.2 代码实现

2.21 查询每个用户登录日期的最大空档期

2.21.1 题目需求

2.21.2 代码实现

*2.22 查询相同时刻多地登陆的用户

2.22.1 题目需求

2.22.2 代码实现

2.23 销售额完成任务指标的商品

第1章环境准备

第2章练习题