金融交易统计 SQL 进阶实战

金融交易统计SQL进阶实战场景：股票/基金/数字货币/支付清算通用，难度从中级→高阶→业务专家级。全部基于MySQL8.0语法（CTE、窗口函数、行号、聚合、风控统计）。一、业务场景与表结构（真实金融系统标准）1.交易表示例（trade）sqlCREATETABLEtrade(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULL,--用户IDsymbolVARCHAR(20)NOTNULL,--交易对/股票代码dirENUM('BUY','SELL')NOTNULL,--买卖方向priceDECIMAL(18,6)NOTNULL,--成交价格amountDECIMAL(18,6)NOTNULL,--成交量total_amtDECIMAL(18,6)NOTNULL,--成交金额=price*amounttsDATETIMENOTNULL,--成交时间statusTINYINTNOTNULL--1成功0失败2撤单);2.用户资产表（user_asset）sqlCREATETABLEuser_asset(user_idBIGINTPRIMARYKEY,balanceDECIMAL(18,6)DEFAULT0,--可用余额frozenDECIMAL(18,6)DEFAULT0,--冻结金额update_timeDATETIME);二、中级实战（业务常用统计）1.统计每个用户总买入、总卖出、净交易额sqlSELECTuser_id,SUM(CASEWHENdir='BUY'THENtotal_amtELSE0END)ASbuy_amt,SUM(CASEWHENdir='SELL'THENtotal_amtELSE0END)ASsell_amt,SUM(CASEWHENdir='BUY'THENtotal_amtELSE-total_amtEND)ASnet_amtFROMtradeWHEREstatus=1GROUPBYuser_id;2.按小时统计交易量（K线/清算常用）sqlSELECTDATE_FORMAT(ts,'%Y-%m-%d%H:00:00')AShour,symbol,COUNT(*)AStrade_count,SUM(amount)AStotal_volume,SUM(total_amt)AStotal_valueFROMtradeWHEREstatus=1GROUPBYhour,symbolORDERBYhour;3.找出单日交易额TOP10用户sqlSELECTuser_id,DATE(ts)ASdt,SUM(total_amt)ASday_amtFROMtradeWHEREstatus=1GROUPBYuser_id,dtORDERBYday_amtDESCLIMIT10;三、进阶级（窗口函数实战，金融高频）4.每用户最近3笔交易（回访/风控/审计）sqlWITHranked_tradeAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYtsDESC)ASrnFROMtradeWHEREstatus=1)SELECT*FROMranked_tradeWHERErn<=3;5.计算每笔交易的上一笔价格、涨跌幅（量化策略常用）sqlWITHtAS(SELECTsymbol,ts,price,LAG(price,1)OVER(PARTITIONBYsymbolORDERBYts)ASprev_priceFROMtradeWHEREstatus=1)SELECT*,ROUND((price-prev_price)/prev_price*100,4)ASchange_pctFROMt;6.每用户当日累计交易额（实时风控限额）sqlSELECTuser_id,DATE(ts)ASdt,ts,total_amt,SUM(total_amt)OVER(PARTITIONBYuser_id,DATE(ts)ORDERBYts)ASday_running_totalFROMtradeWHEREstatus=1;7.找出连续3笔交易盈利的用户（交易策略分析）sqlWITHtAS(SELECTuser_id,id,ts,CASEWHENLAG(price,1)OVER(PARTITIONBYuser_idORDERBYts)<priceTHEN1ELSE0ENDASis_profitFROMtradeWHEREstatus=1),t2AS(SELECT*,SUM(is_profit)OVER(PARTITIONBYuser_idORDERBYtsROWSBETWEEN2PRECEDINGANDCURRENTROW)ASwin_3FROMt)SELECTDISTINCTuser_idFROMt2WHEREwin_3=3;四、高阶实战（复杂业务统计）8.计算每个用户的胜率、平均盈亏、最大单笔盈利sqlSELECTuser_id,COUNT(*)AStotal_trades,SUM(CASEWHEN(dir='BUY'ANDLEAD(price,1)OVERw>price)OR(dir='SELL'ANDLAG(price,1)OVERw>price)THEN1ELSE0END)ASwin_trades,ROUND(SUM(CASEWHEN(dir='BUY'ANDLEAD(price,1)OVERw>price)OR(dir='SELL'ANDLAG(price,1)OVERw>price)THENtotal_amtELSE0END)/SUM(total_amt)*100,2)ASwin_rate,MAX(total_amt)ASmax_single_profitFROMtradeWHEREstatus=1WINDOWwAS(PARTITIONBYuser_idORDERBYts)GROUPBYuser_id;9.找出异常交易：1小时内交易额突增300%（反洗钱/风控）sqlWITHh1AS(SELECTuser_id,DATE_FORMAT(ts,'%Y-%m-%d%H')AShour,SUM(total_amt)AShour_amt,LAG(SUM(total_amt),1)OVER(PARTITIONBYuser_idORDERBYDATE_FORMAT(ts,'%Y-%m-%d%H'))ASprev_hour_amtFROMtradeWHEREstatus=1GROUPBYuser_id,hour)SELECT*FROMh1WHEREprev_hour_amt>0ANDhour_amt/prev_hour_amt>=3;10.日内交易滑点分析（量化核心指标）滑点=实际成交价-理论/中间价sqlSELECTsymbol,AVG(ABS(price-(MAX(price)OVER(PARTITIONBYsymbol,DATE(ts))+MIN(price)OVER(PARTITIONBYsymbol,DATE(ts)))/2))ASslippageFROMtradeWHEREstatus=1GROUPBYsymbol;11.统计用户交易活跃度：日均交易次数、频率sqlWITHuser_dailyAS(SELECTuser_id,DATE(ts)ASdt,COUNT(*)AScntFROMtradeWHEREstatus=1GROUPBYuser_id,dt)SELECTuser_id,COUNT(DISTINCTdt)ASactive_days,SUM(cnt)AStotal_trades,SUM(cnt)/COUNT(DISTINCTdt)ASavg_daily_tradesFROMuser_dailyGROUPBYuser_idORDERBYavg_daily_tradesDESC;五、专家级（多表联合+清算+余额计算）12.根据交易流水自动计算用户余额流水（核心清算逻辑）sqlWITHtxAS(SELECTuser_id,ts,SUM(CASEWHENdir='SELL'THENtotal_amtELSE-total_amtEND)ASchangeFROMtradeWHEREstatus=1GROUPBYuser_id,ts)SELECTt.user_id,t.ts,t.change,a.balance+SUM(t.change)OVER(PARTITIONBYt.user_idORDERBYt.ts)ASbalance_afterFROMtxtJOINuser_assetaONt.user_id=a.user_idORDERBYt.user_id,t.ts;13.找出对敲/自成交可疑账户（同一用户频繁买卖同标的）sqlSELECTuser_id,symbol,COUNT(DISTINCTdir)ASdir_count,COUNT(*)AStrade_countFROMtradeWHEREstatus=1GROUPBYuser_id,symbolHAVINGdir_count=2ANDtrade_count>=10ORDERBYtrade_countDESC;14.找出交易额最大的N个交易对，并计算市场占比sqlWITHsymAS(