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

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

场景描述

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

1. orders 表：存储订单信息。
2. order_items 表：存储订单中的商品信息。
3. products 表：存储商品信息。

表结构如下：

-- 订单表
CREATE TABLE orders (
    order_id IT AUTO_ICREMET PRIMARY KEY,
    order_date DATE,
    customer_id IT
);

-- 商品表
CREATE TABLE products (
    product_id IT AUTO_ICREMET PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

-- 订单商品表
CREATE TABLE order_items (
    order_item_id IT AUTO_ICREMET PRIMARY KEY,
    order_id IT,
    product_id IT,
    quantity IT,
    FOREIG KEY (order_id) REFERECES orders(order_id),
    FOREIG KEY (product_id) REFERECES products(product_id)
);

插入测试数据：

-- 插入订单数据
ISERT ITO orders (order_date, customer_id) VALUES
('202-10-01', 1),
('202-10-02', 2),
('202-10-0', 1),
('202-10-04', ),
('202-10-05', 2);

-- 插入商品数据
ISERT ITO 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);

-- 插入订单商品数据
ISERT ITO order_items (order_id, product_id, quantity) VALUES
(1, 1, 1), -- Laptop
(1, , 2), -- Headphones
(2, 2, 1), -- Smartphone
(, 4, 1), -- Coffee Maker
(4, 5, 1), -- Blender
(5, 1, 1), -- Laptop
(5, 2, 1); -- Smartphone

面试题

请编写一个 SQL 查询，实现以下需求：

1. 统计每个客户的以下信息：
   • customer_id
   • total_spent（该客户的总消费金额）
   • most_expensive_order（该客户最贵的一笔订单的金额）
   • favorite_category（该客户购买次数最多的商品类别）
2. 只统计消费金额超过 1000 的客户。
3. 按 total_spent 从高到低排序。

预期结果

上述数据，查询结果应该类似：

1. 计算每个客户的总消费金额：
   • 使用 orders 表和 order_items 表关联，计算每个订单的总金额，再按客户分组求和。
2. 计算每个客户最贵的一笔订单的金额：
   • 使用 MAX() 聚合函数和子查询，计算每个客户最贵的一笔订单的金额。
3. 计算每个客户购买次数最多的商品类别：
   • 使用 COUT() 和 GROUP BY 统计每个客户购买的商品类别次数，再使用窗口函数 ROW_UMBER() 或 RAK() 到购买次数最多的类别。
4. 过滤消费金额超过 1000 的客户：
   • 使用 HAVIG 条件过滤总消费金额超过 1000 的客户。

SQL 实现

-- 第一步：计算每个客户的总消费金额和最贵的一笔订单金额
WITH customer_spending AS (
    SELECT
        _id,
        SUM(oi.quantity * p.price) AS total_spent,
        MAX(oi.quantity * p.price) AS most_expensive_order
    FROM
        orders o
    JOI order_items oi O _id = _id
    JOI products p O oi.product_id = p.product_id
    GROUP BY
        _id
    HAVIG
        total_spent > 1000
),

-- 第二步：计算每个客户购买次数最多的商品类别
favorite_category AS (
    SELECT
        _id,
        ,
        COUT(*) AS category_count,
        ROW_UMBER() OVER (PARTITIO BY _id ORDER BY COUT(*) DESC) AS category_rank
    FROM
        orders o
    JOI order_items oi O _id = _id
    JOI products p O oi.product_id = p.product_id
    GROUP BY
        _id, 
)

-- 第三步：整合结果并排序
SELECT
    _id,
    _spent,
    _expensive_order,
     AS favorite_category
FROM
    customer_spending cs
JOI favorite_category fc O _id = _id
WHERE
    _rank = 1
ORDER BY
    _spent DESC;

考察点

1. 聚合函数：
   • 使用 SUM() 计算总消费金额，使用 MAX() 计算最贵的一笔订单金额，使用 COUT() 统计购买次数。
2. 窗口函数：
   • 使用 ROW_UMBER() 计算每个客户购买次数最多的商品类别。
3. 子查询和 CTE（Common Table Expressi）：
   • 使用 WITH 子句将复杂查询分解为多个步骤，提高可读性。