如何统计同客户往期订单数？新增week-1_orders与prev-weeks_orders列

架构老林

2026-8-17

订单表新增周度订单统计字段的实现方案

要实现给每笔订单加上同一客户上周订单数、往期总订单数的需求，用窗口函数是最直接高效的方式，也可以用自关联兼容老数据库，两种方法如下：

方法一：窗口函数（推荐，性能更优）

大部分现代SQL数据库（比如PostgreSQL、MySQL 8+、SQL Server等）都支持窗口函数，直接在每行的上下文里计算同一客户的历史数据：

SELECT 
  orderId,
  customerId,
  week,
  -- 统计同一客户上周的订单数量
  COUNT(*) FILTER (WHERE week = o.week - 1) OVER (PARTITION BY o.customerId) AS "week-1_orders",
  -- 统计同一客户当前周之前的所有订单数量
  COUNT(*) FILTER (WHERE week < o.week) OVER (PARTITION BY o.customerId) AS "prev-weeks_orders"
FROM orders o;

如果你的数据库不支持FILTER子句（比如MySQL 5.x），换成CASE语句也可以：

SELECT 
  orderId,
  customerId,
  week,
  COUNT(CASE WHEN week = o.week - 1 THEN 1 END) OVER (PARTITION BY o.customerId) AS "week-1_orders",
  COUNT(CASE WHEN week < o.week THEN 1 END) OVER (PARTITION BY o.customerId) AS "prev-weeks_orders"
FROM orders o;

核心逻辑：

PARTITION BY customerId：把数据按客户分组，只在同一个客户的订单里统计
用FILTER/CASE筛选出符合条件的订单，COUNT就得到对应数量

方法二：自关联（兼容所有SQL数据库）

如果你的数据库不支持窗口函数，用自关联也能实现：

SELECT 
  o1.orderId,
  o1.customerId,
  o1.week,
  COUNT(o2.orderId) AS "week-1_orders",
  COUNT(o3.orderId) AS "prev-weeks_orders"
FROM orders o1
LEFT JOIN orders o2 
  ON o1.customerId = o2.customerId 
  AND o2.week = o1.week - 1
LEFT JOIN orders o3 
  ON o1.customerId = o3.customerId 
  AND o3.week < o1.week
GROUP BY o1.orderId, o1.customerId, o1.week;

逻辑很简单：