❃博主首页 ： 「码到三十五」 ，同名公众号 :「码到三十五」，wx号 : 「liwu0213」
☠博主专栏 ： <mysql高手> <elasticsearch高手> <源码解读> <java核心> <面试攻关>
♝博主的话 ： 搬的每块砖，皆为峰峦之基；公众号搜索「码到三十五」关注这个爱发技术干货的coder，一起筑基
场景描述
电商系统，数据库中有以下表：
- orders 表：存储订单信息。
- order_items 表：存储订单中的商品信息。
- products 表：存储商品信息。
表结构如下：
- -- 订单表
- CREATE TABLE orders (
- order_id INT AUTO_INCREMENT PRIMARY KEY,
- order_date DATE,
- customer_id INT
- );
- -- 商品表
- CREATE TABLE products (
- product_id INT AUTO_INCREMENT PRIMARY KEY,
- product_name VARCHAR(50),
- category VARCHAR(50),
- price DECIMAL(10, 2)
- );
- -- 订单商品表
- CREATE TABLE order_items (
- order_item_id INT AUTO_INCREMENT PRIMARY KEY,
- order_id INT,
- product_id INT,
- quantity INT,
- FOREIGN KEY (order_id) REFERENCES orders(order_id),
- FOREIGN KEY (product_id) REFERENCES products(product_id)
- );
- -- 插入订单数据
- INSERT INTO orders (order_date, customer_id) VALUES
- ('2023-10-01', 1),
- ('2023-10-02', 2),
- ('2023-10-03', 1),
- ('2023-10-04', 3),
- ('2023-10-05', 2);
- -- 插入商品数据
- INSERT INTO products (product_name, category, price) VALUES
- ('Laptop', 'Electronics', 1200.00),
- ('Smartphone', 'Electronics', 800.00),
- ('Headphones', 'Electronics', 150.00),
- ('Coffee Maker', 'Home Appliances', 100.00),
- ('Blender', 'Home Appliances', 80.00);
- -- 插入订单商品数据
- INSERT INTO order_items (order_id, product_id, quantity) VALUES
- (1, 1, 1), -- Laptop
- (1, 3, 2), -- Headphones
- (2, 2, 1), -- Smartphone
- (3, 4, 1), -- Coffee Maker
- (4, 5, 1), -- Blender
- (5, 1, 1), -- Laptop
- (5, 2, 1); -- Smartphone
面试题
请编写一个 SQL 查询，实现以下需求：
- 统计每个客户的以下信息：
- customer_id
- total_spent（该客户的总消费金额）
- most_expensive_order（该客户最贵的一笔订单的金额）
- favorite_category（该客户购买次数最多的商品类别）
- 只统计消费金额高出 1000 的客户。
- 按 total_spent 从高到低排序。
预期效果
上述数据，查询效果应该类似：
customer_idtotal_spentmost_expensive_orderfavorite_category11700.001500.00Electronics22080.002000.00Electronics
解题思绪
- 计算每个客户的总消费金额：
- 使用 orders 表和 order_items 表关联，计算每个订单的总金额，再按客户分组求和。
- 计算每个客户最贵的一笔订单的金额：
- 使用 MAX() 聚合函数和子查询，计算每个客户最贵的一笔订单的金额。
- 计算每个客户购买次数最多的商品类别：
- 使用 COUNT() 和 GROUP BY 统计每个客户购买的商品类别次数，再使用窗口函数 ROW_NUMBER() 或 RANK() 找到购买次数最多的类别。
- 过滤消费金额高出 1000 的客户：
- 使用 HAVING 条件过滤总消费金额高出 1000 的客户。
SQL 实现
- -- 第一步：计算每个客户的总消费金额和最贵的一笔订单金额
- WITH customer_spending AS (
- SELECT
- o.customer_id,
- SUM(oi.quantity * p.price) AS total_spent,
- MAX(oi.quantity * p.price) AS most_expensive_order
- FROM
- orders o
- JOIN order_items oi ON o.order_id = oi.order_id
- JOIN products p ON oi.product_id = p.product_id
- GROUP BY
- o.customer_id
- HAVING
- total_spent > 1000
- ),
- -- 第二步：计算每个客户购买次数最多的商品类别
- favorite_category AS (
- SELECT
- o.customer_id,
- p.category,
- COUNT(*) AS category_count,
- ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY COUNT(*) DESC) AS category_rank
- FROM
- orders o
- JOIN order_items oi ON o.order_id = oi.order_id
- JOIN products p ON oi.product_id = p.product_id
- GROUP BY
- o.customer_id, p.category
- )
- -- 第三步：整合结果并排序
- SELECT
- cs.customer_id,
- cs.total_spent,
- cs.most_expensive_order,
- fc.category AS favorite_category
- FROM
- customer_spending cs
- JOIN favorite_category fc ON cs.customer_id = fc.customer_id
- WHERE
- fc.category_rank = 1
- ORDER BY
- cs.total_spent DESC;
考察点
- 聚合函数：
- 使用 SUM() 计算总消费金额，使用 MAX() 计算最贵的一笔订单金额，使用 COUNT() 统计购买次数。
- 窗口函数：
- 使用 ROW_NUMBER() 计算每个客户购买次数最多的商品类别。
- 子查询和 CTE（Common Table Expressions）：
- 使用 WITH 子句将复杂查询分解为多个步骤，进步可读性。
关注公众号[码到三十五]获取更多技术干货 !
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
