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

    前言

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

    环境准备

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

    1. -- SQL 中级
    2. show databases ;
    3.  
    4. create database practice2;
    5. use practice2;
    6.  
    7. create table user_info(
    8.     `user_id`  string COMMENT '用户id',
    9.     `gender`   string COMMENT '性别',
    10.     `birthday` string COMMENT '生日'
    11. ) COMMENT '用户信息表'
    12.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    13.  
    14. insert overwrite table user_info
    15. values ('101', '男', '1990-01-01'),
    16.        ('102', '女', '1991-02-01'),
    17.        ('103', '女', '1992-03-01'),
    18.        ('104', '男', '1993-04-01'),
    19.        ('105', '女', '1994-05-01'),
    20.        ('106', '男', '1995-06-01'),
    21.        ('107', '女', '1996-07-01'),
    22.        ('108', '男', '1997-08-01'),
    23.        ('109', '女', '1998-09-01'),
    24.        ('1010', '男', '1999-10-01');
    25.  
    26. CREATE TABLE sku_info(
    27.     `sku_id`      string COMMENT '商品id',
    28.     `name`        string COMMENT '商品名称',
    29.     `category_id` string COMMENT '所属分类id',
    30.     `from_date`   string COMMENT '上架日期',
    31.     `price`       double COMMENT '商品单价'
    32. ) COMMENT '商品属性表'
    33.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    34.  
    35. insert overwrite table sku_info
    36. values ('1', 'xiaomi 10', '1', '2020-01-01', 2000),
    37.        ('2', '手机壳', '1', '2020-02-01', 10),
    38.        ('3', 'apple 12', '1', '2020-03-01', 5000),
    39.        ('4', 'xiaomi 13', '1', '2020-04-01', 6000),
    40.        ('5', '破壁机', '2', '2020-01-01', 500),
    41.        ('6', '洗碗机', '2', '2020-02-01', 2000),
    42.        ('7', '热水壶', '2', '2020-03-01', 100),
    43.        ('8', '微波炉', '2', '2020-04-01', 600),
    44.        ('9', '自行车', '3', '2020-01-01', 1000),
    45.        ('10', '帐篷', '3', '2020-02-01', 100),
    46.        ('11', '烧烤架', '3', '2020-02-01', 50),
    47.        ('12', '遮阳伞', '3', '2020-03-01', 20);
    48.  
    49. create table category_info(
    50.     `category_id`   string,
    51.     `category_name` string
    52. ) COMMENT '品类表'
    53.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    54.  
    55. insert overwrite table category_info
    56. values ('1','数码'),
    57.        ('2','厨卫'),
    58.        ('3','户外');
    59.  
    60. create table order_info(
    61.     `order_id`     string COMMENT '订单id',
    62.     `user_id`      string COMMENT '用户id',
    63.     `create_date`  string COMMENT '下单日期',
    64.     `total_amount` decimal(16, 2) COMMENT '订单总金额'
    65. ) COMMENT '订单表'
    66.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    67.  
    68. insert overwrite table order_info
    69. values ('1', '101', '2021-09-27', 29000.00),
    70.        ('2', '101', '2021-09-28', 70500.00),
    71.        ('3', '101', '2021-09-29', 43300.00),
    72.        ('4', '101', '2021-09-30', 860.00),
    73.        ('5', '102', '2021-10-01', 46180.00),
    74.        ('6', '102', '2021-10-01', 50000.00),
    75.        ('7', '102', '2021-10-01', 75500.00),
    76.        ('8', '102', '2021-10-02', 6170.00),
    77.        ('9', '103', '2021-10-02', 18580.00),
    78.        ('10', '103', '2021-10-02', 28000.00),
    79.        ('11', '103', '2021-10-02', 23400.00),
    80.        ('12', '103', '2021-10-03', 5910.00),
    81.        ('13', '104', '2021-10-03', 13000.00),
    82.        ('14', '104', '2021-10-03', 69500.00),
    83.        ('15', '104', '2021-10-03', 2000.00),
    84.        ('16', '104', '2021-10-03', 5380.00),
    85.        ('17', '105', '2021-10-04', 6210.00),
    86.        ('18', '105', '2021-10-04', 68000.00),
    87.        ('19', '105', '2021-10-04', 43100.00),
    88.        ('20', '105', '2021-10-04', 2790.00),
    89.        ('21', '106', '2021-10-04', 9390.00),
    90.        ('22', '106', '2021-10-05', 58000.00),
    91.        ('23', '106', '2021-10-05', 46600.00),
    92.        ('24', '106', '2021-10-05', 5160.00),
    93.        ('25', '107', '2021-10-05', 55350.00),
    94.        ('26', '107', '2021-10-05', 14500.00),
    95.        ('27', '107', '2021-10-06', 47400.00),
    96.        ('28', '107', '2021-10-06', 6900.00),
    97.        ('29', '108', '2021-10-06', 56570.00),
    98.        ('30', '108', '2021-10-06', 44500.00),
    99.        ('31', '108', '2021-10-07', 50800.00),
    100.        ('32', '108', '2021-10-07', 3900.00),
    101.        ('33', '109', '2021-10-07', 41480.00),
    102.        ('34', '109', '2021-10-07', 88000.00),
    103.        ('35', '109', '2020-10-08', 15000.00),
    104.        ('36', '109', '2020-10-08', 9020.00),
    105.        ('37', '1010', '2020-10-08', 9260.00),
    106.        ('38', '1010', '2020-10-08', 12000.00),
    107.        ('39', '1010', '2020-10-08', 23900.00),
    108.        ('40', '1010', '2020-10-08', 6790.00);
    109.  
    110. CREATE TABLE order_detail
    111. (
    112.     `order_detail_id` string COMMENT '订单明细id',
    113.     `order_id`        string COMMENT '订单id',
    114.     `sku_id`          string COMMENT '商品id',
    115.     `create_date`     string COMMENT '下单日期',
    116.     `price`           decimal(16, 2) COMMENT '下单时的商品单价',
    117.     `sku_num`         int COMMENT '下单商品件数'
    118. ) COMMENT '订单明细表'
    119.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    120.  
    121. INSERT overwrite table order_detail
    122. values ('1', '1', '1', '2021-09-27', 2000.00, 2),
    123.        ('2', '1', '3', '2021-09-27', 5000.00, 5),
    124.        ('3', '2', '4', '2021-09-28', 6000.00, 9),
    125.        ('4', '2', '5', '2021-09-28', 500.00, 33),
    126.        ('5', '3', '7', '2021-09-29', 100.00, 37),
    127.        ('6', '3', '8', '2021-09-29', 600.00, 46),
    128.        ('7', '3', '9', '2021-09-29', 1000.00, 12),
    129.        ('8', '4', '12', '2021-09-30', 20.00, 43),
    130.        ('9', '5', '1', '2021-10-01', 2000.00, 8),
    131.        ('10', '5', '2', '2021-10-01', 10.00, 18),
    132.        ('11', '5', '3', '2021-10-01', 5000.00, 6),
    133.        ('12', '6', '4', '2021-10-01', 6000.00, 8),
    134.        ('13', '6', '6', '2021-10-01', 2000.00, 1),
    135.        ('14', '7', '7', '2021-10-01', 100.00, 17),
    136.        ('15', '7', '8', '2021-10-01', 600.00, 48),
    137.        ('16', '7', '9', '2021-10-01', 1000.00, 45),
    138.        ('17', '8', '10', '2021-10-02', 100.00, 48),
    139.        ('18', '8', '11', '2021-10-02', 50.00, 15),
    140.        ('19', '8', '12', '2021-10-02', 20.00, 31),
    141.        ('20', '9', '1', '2021-09-30', 2000.00, 9),
    142.        ('21', '9', '2', '2021-10-02', 10.00, 5800),
    143.        ('22', '10', '4', '2021-10-02', 6000.00, 1),
    144.        ('23', '10', '5', '2021-10-02', 500.00, 24),
    145.        ('24', '10', '6', '2021-10-02', 2000.00, 5),
    146.        ('25', '11', '8', '2021-10-02', 600.00, 39),
    147.        ('26', '12', '10', '2021-10-03', 100.00, 47),
    148.        ('27', '12', '11', '2021-10-03', 50.00, 19),
    149.        ('28', '12', '12', '2021-10-03', 20.00, 13000),
    150.        ('29', '13', '1', '2021-10-03', 2000.00, 4),
    151.        ('30', '13', '3', '2021-10-03', 5000.00, 1),
    152.        ('31', '14', '4', '2021-10-03', 6000.00, 5),
    153.        ('32', '14', '5', '2021-10-03', 500.00, 47),
    154.        ('33', '14', '6', '2021-10-03', 2000.00, 8),
    155.        ('34', '15', '7', '2021-10-03', 100.00, 20),
    156.        ('35', '16', '10', '2021-10-03', 100.00, 22),
    157.        ('36', '16', '11', '2021-10-03', 50.00, 42),
    158.        ('37', '16', '12', '2021-10-03', 20.00, 7400),
    159.        ('38', '17', '1', '2021-10-04', 2000.00, 3),
    160.        ('39', '17', '2', '2021-10-04', 10.00, 21),
    161.        ('40', '18', '4', '2021-10-04', 6000.00, 8),
    162.        ('41', '18', '5', '2021-10-04', 500.00, 28),
    163.        ('42', '18', '6', '2021-10-04', 2000.00, 3),
    164.        ('43', '19', '7', '2021-10-04', 100.00, 55),
    165.        ('44', '19', '8', '2021-10-04', 600.00, 11),
    166.        ('45', '19', '9', '2021-10-04', 1000.00, 31),
    167.        ('46', '20', '11', '2021-10-04', 50.00, 45),
    168.        ('47', '20', '12', '2021-10-04', 20.00, 27),
    169.        ('48', '21', '1', '2021-10-04', 2000.00, 2),
    170.        ('49', '21', '2', '2021-10-04', 10.00, 39),
    171.        ('50', '21', '3', '2021-10-04', 5000.00, 1),
    172.        ('51', '22', '4', '2021-10-05', 6000.00, 8),
    173.        ('52', '22', '5', '2021-10-05', 500.00, 20),
    174.        ('53', '23', '7', '2021-10-05', 100.00, 58),
    175.        ('54', '23', '8', '2021-10-05', 600.00, 18),
    176.        ('55', '23', '9', '2021-10-05', 1000.00, 30),
    177.        ('56', '24', '10', '2021-10-05', 100.00, 27),
    178.        ('57', '24', '11', '2021-10-05', 50.00, 28),
    179.        ('58', '24', '12', '2021-10-05', 20.00, 53),
    180.        ('59', '25', '1', '2021-10-05', 2000.00, 5),
    181.        ('60', '25', '2', '2021-10-05', 10.00, 35),
    182.        ('61', '25', '3', '2021-10-05', 5000.00, 9),
    183.        ('62', '26', '4', '2021-10-05', 6000.00, 1),
    184.        ('63', '26', '5', '2021-10-05', 500.00, 13),
    185.        ('64', '26', '6', '2021-10-05', 2000.00, 1),
    186.        ('65', '27', '7', '2021-10-06', 100.00, 30),
    187.        ('66', '27', '8', '2021-10-06', 600.00, 19),
    188.        ('67', '27', '9', '2021-10-06', 1000.00, 33),
    189.        ('68', '28', '10', '2021-10-06', 100.00, 37),
    190.        ('69', '28', '11', '2021-10-06', 50.00, 46),
    191.        ('70', '28', '12', '2021-10-06', 20.00, 45),
    192.        ('71', '29', '1', '2021-10-06', 2000.00, 8),
    193.        ('72', '29', '2', '2021-10-06', 10.00, 57),
    194.        ('73', '29', '3', '2021-10-06', 5000.00, 8),
    195.        ('74', '30', '4', '2021-10-06', 6000.00, 3),
    196.        ('75', '30', '5', '2021-10-06', 500.00, 33),
    197.        ('76', '30', '6', '2021-10-06', 2000.00, 5),
    198.        ('77', '31', '8', '2021-10-07', 600.00, 13),
    199.        ('78', '31', '9', '2021-10-07', 1000.00, 43),
    200.        ('79', '32', '10', '2021-10-07', 100.00, 24),
    201.        ('80', '32', '11', '2021-10-07', 50.00, 30),
    202.        ('81', '33', '1', '2021-10-07', 2000.00, 8),
    203.        ('82', '33', '2', '2021-10-07', 10.00, 48),
    204.        ('83', '33', '3', '2021-10-07', 5000.00, 5),
    205.        ('84', '34', '4', '2021-10-07', 6000.00, 10),
    206.        ('85', '34', '5', '2021-10-07', 500.00, 44),
    207.        ('86', '34', '6', '2021-10-07', 2000.00, 3),
    208.        ('87', '35', '8', '2020-10-08', 600.00, 25),
    209.        ('88', '36', '10', '2020-10-08', 100.00, 57),
    210.        ('89', '36', '11', '2020-10-08', 50.00, 44),
    211.        ('90', '36', '12', '2020-10-08', 20.00, 56),
    212.        ('91', '37', '1', '2020-10-08', 2000.00, 2),
    213.        ('92', '37', '2', '2020-10-08', 10.00, 26),
    214.        ('93', '37', '3', '2020-10-08', 5000.00, 1),
    215.        ('94', '38', '6', '2020-10-08', 2000.00, 6),
    216.        ('95', '39', '7', '2020-10-08', 100.00, 35),
    217.        ('96', '39', '8', '2020-10-08', 600.00, 34),
    218.        ('97', '40', '10', '2020-10-08', 100.00, 37),
    219.        ('98', '40', '11', '2020-10-08', 50.00, 51),
    220.        ('99', '40', '12', '2020-10-08', 20.00, 27);
    221.  
    222. CREATE TABLE user_login_detail
    223. (
    224.     `user_id`    string comment '用户id',
    225.     `ip_address` string comment 'ip地址',
    226.     `login_ts`   string comment '登录时间',
    227.     `logout_ts`  string comment '登出时间'
    228. ) COMMENT '用户登录明细表'
    229.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    230.  
    231. INSERT overwrite table user_login_detail
    232. VALUES ('101', '180.149.130.161', '2021-09-21 08:00:00', '2021-09-27 08:30:00'),
    233.        ('101', '180.149.130.161', '2021-09-27 08:00:00', '2021-09-27 08:30:00'),
    234.        ('101', '180.149.130.161', '2021-09-28 09:00:00', '2021-09-28 09:10:00'),
    235.        ('101', '180.149.130.161', '2021-09-29 13:30:00', '2021-09-29 13:50:00'),
    236.        ('101', '180.149.130.161', '2021-09-30 20:00:00', '2021-09-30 20:10:00'),
    237.        ('102', '120.245.11.2', '2021-09-22 09:00:00', '2021-09-27 09:30:00'),
    238.        ('102', '120.245.11.2', '2021-10-01 08:00:00', '2021-10-01 08:30:00'),
    239.        ('102', '180.149.130.174', '2021-10-01 07:50:00', '2021-10-01 08:20:00'),
    240.        ('102', '120.245.11.2', '2021-10-02 08:00:00', '2021-10-02 08:30:00'),
    241.        ('103', '27.184.97.3', '2021-09-23 10:00:00', '2021-09-27 10:30:00'),
    242.        ('103', '27.184.97.3', '2021-10-03 07:50:00', '2021-10-03 09:20:00'),
    243.        ('104', '27.184.97.34', '2021-09-24 11:00:00', '2021-09-27 11:30:00'),
    244.        ('104', '27.184.97.34', '2021-10-03 07:50:00', '2021-10-03 08:20:00'),
    245.        ('104', '27.184.97.34', '2021-10-03 08:50:00', '2021-10-03 10:20:00'),
    246.        ('104', '120.245.11.89', '2021-10-03 08:40:00', '2021-10-03 10:30:00'),
    247.        ('105', '119.180.192.212', '2021-10-04 09:10:00', '2021-10-04 09:30:00'),
    248.        ('106', '119.180.192.66', '2021-10-04 08:40:00', '2021-10-04 10:30:00'),
    249.        ('106', '119.180.192.66', '2021-10-05 21:50:00', '2021-10-05 22:40:00'),
    250.        ('107', '219.134.104.7', '2021-09-25 12:00:00', '2021-09-27 12:30:00'),
    251.        ('107', '219.134.104.7', '2021-10-05 22:00:00', '2021-10-05 23:00:00'),
    252.        ('107', '219.134.104.7', '2021-10-06 09:10:00', '2021-10-06 10:20:00'),
    253.        ('107', '27.184.97.46', '2021-10-06 09:00:00', '2021-10-06 10:00:00'),
    254.        ('108', '101.227.131.22', '2021-10-06 09:00:00', '2021-10-06 10:00:00'),
    255.        ('108', '101.227.131.22', '2021-10-06 22:00:00', '2021-10-06 23:00:00'),
    256.        ('109', '101.227.131.29', '2021-09-26 13:00:00', '2021-09-27 13:30:00'),
    257.        ('109', '101.227.131.29', '2021-10-06 08:50:00', '2021-10-06 10:20:00'),
    258.        ('109', '101.227.131.29', '2021-10-08 09:00:00', '2021-10-08 09:10:00'),
    259.        ('1010', '119.180.192.10', '2021-09-27 14:00:00', '2021-09-27 14:30:00'),
    260.        ('1010', '119.180.192.10', '2021-10-09 08:50:00', '2021-10-09 10:20:00');
    261.  
    262. CREATE TABLE sku_price_modify_detail
    263. (
    264.     `sku_id`      string comment '商品id',
    265.     `new_price`   decimal(16, 2) comment '更改后的价格',
    266.     `change_date` string comment '变动日期'
    267. ) COMMENT '商品价格变更明细表'
    268.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    269.  
    270. insert overwrite table sku_price_modify_detail
    271. values ('1', 1900, '2021-09-25'),
    272.        ('1', 2000, '2021-09-26'),
    273.        ('2', 80, '2021-09-29'),
    274.        ('2', 10, '2021-09-30'),
    275.        ('3', 4999, '2021-09-25'),
    276.        ('3', 5000, '2021-09-26'),
    277.        ('4', 5600, '2021-09-26'),
    278.        ('4', 6000, '2021-09-27'),
    279.        ('5', 490, '2021-09-27'),
    280.        ('5', 500, '2021-09-28'),
    281.        ('6', 1988, '2021-09-30'),
    282.        ('6', 2000, '2021-10-01'),
    283.        ('7', 88, '2021-09-28'),
    284.        ('7', 100, '2021-09-29'),
    285.        ('8', 800, '2021-09-28'),
    286.        ('8', 600, '2021-09-29'),
    287.        ('9', 1100, '2021-09-27'),
    288.        ('9', 1000, '2021-09-28'),
    289.        ('10', 90, '2021-10-01'),
    290.        ('10', 100, '2021-10-02'),
    291.        ('11', 66, '2021-10-01'),
    292.        ('11', 50, '2021-10-02'),
    293.        ('12', 35, '2021-09-28'),
    294.        ('12', 20, '2021-09-29');
    295.  
    296. CREATE TABLE delivery_info
    297. (
    298.     `delivery_id` string comment '配送单id',
    299.     `order_id`    string comment '订单id',
    300.     `user_id`     string comment '用户id',
    301.     `order_date`  string comment '下单日期',
    302.     `custom_date` string comment '期望配送日期'
    303. ) COMMENT '邮寄信息表'
    304.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    305.  
    306. insert overwrite table delivery_info
    307. values ('1', '1', '101', '2021-09-27', '2021-09-29'),
    308.        ('2', '2', '101', '2021-09-28', '2021-09-28'),
    309.        ('3', '3', '101', '2021-09-29', '2021-09-30'),
    310.        ('4', '4', '101', '2021-09-30', '2021-10-01'),
    311.        ('5', '5', '102', '2021-10-01', '2021-10-01'),
    312.        ('6', '6', '102', '2021-10-01', '2021-10-01'),
    313.        ('7', '7', '102', '2021-10-01', '2021-10-03'),
    314.        ('8', '8', '102', '2021-10-02', '2021-10-02'),
    315.        ('9', '9', '103', '2021-10-02', '2021-10-03'),
    316.        ('10', '10', '103', '2021-10-02', '2021-10-04'),
    317.        ('11', '11', '103', '2021-10-02', '2021-10-02'),
    318.        ('12', '12', '103', '2021-10-03', '2021-10-03'),
    319.        ('13', '13', '104', '2021-10-03', '2021-10-04'),
    320.        ('14', '14', '104', '2021-10-03', '2021-10-04'),
    321.        ('15', '15', '104', '2021-10-03', '2021-10-03'),
    322.        ('16', '16', '104', '2021-10-03', '2021-10-03'),
    323.        ('17', '17', '105', '2021-10-04', '2021-10-04'),
    324.        ('18', '18', '105', '2021-10-04', '2021-10-06'),
    325.        ('19', '19', '105', '2021-10-04', '2021-10-06'),
    326.        ('20', '20', '105', '2021-10-04', '2021-10-04'),
    327.        ('21', '21', '106', '2021-10-04', '2021-10-04'),
    328.        ('22', '22', '106', '2021-10-05', '2021-10-05'),
    329.        ('23', '23', '106', '2021-10-05', '2021-10-05'),
    330.        ('24', '24', '106', '2021-10-05', '2021-10-07'),
    331.        ('25', '25', '107', '2021-10-05', '2021-10-05'),
    332.        ('26', '26', '107', '2021-10-05', '2021-10-06'),
    333.        ('27', '27', '107', '2021-10-06', '2021-10-06'),
    334.        ('28', '28', '107', '2021-10-06', '2021-10-07'),
    335.        ('29', '29', '108', '2021-10-06', '2021-10-06'),
    336.        ('30', '30', '108', '2021-10-06', '2021-10-06'),
    337.        ('31', '31', '108', '2021-10-07', '2021-10-09'),
    338.        ('32', '32', '108', '2021-10-07', '2021-10-09'),
    339.        ('33', '33', '109', '2021-10-07', '2021-10-08'),
    340.        ('34', '34', '109', '2021-10-07', '2021-10-08'),
    341.        ('35', '35', '109', '2021-10-08', '2021-10-10'),
    342.        ('36', '36', '109', '2021-10-08', '2021-10-09'),
    343.        ('37', '37', '1010', '2021-10-08', '2021-10-10'),
    344.        ('38', '38', '1010', '2021-10-08', '2021-10-10'),
    345.        ('39', '39', '1010', '2021-10-08', '2021-10-09'),
    346.        ('40', '40', '1010', '2021-10-08', '2021-10-09');
    347.  
    348. CREATE TABLE friendship_info(
    349.     `user1_id` string comment '用户1id',
    350.     `user2_id` string comment '用户2id'
    351. ) COMMENT '用户关系表'
    352.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    353.  
    354. insert overwrite table friendship_info
    355. values ('101', '1010'),
    356.        ('101', '108'),
    357.        ('101', '106'),
    358.        ('101', '104'),
    359.        ('101', '102'),
    360.        ('102', '1010'),
    361.        ('102', '108'),
    362.        ('102', '106'),
    363.        ('102', '104'),
    364.        ('102', '102'),
    365.        ('103', '1010'),
    366.        ('103', '108'),
    367.        ('103', '106'),
    368.        ('103', '104'),
    369.        ('103', '102'),
    370.        ('104', '1010'),
    371.        ('104', '108'),
    372.        ('104', '106'),
    373.        ('104', '104'),
    374.        ('104', '102'),
    375.        ('105', '1010'),
    376.        ('105', '108'),
    377.        ('105', '106'),
    378.        ('105', '104'),
    379.        ('105', '102'),
    380.        ('106', '1010'),
    381.        ('106', '108'),
    382.        ('106', '106'),
    383.        ('106', '104'),
    384.        ('106', '102'),
    385.        ('107', '1010'),
    386.        ('107', '108'),
    387.        ('107', '106'),
    388.        ('107', '104'),
    389.        ('107', '102'),
    390.        ('108', '1010'),
    391.        ('108', '108'),
    392.        ('108', '106'),
    393.        ('108', '104'),
    394.        ('108', '102'),
    395.        ('109', '1010'),
    396.        ('109', '108'),
    397.        ('109', '106'),
    398.        ('109', '104'),
    399.        ('109', '102'),
    400.        ('1010', '1010'),
    401.        ('1010', '108'),
    402.        ('1010', '106'),
    403.        ('1010', '104'),
    404.        ('1010', '102');
    405.  
    406. CREATE TABLE favor_info
    407. (
    408.     `user_id`     string comment '用户id',
    409.     `sku_id`      string comment '商品id',
    410.     `create_date` string comment '收藏日期'
    411. ) COMMENT '用户收藏表'
    412.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    413.  
    414. insert overwrite table favor_info
    415. values ('101', '3', '2021-09-23'),
    416.        ('101', '12', '2021-09-23'),
    417.        ('101', '6', '2021-09-25'),
    418.        ('101', '10', '2021-09-21'),
    419.        ('101', '5', '2021-09-25'),
    420.        ('102', '1', '2021-09-24'),
    421.        ('102', '2', '2021-09-24'),
    422.        ('102', '8', '2021-09-23'),
    423.        ('102', '12', '2021-09-22'),
    424.        ('102', '11', '2021-09-23'),
    425.        ('102', '9', '2021-09-25'),
    426.        ('102', '4', '2021-09-25'),
    427.        ('102', '6', '2021-09-23'),
    428.        ('102', '7', '2021-09-26'),
    429.        ('103', '8', '2021-09-24'),
    430.        ('103', '5', '2021-09-25'),
    431.        ('103', '6', '2021-09-26'),
    432.        ('103', '12', '2021-09-27'),
    433.        ('103', '7', '2021-09-25'),
    434.        ('103', '10', '2021-09-25'),
    435.        ('103', '4', '2021-09-24'),
    436.        ('103', '11', '2021-09-25'),
    437.        ('103', '3', '2021-09-27'),
    438.        ('104', '9', '2021-09-28'),
    439.        ('104', '7', '2021-09-28'),
    440.        ('104', '8', '2021-09-25'),
    441.        ('104', '3', '2021-09-28'),
    442.        ('104', '11', '2021-09-25'),
    443.        ('104', '6', '2021-09-25'),
    444.        ('104', '12', '2021-09-28'),
    445.        ('105', '8', '2021-10-08'),
    446.        ('105', '9', '2021-10-07'),
    447.        ('105', '7', '2021-10-07'),
    448.        ('105', '11', '2021-10-06'),
    449.        ('105', '5', '2021-10-07'),
    450.        ('105', '4', '2021-10-05'),
    451.        ('105', '10', '2021-10-07'),
    452.        ('106', '12', '2021-10-08'),
    453.        ('106', '1', '2021-10-08'),
    454.        ('106', '4', '2021-10-04'),
    455.        ('106', '5', '2021-10-08'),
    456.        ('106', '2', '2021-10-04'),
    457.        ('106', '6', '2021-10-04'),
    458.        ('106', '7', '2021-10-08'),
    459.        ('107', '5', '2021-09-29'),
    460.        ('107', '3', '2021-09-28'),
    461.        ('107', '10', '2021-09-27'),
    462.        ('108', '9', '2021-10-08'),
    463.        ('108', '3', '2021-10-10'),
    464.        ('108', '8', '2021-10-10'),
    465.        ('108', '10', '2021-10-07'),
    466.        ('108', '11', '2021-10-07'),
    467.        ('109', '2', '2021-09-27'),
    468.        ('109', '4', '2021-09-29'),
    469.        ('109', '5', '2021-09-29'),
    470.        ('109', '9', '2021-09-30'),
    471.        ('109', '8', '2021-09-26'),
    472.        ('1010', '2', '2021-09-29'),
    473.        ('1010', '9', '2021-09-29'),
    474.        ('1010', '1', '2021-10-01');

    题目

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

    题目需求

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

    代码

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

    这里不需要分区。

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

    1. select sku_id from (
    2.     select sku_id,
    3.            order_num,
    4.            dense_rank() over (order by order_num desc) rank
    5.     from (
    6.         select sku_id,
    7.                sum(sku_num) order_num
    8.             from order_detail
    9.             group by sku_id
    10.             )t1
    11.         )
    12. where rank=2;

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

    题目需求

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

    代码

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

    1. -- 找到用户的下单日期-需要去重(一天下单多次)
    2. select distinct user_id,
    3.                 create_date
    4. from order_info;
    5.  
    6. -- 第二种去重方式
    7. select user_id, 
    8.        create_date
    9. from order_info
    10. group by user_id,create_date;    --group by 就是把相同的字段值放到一起
    11.  
    12. --第三种去重方式
    13. select user_id,create_date
    14. from(
    15. select user_id,
    16.        create_date,
    17.        row_number() over (partition by user_id,create_date) rn
    18. from order_info
    19.     )t1
    20. where rn=1;
    查看SQL语句执行计划
    1. explain select distinct user_id,
    2.                 create_date
    3. from order_info;

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

    思路1：

    1. user_id      create_date      lead(create_date,2,'9999-12-31')      datadiff
    2. 1001          2023-06-01            2023-06-03                      2
    3. 1001          2023-06-02            2023-06-06                      4
    4. 1001          2023-06-03            2023-06-07                      4
    5. 1001          2023-06-06            2023-06-08                      2
    6. 1001          2023-06-07            9999-12-31                      2913****
    7. 1001          2023-06-08            9999-12-31                      2913****
    1. select distinct user_id
    2. from(
    3.     select user_id,
    4.        datediff(create_date,lead2) diff
    5.     from(
    6.         select user_id,
    7.                create_date,
    8.                lead(create_date,2,'9999-12-31') over (partition by user_id order by create_date) lead2
    9.                from
    10.         (
    11.         select distinct user_id,
    12.                         create_date
    13.         from order_info)t1
    14.         )t2
    15.     )t3
    16. where diff=2;

    思路2

    1. user_id      create_date      rank()    diff        
    2. 1001          2023-06-02      1         2023-06-01
    3. 1001          2023-06-03      2         2023-06-01
    4. 1001          2023-06-04      3         2023-06-01
    5. 1001          2023-06-06      4         2023-06-02
    6. 1001          2023-06-07      5         2023-06-02
    7. 1001          2023-06-08      6         2023-06-02
    1. select distinct user_id
    2. from(
    3.     select user_id,
    4.        diff,
    5.        count(*) cnt
    6.     from (
    7.         select user_id,
    8.                        create_date,
    9.                        date_sub(create_date, rank() over (partition by user_id order by create_date)) diff
    10.                 from (
    11.                          select distinct user_id,
    12.                                          create_date
    13.                          from order_info
    14.                      ) t1
    15.             )t2
    16. group by user_id,diff
    17. having cnt
    18.         )t3;

    思路3

    1. user_id      create_date      count() over(partition by user_id order by create_date range between 86400 preceding and 86400 following )
    2. 1001          2023-06-02            2
    3. 1001          2023-06-03            3
    4. 1001          2023-06-04            2
    5. 1001          2023-06-06            2
    6. 1001          2023-06-07            3
    7. 1001          2023-06-08            2
    1. select distinct user_id
    2. from (
    3.          select user_id,
    4.                 ts,
    5.                 count(*) over (partition by user_id order by ts range between 86400 preceding and 86400 following) cnt
    6.          from (
    7.                   select user_id,
    8.                          unix_timestamp(create_date, 'yyyy-MM-dd') ts
    9.                   from (
    10.                            select distinct user_id,
    11.                                            create_date
    12.                            from order_info
    13.                        ) t1
    14.               ) t2
    15.      )t3
    16. 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

    1. select t1.category_id,
    2.        category_name,
    3.        sku_id,
    4.        name,
    5.        order_num,
    6.        order_cnt
    7. from(
    8. -- 查询每件商品的下单数量和商品类别
    9.     select od.sku_id,
    10.            order_num,
    11.            category_name,
    12.            ci.category_id,
    13.            si.name,
    14.            rank() over (partition by ci.category_id order by order_num desc) rk,
    15.            count(distinct od.sku_id) over (partition by ci.category_id) order_cnt
    16.     from(
    17.     select sku_id,
    18.            sum(sku_num) order_num
    19.     from order_detail
    20.     group by sku_id
    21.         ) od
    22.         left join sku_info si
    23.         on od.sku_id=si.sku_id
    24.             left join category_info ci
    25.             on si.category_id=ci.category_id)t1
    26. where rk=1;

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

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

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

    设累积消费总额为X，

    若0=

    若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、先查询用户每天的下单金额

    1. select user_id,
    2.             create_date,
    3.             sum(total_amount) total_amount_per_day
    4.     from order_info
    5.     group by user_id, create_date;

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

    1. select user_id,
    2.                 create_date,
    3.                 sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far
    4.          from (
    5.                   select user_id,
    6.                          create_date,
    7.                          sum(total_amount) total_amount_per_day
    8.                   from order_info
    9.                   group by user_id, create_date
    10.               )t1
    11.      );

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

    1. select user_id,
    2.        create_date,
    3.        sum_so_far,
    4.        case
    5.            when sum_so_far >= 100000 then '钻石会员'
    6.            when sum_so_far >= 80000 then '白金会员'
    7.            when sum_so_far >= 50000 then '黄金会员'
    8.            when sum_so_far >= 30000 then '白银会员'
    9.            when sum_so_far >= 10000 then '青铜会员'
    10.            when sum_so_far >= 0 then '普通会员'
    11.            end vip_level
    12. from (
    13.          select user_id,
    14.                 create_date,
    15.                 sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far
    16.          from (
    17.                   select user_id,
    18.                          create_date,
    19.                          sum(total_amount) total_amount_per_day
    20.                   from order_info
    21.                   group by user_id, create_date
    22.               )t1
    23.      )t2;

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

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

    percentage

    70.0%

    1. - 查询首次下单后第二天连续下单的用户比率
    2. -- 根据用户下单日期排序并排名(为的是 create_date-rk 结果相同的话,说明用户连续下单)
    3. -- create_date rk date_sub()
    4. -- 2020-06-02  1   2020-06-01
    5. -- 2020-06-03  2   2020-06-01
    6. -- 2020-06-04  3   2020-06-01
    7.  
    8. select concat(round(sum(if(datediff(buy_date_second, buy_date_first) = 1, 1, 0)) / count(*) * 100, 1), '%') percentage
    9. from (
    10.          select user_id,
    11.                 min(create_date) buy_date_first,
    12.                 max(create_date) buy_date_second
    13.          from (
    14.                   select user_id,
    15.                          create_date,
    16.                          rank() over (partition by user_id order by create_date) rk
    17.                   from (
    18.                            select user_id,
    19.                                   create_date
    20.                            from order_info
    21.                            group by user_id, create_date
    22.                        ) t1
    23.               ) t2
    24.          where rk <= 2
    25.          group by user_id
    26.      ) t3;

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

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

    期望结果如下：

    sku_id

    （商品id）

    year

    （销售首年年份）

    order_num

    （首年销量）

    order_amount

    （首年销售金额）

    1

    2021

    51

    102000.00

    2

    2021

    302

    3020.00

    3

    2021

    36

    180000.00

    4

    2021

    53

    318000.00

    5

    2021

    242

    121000.00

    6

    2021

    32

    64000.00

    7

    2021

    252

    25200.00

    8

    2021

    253

    151800.00

    9

    2021

    194

    194000.00

    10

    2021

    299

    29900.00

    11

    2021

    320

    16000.00

    12

    2021

    349

    6980.00

    1. -- 同一年被划分为同一排名
    2. select sku_id,
    3.        sale_year,
    4.        sum(sku_num) order_num,
    5.        sum(price*sku_num) order_amount
    6. from (
    7.          select sku_id,
    8.                 price,
    9.                 sku_num,
    10.                 year(create_date) sale_year,
    11.                 rank() over (partition by sku_id order by year(create_date)) rk
    12.          from order_detail
    13.      )t1
    14. where rk=1
    15. group by sku_id, sale_year
    16. order by sku_id;

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

    1. select t1.sku_id,
    2.        name,
    3.        order_num
    4. from(
    5.     -- 查询每件商品2021年的总销量
    6.     select sku_id,
    7.            sum(sku_num) order_num
    8.     from order_detail
    9.     where year(create_date)='2021'
    10.     group by sku_id
    11.     having order_num<100)t1
    12. left join sku_info si
    13. on si.sku_id=t1.sku_id;

  • 相关阅读:
    2022年8月22日，ue4热更新视频教程
    E (1081) : DS堆栈--逆序输出（STL栈使用）
    VVC 预测值写入TXT文件（xCheckModeSplit()函数未写完）
    C++程序设计第十章课后习题答案——运算符重载练习
    Ubuntu 20.04 LTS配置JDK、Git
    linux golang安装
    macOS安装brew和使用brew
    seata-server-1.5.2的部署
    STM32F1与STM32CubeIDE编程实例-金属触摸传感器驱动
    QT编程：QT int和float转QString
  • 原文地址：https://blog.csdn.net/m0_64261982/article/details/133898210