SQL 实战复杂查询（多表连接 + 窗口函数）编写、调优全套指南

SQL实战复杂查询（多表连接+窗口函数）编写、调优全套指南业务真实场景（IoT电梯数据、订单、设备告警），包含：规范写法、易错坑、执行计划分析、优化手段。前置约定推荐环境：PG14+/MySQL8.0（支持窗口函数）业务模拟表（电梯IoT场景，下文所有SQL基于这几张表）sql--电梯设备表CREATETABLEelevator(elev_idBIGINTPRIMARYKEY,elev_nameVARCHAR(100),area_idINT,--区域IDinstall_timeTIMESTAMP);--电梯告警表CREATETABLEelev_alarm(alarm_idBIGINTPRIMARYKEY,elev_idBIGINT,alarm_typeINT,alarm_levelINT,--1紧急2重要3一般create_timeTIMESTAMP,handle_timeTIMESTAMP);--设备运行指标时序表CREATETABLEelev_metric(idBIGSERIALPRIMARYKEY,elev_idBIGINT,speedNUMERIC,load_rateNUMERIC,collect_timeTIMESTAMP);--区域表CREATETABLEarea(area_idINTPRIMARYKEY,area_nameVARCHAR(50));一、多表连接实战（JOIN）1.JOIN分类核心区别INNERJOIN：两边匹配数据才返回（交集）LEFTJOIN：左表全部保留，右表无匹配补NULL（最常用）RIGHTJOIN：右表全部保留FULLOUTERJOIN：左右全部，缺的补NULL（PG支持，MySQL不原生支持）CROSSJOIN：笛卡尔积，业务禁止随便使用❌老旧错误写法（隐式连接，禁止使用！难以维护、容易错转笛卡尔积）sql--不推荐！SELECT*FROMelevator,elev_alarmWHEREelevator.elev_id=elev_alarm.elev_id;✅标准显式JOIN写法sqlSELECT*FROMelevatoreINNERJOINelev_alarmalONe.elev_id=al.elev_id;2.实战案例1：左连接+统计各电梯告警数量（含无告警电梯）需求：列出所有电梯名称、所属区域、告警总数；没有告警的电梯也要展示，告警数=0sqlSELECTe.elev_id,e.elev_name,a.area_name,COUNT(al.alarm_id)ASalarm_totalFROMelevatoreLEFTJOINareaaONe.area_id=a.area_idLEFTJOINelev_alarmalONe.elev_id=al.elev_idGROUPBYe.elev_id,e.elev_name,a.area_name;高频坑：LEFTJOIN后WHERE过滤右表字段错误示范：sql--错误！alarm_level=1把NULL行过滤，LEFTJOIN失效等价INNERJOINSELECT*FROMelevatoreLEFTJOINelev_alarmalONe.elev_id=al.elev_idWHEREal.alarm_level=1;✅修正：条件放到ON子句sqlSELECT*FROMelevatoreLEFTJOINelev_alarmalONe.elev_id=al.elev_idANDal.alarm_level=1;--过滤条件写在JOINON内3.实战案例2：多表级联关联+分页需求：查询A区域所有电梯最近产生的紧急告警sqlSELECTe.elev_name,al.alarm_id,al.create_timeFROMelevatoreJOINareaarONe.area_id=ar.area_idLEFTJOINelev_alarmalONe.elev_id=al.elev_idANDal.alarm_level=1WHEREar.area_name='一号园区'ORDERBYal.create_timeDESCLIMIT20OFFSET0;4.多表JOIN通用优化原则小表驱动大表：FROM顺序尽量小表在前（优化器大部分自动调整，但规范优先）JOIN条件字段必须建立索引：elev_id、area_id禁止JOIN字段使用函数：ONfunc(e.elev_id)=al.elev_id会失效索引尽量减少SELECT*，只查询需要字段，减少内存IO大表关联先过滤缩小数据集（子查询/CTE提前WHERE）二、窗口函数（重点！复杂报表、排名、同比环比、取每组第一条）基础语法sql<聚合/排名函数>()OVER(PARTITIONBY分组字段ORDERBY排序字段ROWS/RANGE窗口范围)常用窗口函数清单1）排名类ROW_NUMBER()：每组连续编号，相同值序号不重复RANK()：并列排名，跳号1,1,3DENSE_RANK()：并列排名，不跳号1,1,22）偏移取值类（上下行取数）LAG(col,n)：取分组内上第N行数据LEAD(col,n)：取分组内下第N行数据3）聚合窗口函数SUM()OVER()/AVG()OVER()/MAX()OVER()和GROUPBY区别：不会合并多行，保留原始明细同时输出聚合结果实战场景1：【最高频】取每个电梯最新一条告警传统方案：关联子查询，性能差；窗口函数最优解sqlWITHalarm_rnAS(SELECTelev_id,alarm_id,alarm_type,create_time,--按电梯分组，时间倒序编号ROW_NUMBER()OVER(PARTITIONBYelev_idORDERBYcreate_timeDESC)ASrnFROMelev_alarm)SELECT*FROMalarm_rnWHERErn=1;--每组第一条=最新告警区分三个排名函数场景：只需要一条记录：ROW_NUMBER()需要并列全部展示：DENSE_RANK()实战场景2：LAG实现同比，对比本次指标和上一次采集数据需求：每个电梯，对比当前负载率和上一条采集负载率，计算差值sqlSELECTelev_id,collect_time,load_rate,LAG(load_rate,1)OVER(PARTITIONBYelev_idORDERBYcollect_time)ASlast_load_rate,load_rate-LAG(load_rate,1)OVER(PARTITIONBYelev_idORDERBYcollect_time)ASload_diffFROMelev_metricWHEREcollect_time>='2026-08-0100:00:00';实战场景3：分组内聚合，明细附带分组汇总需求：展示每条告警，同时附带该电梯告警总数sqlSELECTelev_id,alarm_id,create_time,COUNT(alarm_id)OVER(PARTITIONBYelev_id)ASelev_alarm_countFROMelev_alarm;👉GROUPBY会压缩行；窗口函数明细与聚合共存，报表开发利器。实战场景4：窗口范围控制（滚动窗口、滑动平均）计算每个测点前后5条数据的滑动平均（时序数据常用，适配TDengine/PG时序查询）sqlSELECTelev_id,collect_time,load_rate,AVG(load_rate)OVER(PARTITIONBYelev_idORDERBYcollect_timeROWSBETWEEN2PRECEDINGAND2FOLLOWING)ASsliding_avg_loadFROMelev_metric;窗口函数常见误区WHERE不能直接使用窗口别名（执行顺序限制）❌错误sqlSELECTROW_NUMBER()OVER(...)rnFROMtableWHERErn=1✅正确：套CTE/子查询2.PARTITIONBY不要写过多字段，增大内存开销3.ORDERBY缺失：ROW_NUMBER结果随机不稳定！三、综合复杂SQL案例：多表JOIN+窗口函数混合业务需求：统计园区各电梯，当日告警；筛选每个电梯紧急告警最新3条，关联电梯名称、区域名称sqlWITHdaily_alarmAS(SELECTe.elev_id,e.elev_name,ar.area_name,al.alarm_id,al.alarm_type,al.create_time,ROW_NUMBER()OVER(PARTITIONBYal.elev_idORDERBYal.create_timeDESC)ASrnFROMelevatoreJOINareaarONe.area_id=ar.area_idLEFTJOINelev_alarmalONe.elev_id=al.elev_idWHEREal.alarm_level=1ANDal.create_time>=CURRENT_DATE)SELECT*FROMdaily_alarmWHERErn<=3ORDERBYarea_name,elev_id;四、复杂SQL系统化优化流程（工程实战标准步骤）步骤1：查看执行计划PostgreSQLsqlEXPLAINANALYZESELECT......;MySQLsqlEXPLAINSELECT......;重点观察关键词：SeqScan（全表扫描⚠️需要建索引）NestedLoop/HashJoin/MergeJoinSort（大量排序消耗内存，窗口函数ORDERBY容易触发）三种JOIN算法适用场景NestedLoop：小表驱动大表，有可用索引（最优）HashJoin：无索引、中大表关联（PG/MySQL8常用）MergeJoin：两边关联字段有序步骤2通用优化手段1.索引优化窗口查询高频索引模板：sql--针对PARTITIONBY+ORDERBY建立复合索引CREATEINDEXidx_alarm_elev_timeONelev_alarm(elev_id,create_timeDESC);窗口函数按elev_id分组、按时间排序，复合索引完美覆盖，避免内存排序。2.提前过滤数据，缩小计算窗口大表务必先用WHERE过滤时间范围，不要在外层过滤。时序数据尤其重要。3.CTE/子查询合理使用PG12+支持CTE内联；低版本不要滥用多层嵌套CTE，避免性能退化。4.避免超大窗口PARTITION后单分组数据几十万行→数据库需要在内存维护窗口，容易OOM。解决方案：按时间分片查询。步骤3窗口函数专项优化尽可能利用复合索引消除Sort操作（EXPLAIN看不到Sort为最优）同一OVER条件的多个窗口函数可以复用窗口定义sql--优化写法，统一窗口WITHwAS(PARTITIONBY