窗口函数是 SQL 里从会写到写得好的分水岭。但网上讲窗口函数的文章大多只贴语法不给可运行的数据看完还是不会用。这篇文章的 3 个例子来自我自己搭的一套电商测试库用户/商品/订单/明细/行为 5 张表每一条 SQL 都真跑过结果一并贴出。一、给每个用户的订单编号ROW_NUMBER需求想看每个用户的第几单用于识别首单、复购。SELECTu.nameAS用户名,o.order_dateAS下单日期,ROW_NUMBER()OVER(PARTITIONBYo.user_idORDERBYo.order_date,o.id)AS第几单FROMorders oJOINusers uONu.ido.user_idORDERBY用户名,第几单;要点PARTITION BY决定分组边界ORDER BY决定组内排序。排序字段如果可能重复一定要再补一个唯一列比如 id否则编号会不稳定。二、算环比增长率LAG需求按月看销售额并算出相对上月的增长率。WITHmAS(SELECTsubstr(order_date,1,7)AS月份,SUM(pay_amount)AS销售额FROMordersWHEREstatus已完成GROUPBYsubstr(order_date,1,7))SELECT月份,ROUND(销售额,2)AS销售额,ROUND(100.0*(销售额-LAG(销售额)OVER(ORDERBY月份))/LAG(销售额)OVER(ORDERBY月份),1)AS环比增长百分比FROMmORDERBY月份;要点第一个月没有上月结果自然是 NULL——别急着用 IFNULL 填 00 和没有数据是两回事报表里含义不同。三、找出连续两个月都有下单的用户CTE 自连接WITHumAS(SELECTDISTINCTuser_id,substr(order_date,1,7)AS月份FROMordersWHEREstatus已完成)SELECTDISTINCTu.nameAS用户名FROMum aJOINum bONa.user_idb.user_idANDb.月份strftime(%Y-%m,date(a.月份||-01,1 month))JOINusers uONu.ida.user_id;要点先去重到用户-月份再自连接比直接在两百万行明细上做关联快得多。能先缩小数据规模就别一上来就 JOIN 大表。四个最容易踩的坑把窗口函数塞进 WHERE窗口函数在 WHERE 之后才计算必须用子查询或 CTE 包一层再过滤。PARTITION BY 忘了写整张表被当成一个组排名全乱。ROW_NUMBER 与 RANK 混用并列时 ROW_NUMBER 仍连续编号RANK 会跳号1,1,3。NULL 参与排序不同数据库对 NULL 的排序位置默认不同需要显式指定 NULLS FIRST/LAST。怎么验证自己写对了最省事的办法是先跑一遍看行数算排名时行数应该与明细行数一致算分组聚合时行数应该等于组数。对不上多半就是 GROUP BY 或 PARTITION BY 写漏了。上面 3 个例子来自我整理的一套 SQL 进阶题库20 道题 参考答案 一张可直接跑的电商测试库而且每道题的答案都真实执行过、运行结果列名/行数/数据原样附在包里还配了 40 道面试题。在 CSDN 下载里搜索「SQL实战进阶」即可找到。
