“首单与末单之间的累计消费总额”指对每个客户,以其逻辑首单和末单时间点为界,汇总该时间窗口内所有订单金额;需用ROW_NUMBER()精准定位首末单,再通过LATERAL或相关子查询安全求和,避免笛卡尔积。

什么是“首单与末单之间的累计消费总额”
这个需求常被误解为“只加首单和末单金额”,实际业务中通常指:对每个客户,找出其第一笔订单和最后一笔订单的时间点,然后把这两个时间点(含)之间所有订单的金额加总。关键在“时间窗口”而非“仅两笔订单”。
用窗口函数定位首单/末单时间点
不能依赖 MIN(order_time) 和 MAX(order_time) 直接做范围筛选——因为多个订单可能在同一秒下单,且需确保取到的是“逻辑上第一笔/最后一笔”(比如按主键或时间+ID排序)。推荐用 ROW_NUMBER():
SELECT customer_id,
FIRST_VALUE(order_time) OVER (PARTITION BY customer_id ORDER BY order_time, order_id) AS first_order_time,
LAST_VALUE(order_time) OVER (PARTITION BY customer_id ORDER BY order_time, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_time
FROM orders;
注意:LAST_VALUE 必须显式声明窗口帧(ROWS BETWEEN...),否则默认是 CURRENT ROW,结果全为当前行时间。
关联原表并求和:避免自连接爆炸
拿到每个客户的 first_order_time 和 last_order_time 后,常见错误是直接 JOIN 原表并用 BETWEEN 筛选——若客户订单量大、时间跨度长,会引发笛卡尔积风险(尤其当首末时间相同且有多单时)。更稳的方式是用 LATERAL(PostgreSQL)或带相关子查询(MySQL 8.0+/SQL Server):
- PostgreSQL 推荐写法:
SELECT t1.customer_id, SUM(t2.amount) AS total_between_first_last
FROM (
SELECT DISTINCT customer_id,
MIN(order_time) FILTER (WHERE rn_asc = 1) AS first_t,
MAX(order_time) FILTER (WHERE rn_desc = 1) AS last_t
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time, order_id) AS rn_asc,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC, order_id DESC) AS rn_desc
FROM orders
) t
GROUP BY customer_id
) t1
JOIN LATERAL (
SELECT amount FROM orders t2
WHERE t2.customer_id = t1.customer_id
AND t2.order_time >= t1.first_t
AND t2.order_time <= t1.last_t
) t2 ON true
GROUP BY t1.customer_id;
- MySQL/SQL Server 可改用相关子查询或 CTE +
IN,但要注意order_time字段必须有索引,否则性能断崖下跌 - 如果业务允许“首单/末单按订单ID排序而非时间”,可直接用
MIN(order_id)/MAX(order_id),避免时间重复带来的歧义
边界情况必须手动校验
真实数据里容易漏掉三类 case:
- 客户只有 1 笔订单:此时首单=末单,累计总额就是该单金额,但部分写法会因
ORDER BY ... DESC导致rn_desc不稳定而漏算 - 首单与末单时间相同但订单 ID 不同:需确认业务是否要求包含所有同秒订单(通常要)
- 存在订单时间为空(
NULL):MIN/MAX和ROW_NUMBER默认忽略NULL,但若首单恰好是NULL时间(极少见),整个逻辑就失效——建议提前清洗或用CASE WHEN order_time IS NULL THEN '1970-01-01' ELSE order_time END统一兜底
最保险的做法是:先用小样本(如 5 个客户)人工核对首单 ID、末单 ID、期间订单 ID 列表,再跑全量。时间字段的精度(秒?毫秒?)和数据库时区设置也会影响结果一致性,不能只看 SQL 逻辑。

















