MySql场景面试题：电商购物中最贵及购买次数最多的商品
❃博主首页 ： 「码到三十五」 ，同名公众号 :「码到三十五」，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 子句将复杂查询分解为多个步骤，进步可读性。
关注公众号[码到三十五]获取更多技术干货 ! https://img-blog.csdnimg.cn/direct/d8a0f829c23843419a500ccf4932b1f3.gif#pic_center
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]