LAG和LEAD比聚合更合适识别波峰波谷，因其支持局部时序比较——通过获取前后相邻时间点的值与当前行对比，精准识别“某小时比前后都高/低”，而非仅返回全局极值。
为什么 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 < COALESCE(prev_hour, 9999999)
AND total_payment < COALESCE(next_hour, 9999999) THEN 'trough'
ELSE 'normal'
END AS trend_type
FROM (
SELECT
DATE_TRUNC('hour', pay_time) AS hour_slot,
SUM(payment_amount) AS total_payment,
LAG(SUM(payment_amount), 1) OVER (ORDER BY DATE_TRUNC('hour', pay_time)) AS prev_hour,
LEAD(SUM(payment_amount), 1) OVER (ORDER BY DATE_TRUNC('hour', pay_time)) AS next_hour
FROM orders
WHERE pay_status = 'paid'
AND pay_time >= 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是为了防止首尾小时被误标为波谷——真实场景中，你通常不关心数据边界上的“假极值”
如何处理高频订单导致的“毛刺”干扰
单笔大额订单（比如企业采购）会让某分钟突然飙升，但这不是真正的流量波峰。直接看原始支付金额会失真，得平滑。
- 改用支付订单数（
COUNT(*)）代替金额，更反映用户行为密度 - 或加一层移动平均：用
AVG(total_payment) OVER (ORDER BY hour_slot ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)替换原始值再比较 - 更稳妥的是先过滤异常单：在
WHERE加payment_amount （PostgreSQL）剔除顶部 5% 订单
为什么不能只依赖 ROW_NUMBER() 排名找极值
ROW_NUMBER() OVER (ORDER BY total_payment DESC) 只给出全局排名，第 1 名可能是凌晨 3 点的孤立高点，和业务上“白天持续高峰”完全无关。波峰波谷是局部概念，必须带上下文比较。
另外，RANK() 在值相同时会跳名次，导致两个相同高峰一个标 peak、一个没标；而 LAG/LEAD 不受重复值影响，逻辑稳定。
真正难的不是写对语法，是定义清楚“波峰”的业务含义：要持续 3 小时以上？是否排除节假日？这些规则得先固化进 WHERE 和 CASE 条件里，而不是靠窗口函数自动推断。
