如何用SQL窗口函数快速识别电商订单支付的波峰波谷-SQL-PHP中文网

lag和lead比聚合更合适识别波峰波谷，因其支持局部时序比较——通过获取前后相邻时间点的值与当前行对比，精准识别“某小时比前后都高/低”，而非仅返回全局极值。

如何用sql窗口函数快速识别电商订单支付的波峰波谷

为什么 `LAG` 和 `LEAD` 比聚合更合适识别波峰波谷

直接用 GROUP BY + MAX/MIN 只能拿到全局极值，但电商支付流量是随时间连续变化的——你需要知道「某个小时比前后都高」，而不是「哪个小时最高」。窗口函数让每行都能看到邻近时间点的值，这才是波峰波谷的本质定义。

关键不是找最大值，而是做局部比较。用 LAG(payment_amount, 1) OVER (ORDER BY pay_time) 拿前一小时金额，LEAD(payment_amount, 1) OVER (ORDER BY pay_time) 拿后一小时，再和当前行对比即可。

必须按 pay_time 精确到小时（或分钟）排序，否则时序错乱，波峰判断全失效
如果原始数据是秒级时间戳，先用 DATE_TRUNC('hour', pay_time)（PostgreSQL）或 FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(pay_time)/3600)*3600)（MySQL）归一化
LAG/LEAD 默认返回 NULL（边界行），需用 COALESCE 处理，否则 current > prev AND current > next 整个条件会变成 UNKNOWN

用 `CASE WHEN` + 窗口函数标记波峰波谷的实际写法

别写成多个子查询嵌套。一步到位：在主查询里直接计算前后值，再用 CASE 判断类型。这样既可读又高效，避免反复开窗。

SELECT
  hour_slot,
  total_payment,
  CASE
    WHEN total_payment > COALESCE(prev_hour, 0) 
     AND total_payment > COALESCE(next_hour, 0) THEN 'peak'
    WHEN total_payment = CURRENT_DATE - INTERVAL '7 days'
  GROUP BY DATE_TRUNC('hour', pay_time)
) t

注意内层 GROUP BY 和外层窗口的排序字段必须一致，否则 LAG/LEAD 顺序错位
MySQL 用户把 DATE_TRUNC 换成 DATE_FORMAT(pay_time, '%Y-%m-%d %H:00:00')，且 ORDER BY 要用同样表达式
波谷判断里用大数代替 NULL 是为了防止首尾小时被误标为波谷——真实场景中，你通常不关心数据边界上的“假极值”

如何处理高频订单导致的“毛刺”干扰

单笔大额订单（比如企业采购）会让某分钟突然飙升，但这不是真正的流量波峰。直接看原始支付金额会失真，得平滑。

Transfusion AI

一款面向室内设计场景的AI设计工具，帮助设计师围绕空间方案进行效果创作与视觉方案探索。

下载

改用支付订单数（COUNT(*)）代替金额，更反映用户行为密度
或加一层移动平均：用 AVG(total_payment) OVER (ORDER BY hour_slot ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) 替换原始值再比较
更稳妥的是先过滤异常单：在 WHERE 加 payment_amount （PostgreSQL）剔除顶部 5% 订单