当前位置： 首页 > news >正文

Hive 中级练习题（40题 待更新）

news 2025/8/24 0:22:28

前言

最近快一周没更了，主要原因是最近在忙另一件事情（关于JavaFX桌面软件开发），眼看大三上一半时间就要过去了，抓紧先学Hive，完了把 Spark 剩下的补了，还有 Kafka、Flume，任务还是不少的，最后再放假前做一个小的大数据项目，希望一切顺利。

环境准备

这部分直接上代码，反正也没人看哈哈哈。

-- SQL 中级
show databases ;create database practice2;
use practice2;create table user_info(`user_id`  string COMMENT '用户id',`gender`   string COMMENT '性别',`birthday` string COMMENT '生日'
) COMMENT '用户信息表'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';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');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';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);create table category_info(`category_id`   string,`category_name` string
) COMMENT '品类表'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';insert overwrite table category_info
values ('1','数码'),('2','厨卫'),('3','户外');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';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);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';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);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';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');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';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');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';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');CREATE TABLE friendship_info(`user1_id` string comment '用户1id',`user2_id` string comment '用户2id'
) COMMENT '用户关系表'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';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');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';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');

题目

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

题目需求

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

代码

这里使用了窗口函数中的排名函数-dense_rank() ，对于相同值的数据，它的排名是相同的，比如100,100,200，它的排名就是 1,1,2，默认是降序的，这里我们需要设置为升序排列。

这里不需要分区。

对于不同订单，它可能会有相同的商品，所以我们需要用一个聚合函数sum来实现。

select sku_id from (select sku_id,order_num,dense_rank() over (order by order_num desc) rankfrom (select sku_id,sum(sku_num) order_numfrom order_detailgroup by sku_id)t1)
where rank=2;

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

题目需求

查询订单信息表(order_info)中最少连续3天下单的用户id。

代码

1. 找到每个用户的下单日期（需要去重）

-- 找到用户的下单日期-需要去重(一天下单多次)
select distinct user_id,create_date
from order_info;-- 第二种去重方式
select user_id, create_date
from order_info
group by user_id,create_date;    --group by 就是把相同的字段值放到一起--第三种去重方式
select user_id,create_date
from(
select user_id,create_date,row_number() over (partition by user_id,create_date) rn
from order_info)t1
where rn=1;
查看SQL语句执行计划
explain select distinct user_id,create_date
from order_info;

2.查看连续三天下单的用户

思路1：

user_id      create_date      lead(create_date,2,'9999-12-31')      datadiff
1001          2023-06-01            2023-06-03                      2
1001          2023-06-02            2023-06-06                      4
1001          2023-06-03            2023-06-07                      4
1001          2023-06-06            2023-06-08                      2
1001          2023-06-07            9999-12-31                      2913****
1001          2023-06-08            9999-12-31                      2913****
select distinct user_id
from(select user_id,datediff(create_date,lead2) difffrom(select user_id,create_date,lead(create_date,2,'9999-12-31') over (partition by user_id order by create_date) lead2from(select distinct user_id,create_datefrom order_info)t1)t2)t3
where diff=2;

思路2

user_id      create_date      rank()    diff        
1001          2023-06-02      1         2023-06-01
1001          2023-06-03      2         2023-06-01
1001          2023-06-04      3         2023-06-01
1001          2023-06-06      4         2023-06-02
1001          2023-06-07      5         2023-06-02
1001          2023-06-08      6         2023-06-02
select distinct user_id
from(select user_id,diff,count(*) cntfrom (select user_id,create_date,date_sub(create_date, rank() over (partition by user_id order by create_date)) difffrom (select distinct user_id,create_datefrom order_info) t1)t2
group by user_id,diff
having cnt)t3;

思路3

user_id      create_date      count() over(partition by user_id order by create_date range between 86400 preceding and 86400 following )
1001          2023-06-02            2
1001          2023-06-03            3
1001          2023-06-04            2
1001          2023-06-06            2
1001          2023-06-07            3
1001          2023-06-08            2

select distinct user_id
from (select user_id,ts,count(*) over (partition by user_id order by ts range between 86400 preceding and 86400 following) cntfrom (select user_id,unix_timestamp(create_date, 'yyyy-MM-dd') tsfrom (select distinct user_id,create_datefrom order_info) t1) t2)t3
where cnt=3;

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

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

category_id

(分类id)

category_name

(分类名称)

sku_id

(销量最好的商品id)

name

(商品名称)

order_num

（销量最好的商品销量）

order_cnt

(商品种类数量)

1

数码

2

手机壳

302

4

2

厨卫

8

微波炉

253

4

3

户外

12

遮阳伞

349

4

select t1.category_id,category_name,sku_id,name,order_num,order_cnt
from(
-- 查询每件商品的下单数量和商品类别select od.sku_id,order_num,category_name,ci.category_id,si.name,rank() over (partition by ci.category_id order by order_num desc) rk,count(distinct od.sku_id) over (partition by ci.category_id) order_cntfrom(select sku_id,sum(sku_num) order_numfrom order_detailgroup by sku_id) odleft join sku_info sion od.sku_id=si.sku_idleft join category_info cion si.category_id=ci.category_id)t1
where rk=1;

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

从订单信息表(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

黄金会员

1、先查询用户每天的下单金额

select user_id,create_date,sum(total_amount) total_amount_per_dayfrom order_infogroup by user_id, create_date;

2、查询截止当前的累计下单金额

select user_id,create_date,sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_farfrom (select user_id,create_date,sum(total_amount) total_amount_per_dayfrom order_infogroup by user_id, create_date)t1);

3、根据截止当前累计下单金额判断用户vip等级

select user_id,create_date,sum_so_far,casewhen 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_farfrom (select user_id,create_date,sum(total_amount) total_amount_per_dayfrom order_infogroup by user_id, create_date)t1)t2;

相关文章：

Hive 中级练习题（40题 待更新）

前言 最近快一周没更了&#xff0c;主要原因是最近在忙另一件事情&#xff08;关于JavaFX桌面软件开发&#xff09;&#xff0c;眼看大三上一半时间就要过去了&#xff0c;抓紧先学Hive&#xff0c;完了把 Spark 剩下的补了&#xff0c;还有 Kafka、Flume&#xff0c;任务还是…...

编程日记 2023/10/22 17:39:07

核酸检测人员安排

题目描述： 在系统、网络均正常的情况下组织核酸采样员和志愿者对人群进行核酸检测筛查。每名采样员的效率不同，采样效率为N人/小时。由于外界变化，采样员的效率会以M人/小时为粒度发生变化，M为采样效率浮动粒度，M=N10%，输入保证N10%的结果为整数。采样员效率浮动规则：采…...

编程日记 2023/10/22 17:38:05

Vue组件间传值

一、父传子 子组件中定义一个props&#xff0c;用来取出父组件传来的值 <script>export default {props:[msg] //子组件定义props} </script> 在父组件中对子组件的自定义属性绑定父组件的变量 <template><div class"parent">//子组件&a…...

编程日记 2023/10/22 17:37:05

《低代码指南》——维格云和Airtable的比较

Airtable​ 什么是Airtable​ Airtable 是一个任务管理应用程序，它合并了电子表格、数据存储和模板，以帮助组织构建他们的工作流程。 适用于哪些企业/组织/人群​ 根据 Airtable 网站，该工具被超过 200,000 个组织的团队使用。 维格表与Airtable相比如何​ Airtable作为…...

编程日记 2023/10/22 17:36:02

牛客：NC59 矩阵的最小路径和

牛客&#xff1a;NC59 矩阵的最小路径和 文章目录 牛客&#xff1a;NC59 矩阵的最小路径和题目描述题解思路题解代码 题目描述 题解思路 动态规划&#xff0c;递推公式&#xff1a;matrix[i][j] min(matrix[i-1][j], matrix[i][j-1]) 题解代码 func minPathSum( matrix [][…...

编程日记 2023/10/22 17:35:02

20231017定时任务

1. 构建定时任务 表达式生成 在线Cron表达式生成器 1.1 启动类 1.2 测试范例 描述: 1,将该类用Component描述,交给spring管理. 2,定时任务方法用Scheduled&#xff0b;cron表达式描述 2. 定时任务的弊端和优化方案 1.假如有一个定时任务,每小时检查关闭超时未支付订单,当10…...

编程日记 2023/10/22 17:33:56

通讯录和内存动态管理

目录 (通讯录)动态增长版 实现效果 找单身狗 题目 源码 思路 三个内存函数的模拟实现 模拟实现strncpy 模拟实现strncat 模拟实现atoi (通讯录)动态增长版 该版本通讯录在原版的基础上增加了检查容量函数&#xff0c;实现了通讯录的动态…...

编程日记 2023/10/22 17:32:53

安全渗透测试之网络基础知识(IP地址)

#1.IP地址介绍 注意：不同局域网需要有不同的网络部分，通过网络部分区别出网段/网络； 局域网内部，主机部分不能一样，否则会出现地址冲突 范围：0.0.0.0-255.255.255.255 表示：点分十进制 组成：由网络部分和主机部分组成 192.168.1.1 1.1.1.1 255.254.188.2 二进制：00000…...

编程日记 2023/10/22 17:31:53

dubbogo-1 基础rpc服务

文章目录 基本环境处理编译pb接口开启rpc调用业务观察qa1 能取出protoc里面的字段值吗&#xff1f; 基本环境处理 https://cn.dubbo.apache.org/zh-cn/overview/quickstart/go/install/ 这里没有 protoc-gen-go --version 执行 go get -u github.com/golang/protobuf/protoc…...

编程日记 2023/10/22 17:30:51

分布式缓存Spring Cache

一、缓存里的数据如何和数据库的数据保持一致&#xff1f; 缓存数据一致性1)、双写模式2)、失效模式1、缓存数据一致性-双写模式 2、 缓存数据一致性-失效模式 我们系统的一致性解决方案: 1、缓存的所有数据都有过期时间&#xff0c;数据过期下一次查询触发主动更新 2、读写数据…...

编程日记 2023/10/22 17:29:50

CI2454 2.4g无线MCU芯片应用

Ci2454集成MCU芯片 | Ci2454是一款集成无线收发器和 8 位 RISC&#xff08;精简指令集&#xff09;MCU 的SOC芯片。 #Ci2454芯片 集成MCU芯片# 中国芯片# 无线收发器特性&#xff1a; 工作在 2.4GHz ISM 频段 调制方式&#xff1a;GFSK/FSK 数据速率&#xff1a;2Mbps/1Mbps…...

编程日记 2023/10/22 17:28:49

生成包含10个随机字母或数字的字符串,然后统计每个字符的出现次数

from random import choices from string import ascii_letters, digitsx .join(choices(ascii_lettersdigits, k10)) d dict() # 创建空字典 for ch in x:d[ch] d.get(ch, 0) 1 # x中有ch字符,个数1,并作为字典的值 print(x) print(d)也可以使用collections模块的defaul…...

编程日记 2023/10/22 17:26:46

huggingface 模型推理几个重要到类

pipeline 它可以让您方便地使用预训练的模型进行各种任务。当您用pipeline函数创建一个图像分割的pipeline时&#xff0c;它会自动加载和初始化一个SegformerForSemanticSegmentation的实例&#xff0c;并且封装了一些预处理和后处理的逻辑&#xff0c;例如将图像转换为张量&a…...

编程日记 2023/10/22 17:24:41

qml之动态元素类型

文章目录 动画例子 应用动画例子 缓动曲线例子 动画分组例子 嵌套动画代码 状态和转换代码 动画 QMlL使用插值的方式控制属性的更改。动画是在指定的时间内一些列属性的持续变化。 常用的动画类型元素动画&#xff1a;PropertyAnimation:属性值改变播放动画NumberAnimation:qr…...

编程日记 2023/10/22 17:23:40

超详细 | 差分进化算法原理及其实现(Matlab/Python)

差分进化(Differential Evolution&#xff0c;DE)算法是由美国学者Storn和 Price在1995年为求解Chebyshev多项式拟合问题而提出的。算法主要通过基于差分形式的变异操作和基于概率选择的交叉操作进行优化搜索&#xff0c;虽然其操作名称和遗传算法相同&#xff0c;但实现方法有…...

编程日记 2023/10/22 17:22:38

大二第三周总结（算法+生活）

算法&#xff1a; 题目&#xff1a;有效的括号 这个题目也是做过很多回了。主要就是数据结构中”栈“的应用&#xff0c;先进后出。 解题思路&#xff1a; 1.创建 Map 哈希表形成键值对映射 2.进行遍历字符串 在遍历过程中 如果 遍历到的字符c 是左括号&#xff0c;则入栈 pu…...

编程日记 2023/10/22 17:21:37

Lake Formation 和 IAM 之间的区别与联系

IAM 和 Lake Formation 都是 AWS 上的权限管理服务，且默认都是自动开启并生效的，只是如果你没有特别配置过它们，可能感觉不到它们的存在，特别是Lake Formation（后文简写为 LF），通常情况下都是“透明”的，但它确实在每次请求时进行了权限检查。本文会详细介绍一下两者之…...

编程日记 2023/10/22 17:18:35

音频抓取代码示例

以下是一个使用DefaultsKit库的简单爬虫程序&#xff0c;用于爬取音频。代码中使用了https://www.duoip.cn/get_proxy的API获取代理服务器。 import Foundation import DefaultsKit ​ let url "https://www.douban.com/music" // 目标网站URL let proxyUrl "…...

编程日记 2023/10/22 17:17:32

Hexo搭建个人博客系列之环境准备

环境准备 Git Git官网&#xff0c;安装过程,就是一直下一步,详细的看这篇文章 Git的安装 Node.js Node.js官网 Node.js的安装 注册一个GitHub账号 安装hexo 新建一个文件夹(位置任意),运行cmd(若出现了operation not permitted,就以管理员的权限来运行cmd)&#xff0c;运行…...

编程日记 2023/10/22 17:15:30

小程序技术在信创操作系统中的应用趋势：适配能力有哪些？

小程序技术在信创操作系统中的应用前景非常广阔&#xff0c;但也面临着一些挑战和问题。开发者需要积极应对这些挑战和问题&#xff0c;为信创操作系统的发展和推广做出贡献。同时&#xff0c;开发者也需要关注小程序技术在信创操作系统中的应用趋势&#xff0c;积极探索新的应…...

编程日记 2023/10/22 17:13:28

KubeSphere 容器平台高可用：环境搭建与可视化操作指南

Linux_k8s篇 欢迎来到Linux的世界&#xff0c;看笔记好好学多敲多打&#xff0c;每个人都是大神&#xff01; 题目&#xff1a;KubeSphere 容器平台高可用&#xff1a;环境搭建与可视化操作指南 版本号: 1.0,0 作者: 老王要学习 日期: 2025.06.05 适用环境: Ubuntu22 文档说…...

编程新知 2025/8/23 9:15:45

Vue3 + Element Plus + TypeScript中el-transfer穿梭框组件使用详解及示例

使用详解 Element Plus 的 el-transfer 组件是一个强大的穿梭框组件&#xff0c;常用于在两个集合之间进行数据转移&#xff0c;如权限分配、数据选择等场景。下面我将详细介绍其用法并提供一个完整示例。 核心特性与用法 基本属性 v-model&#xff1a;绑定右侧列表的值&…...

编程新知 2025/8/22 3:28:41

Debian系统简介

目录 Debian系统介绍 Debian版本介绍 Debian软件源介绍 软件包管理工具dpkg dpkg核心指令详解 安装软件包 卸载软件包 查询软件包状态 验证软件包完整性 手动处理依赖关系 dpkg vs apt Debian系统介绍 Debian 和 Ubuntu 都是基于 Debian内核 的 Linux 发行版&#xff…...

编程新知 2025/8/22 3:50:45

【机器视觉】单目测距——运动结构恢复

ps&#xff1a;图是随便找的&#xff0c;为了凑个封面 前言 在前面对光流法进行进一步改进&#xff0c;希望将2D光流推广至3D场景流时&#xff0c;发现2D转3D过程中存在尺度歧义问题&#xff0c;需要补全摄像头拍摄图像中缺失的深度信息&#xff0c;否则解空间不收敛&#xf…...

编程新知 2025/8/22 4:42:14

React Native在HarmonyOS 5.0阅读类应用开发中的实践

一、技术选型背景 随着HarmonyOS 5.0对Web兼容层的增强&#xff0c;React Native作为跨平台框架可通过重新编译ArkTS组件实现85%以上的代码复用率。阅读类应用具有UI复杂度低、数据流清晰的特点。 二、核心实现方案 1. 环境配置 &#xff08;1&#xff09;使用React Native…...

编程新知 2025/8/19 2:57:37

电脑插入多块移动硬盘后经常出现卡顿和蓝屏

当电脑在插入多块移动硬盘后频繁出现卡顿和蓝屏问题时&#xff0c;可能涉及硬件资源冲突、驱动兼容性、供电不足或系统设置等多方面原因。以下是逐步排查和解决方案&#xff1a; 1. 检查电源供电问题 问题原因&#xff1a;多块移动硬盘同时运行可能导致USB接口供电不足&#x…...

编程新知 2025/8/2 6:48:59

【服务器压力测试】本地PC电脑作为服务器运行时出现卡顿和资源紧张（Windows/Linux）

要让本地PC电脑作为服务器运行时出现卡顿和资源紧张的情况&#xff0c;可以通过以下几种方式模拟或触发&#xff1a; 1. 增加CPU负载 运行大量计算密集型任务&#xff0c;例如&#xff1a; 使用多线程循环执行复杂计算&#xff08;如数学运算、加密解密等&#xff09;。运行图…...

编程新知 2025/8/22 22:34:43

Java入门学习详细版（一）

大家好&#xff0c;Java 学习是一个系统学习的过程&#xff0c;核心原则就是“理论 实践 坚持”&#xff0c;并且需循序渐进&#xff0c;不可过于着急&#xff0c;本篇文章推出的这份详细入门学习资料将带大家从零基础开始&#xff0c;逐步掌握 Java 的核心概念和编程技能。 …...

编程新知 2025/7/27 0:21:52

大数据学习（132）-HIve数据分析

​​​​&#x1f34b;&#x1f34b;大数据学习&#x1f34b;&#x1f34b; &#x1f525;系列专栏&#xff1a; &#x1f451;哲学语录: 用力所能及&#xff0c;改变世界。 &#x1f496;如果觉得博主的文章还不错的话&#xff0c;请点赞&#x1f44d;收藏⭐️留言&#x1f4…...

编程新知 2025/7/26 19:10:34

腾讯云V3签名

想要接入腾讯云的Api&#xff0c;必然先按其文档计算出所要求的签名。 之前也调用过腾讯云的接口&#xff0c;但总是卡在签名这一步&#xff0c;最后放弃选择SDK&#xff0c;这次终于自己代码实现。 可能腾讯云翻新了接口文档&#xff0c;现在阅读起来&#xff0c;清晰了很多&…...

编程新知 2025/7/5 14:21:20