用SQL分析良率：那些必会的窗口函数-平芜编程栈

一、背景故事：凌晨两点的Excel卡死

我们良率组的同事小周，每周三都要熬一个夜：把全厂两周的良率数据从MES导出成CSV，再在Excel里做透视表，算每个设备的良率排名、每个lot的wafer内良率分布、环比变化。文件二十万行，Excel打开要半分钟，每次透视都要等，改一个维度就要重来。上周三他做到凌晨两点，Excel直接卡死未保存，一晚上的活全没了。

我说你这些分析用SQL半小时就做完了，他不信。我当场在他电脑上敲了一段窗口函数SQL，把他要的三个分析一次跑完，结果两分钟出全。他愣了半天，第二天就开始学窗口函数，现在他是我们组里SQL用得最溜的人，每周三准时下班。

这不是段子，是很多良率工程师的真实处境：不是不会分析，而是工具拖了后腿。SQL窗口函数是良率分析场景下性价比最高的技能，没有之一。本文就把良率分析最常用的窗口函数场景整理成一份可直接抄的实战手册。

二、技术原理：窗口函数到底怎么执行

窗口函数（Window Function）和普通聚合函数（GROUP BY）最大的区别是：聚合函数把多行压缩成一行，窗口函数在每一行上都返回计算结果，行数不减少。执行时，数据库先按PARTITION BY把数据分成若干组（分区），再在每个分区内按ORDER BY排序，然后对每一行计算指定窗口范围内的聚合或排名值。

理解三个核心概念就够了。分区（PARTITION BY）：决定"按什么分组计算"，比如按设备ID分区，就是每台设备独立计算。排序（ORDER BY）：决定分区内的行序，排名类函数依赖它。窗口框架（ROWS BETWEEN...）：决定计算范围，默认是"分区内从第一行到当前行"，可以改成滑动窗口，比如最近三天。这三个概念组合起来，几乎能覆盖良率分析的所有场景。

良率分析最常用的函数族有四类：排名类（ROW_NUMBER、RANK、DENSE_RANK），用于设备/批次排名；偏移类（LAG、LEAD），用于取上一行/下一行的值，算环比、同比；聚合类（SUM、AVG、MIN、MAX加OVER），用于累计值、移动平均；分布类（NTILE、PERCENT_RANK），用于分位、分层。每类函数在良率分析里都有典型场景，下面逐个实战。

三、现状分析：良率数据长什么样

先看我们良率分析常用的三张核心表。批次表LOT_RECORD：记录每个lot的ID、产品型号、工艺节点、投入日期、完成日期、良率。wafer表WAFER_RECORD：记录每个lot下每片wafer的ID、位置（边缘/中心）、每道关键工序的良率，一个lot通常二十五片wafer。设备履历表EQUIPMENT_LOG：记录每个lot在每道工序实际使用的设备ID和腔体号，这是设备级分析的基础。

传统做法是用GROUP BY加子查询：算设备排名要先算每台设备的平均良率，再在外面套一层排序，还要处理并列名次，SQL写得又长又绕。算环比更痛苦：要把本月数据和上月数据分别查出来再JOIN，月数一多SQL就爆炸。这就是为什么大家宁可导出Excel——不是Excel好用，是SQL写起来太痛。

痛点集中在四类场景：同组内排名要自连接、环比要自连接、移动平均要自连接、TopN筛选要写复杂的子查询。每一类自连接都让SQL的复杂度和出错概率翻倍。窗口函数把这些自连接全部消灭，语法上就是"在SELECT里加一个函数加一个OVER"。

四、瓶颈问题：GROUP BY解决不了的四个场景

场景一：设备良率排名。要用GROUP BY算出每台设备良率，再按良率排序给名次，还要处理并列。传统写法：先子查询算出设备良率，再外层排序，排名字段还得自己用变量或自连接模拟。场景二：同lot内wafer良率对比。要算每个lot内每片wafer的良率相对本lot平均值的偏差，传统写法需要lot级别的聚合结果再JOIN回wafer明细。

场景三：良率环比。要算本月每台设备的良率比上个月高还是低，传统写法要把本月数据和上月数据分别聚合，再按设备ID FULL JOIN，漏掉某月没生产的设备还容易出NULL坑。场景四：TopN筛选。要找出每台设备最近十批的良率，或者每个产品型号良率最高的三个lot，传统写法要用相关子查询或者窗口函数套子查询，性能还差。

这四个场景恰好是良率分析周报的核心内容，每周都要跑一遍。用传统SQL写，每段都要二十行以上，改个维度就要重写；用窗口函数写，每段不超过五行，参数化之后一次写完、永久复用。这就是窗口函数值得学的根本原因——它不是炫技，是实打实地把高频工作从半小时压缩到两分钟。

五、解决方案：良率分析窗口函数实战SQL

第一类：设备良率排名。SELECT 设备ID, AVG(良率) AS 平均良率, RANK() OVER (ORDER BY AVG(良率) DESC) AS 排名 FROM WAFER_RECORD GROUP BY 设备ID。RANK遇到并列会跳号（1,1,3），DENSE_RANK不跳号（1,1,2），要哪种看需求。如果还想看每台设备在同类设备里的排名，加上PARTITION BY设备类型即可。

第二类：同lot内wafer偏差。SELECT lot_ID, wafer_ID, 良率, AVG(良率) OVER (PARTITION BY lot_ID) AS lot均值, 良率 - AVG(良率) OVER (PARTITION BY lot_ID) AS 偏差 FROM WAFER_RECORD。一行SQL同时输出每片wafer的良率、所在lot的均值和偏差，直接在结果里标红偏差超过三个百分点的wafer，就是现成的异常清单。

第三类：环比。SELECT 月份, 设备ID, 平均良率, LAG(平均良率, 1) OVER (PARTITION BY 设备ID ORDER BY 月份) AS 上月良率, 平均良率 - LAG(平均良率, 1) OVER (PARTITION BY 设备ID ORDER BY 月份) AS 环比变化 FROM 月度设备良率表。LAG取上一条记录的值，LEAD取下一条，环比同比从此告别自连接。

第四类：移动平均与累计。SELECT 日期, 日良率, AVG(日良率) OVER (ORDER BY 日期 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 七日移动平均 FROM 每日良率表。移动平均可以平滑日常波动，看趋势比原始数据清晰得多；SUM(良率) OVER (ORDER BY 日期)则是累计良率曲线，爬坡阶段看这个最直观。

第五类：TopN筛选。SELECT * FROM (SELECT lot_ID, 良率, ROW_NUMBER() OVER (PARTITION BY 产品型号 ORDER BY 良率 DESC) AS rn FROM LOT_RECORD) t WHERE rn <= 3。取出每个产品型号良率最高的三个lot，用于标杆分析。注意ROW_NUMBER给每行唯一编号，RANK会并列，TopN精确取数用ROW_NUMBER。

六、实战案例：一次两小时的排查被压缩到十分钟

上个月，设备工程师报告某台刻蚀机的良率"好像"在下降，需要良率组确认。放在以前，流程是：导出两周wafer数据（半小时）→Excel透视算周度良率（半小时）→对比上周（半小时）→发现数据波动再回去查设备履历（半小时），两小时起步。这次我用窗口函数一把梭：设备良率周度排名+环比变化+LAG取上周值，一条SQL出结果。

结果直接定位：该刻蚀机本周良率排名从第三掉到第九，环比下降二点三个百分点，而同样腔体的另外两台设备环比持平。数据清清楚楚指向这台设备，设备团队拿着这张表去做排查，发现是腔体加热器老化导致的工艺温度漂移，更换后良率回升。整个分析过程十分钟，其中九分钟在等数据库跑数。

另一个高频场景是lot异常拦截。我们做了一个每日自动SQL：用窗口函数算出每片wafer相对lot均值的偏差，偏差超标的wafer自动进入复核队列，原来人工翻Excel要两小时，现在每天上班前自动跑完，异常清单已经躺在邮箱里。

七、实施效果：分析效率的量变到质变

窗口函数在良率组推广三个月后的效果：周报制作时间从每人每周六小时压缩到一小时以内；设备异常定位从"先怀疑、再验证"变成"数据先说话、设备去验证"，平均定位周期从两天缩短到半天；因为分析效率提升，我们终于有余力做以前没时间做的分析——每台设备的良率趋势移动平均监控、每个产品型号的标杆lot对比、每道工序的累计良率爬坡曲线。

还有两个隐性收益。第一，SQL让分析口径固化了，以前Excel手工透视每个人算出来的数可能不一样，现在同一段SQL跑出来永远一致，会议上再也不用争论"你这个数怎么来的"。第二，分析结果可以直接对接自动化工单：SQL查出来的异常列表，脚本自动生成工单推给对应工程师，分析到执行的链路第一次真正打通。

给同行们的建议：别被窗口函数的语法吓到，核心就记住三句话——PARTITION BY决定分组、ORDER BY决定顺序、ROWS BETWEEN决定范围。把本文的五个场景SQL在自己的数据库里跑一遍，你就能体会到"半小时变两分钟"的快感。

八、常见问题与延伸阅读

Q1：窗口函数和GROUP BY一起用，有什么注意事项？

一个最常见的报错场景：SELECT里同时写了GROUP BY聚合和窗口函数，数据库报错说窗口函数不能和聚合混用。正确做法是分两步：先用子查询或CTE把GROUP BY的聚合结果算出来，再在外层对聚合结果用窗口函数。比如"每台设备的月度良率排名"，先GROUP BY设备、月份算出平均良率，再在外层用RANK OVER (PARTITION BY 月份 ORDER BY 平均良率 DESC)。记住这个次序：聚合先生成结果集，窗口函数在结果集上做计算，两者不在同一层混用。

Q2：窗口函数会不会很慢，大数据量下怎么办？

窗口函数需要把分区内的数据排序，数据量大时确实有开销，但远小于自连接的代价。优化三板斧：第一，确保PARTITION BY和ORDER BY涉及的列有合适的索引，让数据库能利用索引顺序避免额外排序；第二，能用分区裁剪就尽量加过滤条件，把参与计算的数据量降下来；第三，如果窗口函数出现在过滤条件里（比如取TopN），先在外层子查询里算，再过滤，避免对全表每行都算一遍。良率分析的数据量级（百万行以内）对窗口函数毫无压力，真正该担心的是笛卡尔积式的自连接。

Q3：LAG取环比时，某个月没数据导致结果不对怎么办？

这是环比场景的高频坑：某台设备上个月停机没生产，本月恢复生产，LAG取到的"上月良率"是NULL，环比计算结果全部落空。两个处理办法：一是用COALESCE把NULL替换成合理值，比如替换成该设备的历史均值或者本月值本身，同时在报表里标注"上月无数据"；二是更严谨的做法，改用LAST_VALUE加IGNORE NULLS，或者直接对"有数据的最近一个月"做环比，而不是机械地取上一条记录。环比的意义在于对比，数据缺失时宁可明确标注，也不要让一个NULL污染整行结论。

Q4：公司数据库权限有限，窗口函数不让用怎么办？

权限受限的情况下有两条路。第一条路：让DBA评估后开通窗口函数权限，说明使用场景是良率分析，多数公司对只读分析权限的审批并不严格；第二条路：在权限开通前，用Python在本地模拟窗口函数逻辑——pandas的groupby加transform、rank、shift方法完全能复现ROW_NUMBER、RANK、LAG、移动平均的效果。实际上很多良率分析组的标准做法就是"SQL取数加pandas分析"，窗口函数让SQL把取数和初步加工一步完成，但pandas永远是兜底方案。两条路都值得掌握，SQL负责快，pandas负责灵活。

延伸思考：窗口函数之外，良率分析还要学什么？

窗口函数解决的是"取数和加工"的效率问题，往上走还有两个方向值得投入。一是统计方法：置信区间、假设检验、方差分析，这些是判断"良率差异到底是真是假"的武器，良率工程师的很多争论其实都能用统计检验一锤定音。二是可视化：把窗口函数算出来的结果画成趋势图、分布图、热力图，图比表更能说服管理层。工具是链条，SQL、统计、可视化三个环节都通了，你才算真正具备了数据驱动的良率分析能力。

Q4：窗口函数在不同数据库里的语法一样吗？

主流数据库的窗口函数语法高度统一，都是"函数加OVER(PARTITION BY加ORDER BY)"，但有几个细节差异值得注意。MySQL从8.0开始支持窗口函数，8.0之前只能用变量模拟；PostgreSQL、SQL Server、Oracle、Hive都完整支持，语法基本一致。差异主要在细节：一是字符串拼接和日期函数各库不同（不影响窗口函数本身）；二是部分数据库对ORDER BY在窗口函数里的用法有细微限制；三是SQL Server的LAG/LEAD需要指定默认值时的写法略有不同。总体结论：窗口函数的SQL可以一份脚本跑通绝大多数数据库，迁移成本比想象中低，这也是它值得学的原因之一。

Q5：除了窗口函数，良率分析还有哪些SQL必学技能？

按优先级排，窗口函数之外还有四样。第一，CASE WHEN条件聚合，把多列状态转成指标，比如把不同缺陷类型转成多列计数；第二，日期时间函数，月周日报、同比环比的日期口径全靠它；第三，子查询和CTE（WITH语句），复杂分析拆成多步，可读性翻倍；第四，索引和执行计划的基本认知，知道一条SQL为什么慢、怎么让它快。这四样加窗口函数，基本覆盖了良率分析百分之九十的SQL场景。学习路径建议：先窗口函数，再CASE WHEN和日期函数，最后补CTE和性能优化，一个月就能从"够用"到"顺手"。

最后给一个学习心态的建议：别追求一次把SQL学完，良率分析场景就那么几十个高频问题，一个一个解决，每解决一个就沉淀一段可复用的SQL片段，三个月后你就拥有一个自己的"良率分析SQL工具箱"。工具的意义在于解决问题，不在于炫技，能两分钟出结果的分析，就是好分析。

九、行动清单：今天就能上手的三个SQL

第一，在你自己库的wafer表上跑一遍"同lot内wafer良率偏差"的SQL，把偏差超过三个百分点的wafer标出来，看看能不能发现平时Excel里看不到的规律；第二，写一个"设备月度良率环比"查询，用LAG函数替代你以前的自连接写法，感受一下代码量减半的爽快；第三，把周报里最常做的三个分析全部改成窗口函数版本，跑通后保存成你的个人SQL片段库。这三个SQL写完之后，你就是你们组第一个"两分钟出周报数据"的人。窗口函数的学习曲线很陡，但第一个SQL跑通之后，剩下的都是水到渠成。

再补充一个实用细节：把窗口函数和你的周报流程绑定起来，价值会翻倍。比如把"同lot偏差超标wafer自动标记"这段SQL挂到定时任务里，每天早上自动跑，异常清单直接推送到邮箱或者企业微信群，良率组上班第一件事就是看清单而不是翻Excel。从"手动分析"到"自动推送"，中间只差一个定时任务，但工作方式的改变是质变。

八、配图：数据可视化

图1：SQL分析流水线处理流程

图2：窗口函数分析结果对比

九、窗口函数速查表

函数	用途	良率分析场景	关键语法
ROW_NUMBER()	行号	TopN精确取数	OVER (PARTITION BY ... ORDER BY ...)
RANK()	排名(并列跳号)	设备良率排名	OVER (ORDER BY良率DESC)
DENSE_RANK()	排名(并列不跳号)	排名且保留并列	OVER (ORDER BY良率DESC)
LAG/LEAD()	取前/后行值	环比、同比	LAG(列, 1) OVER (ORDER BY月份)
AVG/SUM OVER()	窗口聚合	移动平均、累计良率	ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
NTILE()	分桶	良率分层分析	NTILE(4) OVER (ORDER BY良率)

十、良率分析核心表结构说明

表名	关键字段	粒度	典型用途
LOT_RECORD	lot_ID/产品/节点/良率	批次级	批次良率排名、标杆分析
WAFER_RECORD	lot_ID/wafer_ID/位置/工序良率	wafer级	同lot偏差、工序良率
EQUIPMENT_LOG	lot_ID/设备ID/腔体号	批次-设备级	设备良率排名、环比
DAILY_YIELD	日期/良率/产量	日级	移动平均、累计曲线
DEFECT_RECORD	lot_ID/缺陷类型/密度	批次级	缺陷与良率关联
月度设备良率表	月份/设备ID/平均良率	月-设备级	环比、同比