1. 窗口函数入门为什么它能解决传统SQL的痛点第一次接触MySQL窗口函数时我正被一个销售报表需求折磨得焦头烂额。需要计算每个销售人员的业绩排名同时还要显示他们占团队总业绩的百分比。用传统GROUP BY写法要么得写五六层嵌套子查询要么得跑多次查询再程序里拼接数据。直到同事扔给我一个包含OVER关键字的SQL示例我才发现原来还有这么优雅的解决方案。窗口函数最神奇的地方在于它能在保留原始行明细的同时完成各种复杂的分析计算。比如你查询订单表时既能显示每笔订单的详细信息又能同时计算该客户的历史订单总金额。这种既要...又要...的需求在电商、金融、物流等业务场景中实在太常见了。举个真实案例某外卖平台需要分析骑手配送效率。传统做法是先按骑手分组计算平均配送时间再关联回原表比对每个订单是否超时。而用窗口函数一个查询就能同时输出订单详情、骑手平均时效、当前订单与平均值的差异。查询速度从原来的15秒降到0.3秒代码量减少了70%。2. 排名函数的实战技巧2.1 电商爆款排名中的三种选择去年双十一大促时我们用ROW_NUMBER()实现了个有趣的功能——实时刷新商品销量排行榜。这里有个细节当两个商品销量相同时ROW_NUMBER()会随机分配名次导致页面频繁跳动。后来改用DENSE_RANK()相同销量的商品共享排名用户体验立刻提升。-- 实时销量排行榜 SELECT product_id, product_name, sales_count, DENSE_RANK() OVER(ORDER BY sales_count DESC) AS sales_rank FROM products WHERE category_id 101 ORDER BY sales_rank LIMIT 100;但要注意RANK()和DENSE_RANK()在性能上会比ROW_NUMBER()稍差。我们在压力测试时发现当处理百万级数据时前两者的执行时间会比后者多15-20%。所以如果是分页查询这种不需要处理并列排名的场景坚持用ROW_NUMBER()更合适。2.2 分组排名的隐藏陷阱PARTITION BY用起来很顺手但有个坑我踩过三次当分组字段存在NULL值时所有NULL记录会被归到同一个分组。有次做客户价值分析时因为部分客户缺少地区信息导致排名结果完全错乱。解决方案要么提前处理NULL值要么加上COALESCE函数SELECT customer_id, region, purchase_amount, RANK() OVER( PARTITION BY COALESCE(region, 未知地区) ORDER BY purchase_amount DESC ) AS region_rank FROM customers;3. 累计计算的高级玩法3.1 动态时间窗口的销售分析在做年度销售报表时我们经常需要对比不同时间段的累计数据。通过调整ROWS BETWEEN的区间可以玩出很多花样。比如这个查询能同时计算当月累计、季度累计和年度累计SELECT sale_date, amount, SUM(amount) OVER( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS ytd_amount, SUM(amount) OVER( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS last_3days_amount, SUM(amount) OVER( PARTITION BY QUARTER(sale_date) ORDER BY sale_date ROWS UNBOUNDED PRECEDING ) AS quarter_amount FROM sales WHERE YEAR(sale_date) 2023;3.2 移动平均的优化实践计算7日移动平均时新手常犯的错误是直接使用RANGE BETWEEN INTERVAL 6 DAY PRECEDING。这在MySQL中会导致全表扫描。更高效的做法是先用日期维度表关联再用ROWS BETWEEN-- 优化后的7日移动平均 SELECT t1.date, t1.daily_sales, AVG(t2.daily_sales) OVER( ORDER BY t1.date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS moving_avg_7day FROM daily_sales t1 JOIN daily_sales t2 ON t2.date BETWEEN t1.date - INTERVAL 6 DAY AND t1.date GROUP BY t1.date, t1.daily_sales;4. 趋势分析的杀手锏组合4.1 同比环比的正确姿势计算同比增长率时很多人直接用LAG(12)来取去年同月数据。这在有数据缺失的月份会出错。更可靠的方法是先按年月分组聚合再用自连接确保对比月份存在WITH monthly_sales AS ( SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount) AS total FROM sales GROUP BY YEAR(sale_date), MONTH(sale_date) ) SELECT m1.year, m1.month, m1.total, (m1.total - m2.total) / m2.total * 100 AS yoy_growth FROM monthly_sales m1 LEFT JOIN monthly_sales m2 ON m1.month m2.month AND m1.year m2.year 1;4.2 使用LEAD预测库存需求在供应链系统中我们结合历史销量和LEAD函数开发了智能补货模型。这个查询能预测未来7天的销量趋势SELECT product_id, date, daily_sales, AVG(daily_sales) OVER( PARTITION BY product_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS avg_recent_sales, LEAD(daily_sales, 7) OVER( PARTITION BY product_id ORDER BY date ) AS next_week_sales FROM inventory_sales WHERE date CURRENT_DATE - INTERVAL 30 DAY;5. 性能优化的血泪教训5.1 索引设计的黄金法则窗口函数最吃性能的就是PARTITION BY和ORDER BY子句。有次我们处理千万级用户行为数据时发现某个查询跑了20分钟还没结果。加上复合索引后3秒就出结果了。正确的索引策略应该是-- 为窗口函数优化的索引 ALTER TABLE user_events ADD INDEX idx_window (user_id, event_time);5.2 避免窗口函数嵌套曾见过一个报表查询嵌套了5层窗口函数执行计划惨不忍睹。后来我们拆分成多个CTECommon Table Expressions性能提升了40倍-- 优化前伪代码 SELECT ... OVER(... OVER(... OVER(...))) FROM table; -- 优化后 WITH step1 AS (SELECT ... OVER(...) FROM table), step2 AS (SELECT ... OVER(...) FROM step1) SELECT ... FROM step2;5.3 分区剪枝的妙用当处理大型历史数据时先用WHERE条件缩小数据范围再应用窗口函数。有次我们分析5年数据时先按季度过滤再计算查询时间从8分钟降到15秒-- 好的实践 SELECT ... OVER(...) FROM large_table WHERE year_column 2023 AND quarter_column 2; -- 反面教材 SELECT ... OVER(...) FROM large_table;