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