shell自动换行：关放大阅读展开代码1. 使用docker搭建的hadoop集群 192.168.202.100 hadoop100 宿主机器 192.168.202.162 hadoop162 容器 192.168.202.163 hadoop163 容器 192.168.202.164 hadoop164 容器 执行引擎：hive on spark <!-- more --> 2. 集群启动步骤 2.1 启动宿主机上的docker，已经设置开机自动docker，不用手动开启 2.2 使用hedeoer用户（密码：hedeoer）登录hadoop100机器，并启动三个容器 /home/hedeoer/docker_bigdata/contains.sh start 2.3 使用atguigu用户（密码：aaaaaa）登录hadoop集群的NameNode节点（即hadoop162机器），并登录启动hadoop集群，并执行如下的脚本命令启动hive的hiveserver2和metastore服务 启动hadoop集群: hadoop.sh start 启动hive相关服务：hive2.sh start 2.4 使用外部工具连接hive并练习sql
sql自动换行：关放大阅读展开代码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'; 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'); 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'; 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); DROP TABLE IF EXISTS category_info; 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', '户外'); 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'; 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); 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'; 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); 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'; 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'); 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'; 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'); 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'; 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'); 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'; 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'); 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'; 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');
plaintext自动换行：关放大阅读展开代码-- show databases ; -- -- use hive_no_spark; -- 查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回 select -- nvl(sku_id, null) sku_id coalesce(sku_id, null) from (select sku_id, dense_rank() over (order by sku_amount desc) nums from (SELECT sku_id, sum(sku_num) sku_amount from order_detail t1 group by sku_id) t2) t3 -- left join (select 2 flag) t4 on t3.nums = t4.flag where nums = 2; -- 查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下 select user_id from (select user_id, create_date, date_sub(create_date, row_number() over (partition by user_id order by create_date asc)) num from (select user_id, create_date from order_info t1 group by user_id, create_date) t2) t3 group by user_id, num having count(1) >= 3; -- 从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品 --category_id -- <string> -- (分类id) category_name -- <string> -- (分类名称) sku_id -- <string> -- (销量最好的商品id) name -- <string> -- (商品名称) order_num -- <bigint> -- （销量最好的商品销量） sku_cnt -- <bigint> -- (商品种类数量) -- 品类销售出的商品种类数和销量最好的商品销量 with t1 as (select category_id, sku_id, sku_nums, order_num, sku_amount_nums from (select category_id, sku_id, sku_nums, sku_amount_nums, row_number() over (partition by category_id order by sku_amount_nums desc) `order_num` from (select category_id, sku_id, count(distinct sku_id) over (partition by category_id) sku_nums, sku_amount_nums from (select t2.category_id, t1.sku_id, sum(t1.sku_num) sku_amount_nums from order_detail t1 left join sku_info t2 on t1.sku_id = t2.sku_id group by t2.category_id, t1.sku_id) t3) t4) t5 where `order_num` = 1) select t1.category_id, t2.category_name, t1.sku_id, t3.name sku_num, t1.sku_nums, t1.sku_amount_nums from category_info t2 left join t1 on t1.category_id = t2.category_id left join sku_info t3 on t1.sku_id = t3.sku_id; -- 从订单信息表(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-- <string>-- (用户id) -- create_date-- <string>-- (下单日期) -- sum_so_far-- <decimal(16,2)>-- (截至每个下单日期的累计下单金额) -- vip_level-- <string>-- (每个下单日期的VIP等级) select user_id, create_date, acc_date_amount, CASE WHEN acc_date_amount >= 100000 THEN '钻石会员' WHEN acc_date_amount >= 80000 AND acc_date_amount < 100000 THEN '白金会员' WHEN acc_date_amount >= 50000 AND acc_date_amount < 80000 THEN '黄金会员' WHEN acc_date_amount >= 30000 AND acc_date_amount < 50000 THEN '白银会员' WHEN acc_date_amount >= 10000 AND acc_date_amount < 30000 THEN '青铜会员' WHEN acc_date_amount >= 0 AND acc_date_amount < 10000 THEN '普通会员' ELSE '未知等级' END AS vip_level from ( select user_id, create_date, sum(amount_date) over(order by create_date asc) acc_date_amount from ( select user_id, create_date, sum(coalesce(total_amount,0.0)) amount_date from order_info t1 group by user_id, create_date ) t2 ) t3 ; -- 从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，期望结果如下： -- percentage<string> -- 60.0% select -- user_id, concat(round(count( distinct if(datediff(second_order_date,create_date) = 1, user_id, null)) / count(distinct user_id) * 100 , 1), '%') from ( select user_id, create_date, row_number() over (partition by user_id order by create_date asc) gap_number, lead(create_date,1,'9999-12-31') over(partition by user_id order by create_date asc) second_order_date from ( select user_id, date_format(create_date, 'yyyy-MM-dd') create_date from order_info t1 group by user_id, create_date ) t2 ) t3 where gap_number = 1 -- 从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额 -- sku_id<string>（商品id） -- year-- <bigint>-- （销售首年年份） -- order_num-- <bigint>-- （首年销量） -- order_amount-- <decimal(16,2)>-- （首年销售金额） select sku_id, amount_year, nums_amount, pay_amount from ( select sku_id, amount_year, nums_amount, pay_amount, row_number() over (partition by sku_id order by amount_year asc) order_num from ( select sku_id, year(create_date) amount_year, sum(sku_num) nums_amount, sum(price * sku_num) pay_amount from order_detail t1 group by sku_id, year(create_date) ) t2 ) t3 where order_num = 1;
本文作者:hedeoer
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！