高级分析1:每个客户”最贵的一笔订单”

1.需求描述

对每个客户，找出他下的所有订单中:

  • 金额最高的一单(不是明细，是整单总价)
  • 显示客户姓名,订单ID,订单金额
    这是非常常见的业务需求(类似”每个客户最近购买的物品”，”每个员工的最高销售额”)

2.SQL思路

  1. 先算每个订单的总金额(订单金额=每项价格x数量)
  2. 再按窗户分组，找金额最大的订单
  3. 用窗口函数ROW_NUMBER()排序取第一名

3.解决方案(MySQL8.0)

第一步:计算每个订单的金额(子查询)

1
2
3
4
5
6
7
8
9
SELECT
    o.ordee_id,
    o.customer_id,
    SUM(p.price * oi.quantity)
AS order o
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
GROUP BY o.order_id,o.customer_id;

得到:

order_id customer_id product_amount
1001 1 2597.00
1002 2 447.0
第二部:对每个客户按金额排序，取第一名 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT *FROM(
    SELECT
        c.name AS customer_name,
        o2.order_id,
        o2.order_amout,
        ROW_NUMBER() OVER(
            PARTITION BY c.customer_id  ORDER BY o2.order_amout DESC
        )AS rn
        FROM (SELECT
            o.order_id,
            o.customer_id,
            SUM(p.price * oi.quantity) AS order_amout order_amout
            FROM Orders o
            JOIN OrderItems oi ON o.order_id = oi.order_id
            JOIN Products p ON oi.product_id = p.product_id
            GROUP BY o.order_id,o.customer_id 
            )o2
            JOIN Customers c ON o2.customer_id =c.customer_id)ranked
            WHERE rn =1;

4.查询结果示例

customer_name order_id order_amout
Alice 1001 2597.00
Bob 1002 447.00

高级分析2:每个产品的平均”每单”销量

1.需求描述

例如，充电宝买了3个，但分布在多少订单?
需要算:

1
平均每单购买数量 = (总销量)/(下单次数)

2.SQL

1
2
3
4
5
6
7
8
SELECT
    p.product_name,
    SUM(oi.quantity) AS total_sold,
    COUNT(DISTINCT oi.order_id) AS order_count,
    ROUND(SUM(oi.quantity)/(DISTINCT oi.order_id),2) AS avg_per_order
    FROM Products p
    JOIN OrderItems oi ON p.product_id = oi.product_id
    GROUP BY p.product_id,p.product_name;
product_name total_sold order_count avg_per_orders
充电宝 3 1 3.00
耳机 2 1 2.00
手机 1 1 1.00

高级分析3:统计每日销售额(安居日期聚合)

这是电商最常用的时间序列分析。

1
2
3
4
5
6
7
8
9
SELECT
     o.order_date,
     SUM(p.price * oi.quantity) AS daily_sales
     FROM Orders o
     JOIN OrderItems oi ON o.order_id = oi.order_id
     JOIN Products p ON oi.product_id = p.product_id
     GROUP BY o.order_date
     ORDER BY o.order_date;

效果:

|order_date|daily_sales|
|2025-11-01|2597.00|
|2025-11-02|447.00|

高级分析4:每个客户最近一次下单

业务中非常常见:”给过去30天未下单客户发送优惠卷”。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT *
FROM (
    SELECT
        c.name AS customer_name,
        o.order_id,
        o.order_date,
        ROW_NUMBER()OVER(
        PARTITION BY o.customer_id
        ORDER BY o.order_name DESC)
        AS rn
    FROM orders o
    JOIN Customers c ON o.customer_id = c.customer_id)t
    WHERE rn = 1;