MySql场景面试题：电商购物中最贵及购买次数最多的商品

公众号:码到三十五

发布于 2025-01-16 00:10:17

15400

代码可运行

文章被收录于专栏：设计模式设计模式

运行总次数：0

代码可运行

场景描述

电商系统，数据库中有以下表：

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_id	total_spent	most_expensive_order	favorite_category
1	1700.00	1500.00	Electronics
2	2080.00	2000.00	Electronics

解题思路

计算每个客户的总消费金额：
- 使用 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;