DolphinDB实时排名计算：Top-N实时排行

目录摘要一、排名计算概述1.1 排名场景1.2 排名类型1.3 排名函数二、排名函数2.1 基本排名2.2 分组排名2.3 百分位排名三、Top-N计算3.1 Top-N查询3.2 分组Top-N3.3 实时Top-N四、动态排序4.1 实时排序4.2 多字段排序4.3 动态排名更新五、排名变化追踪5.1 排名变化检测5.2 排名历史5.3 排名趋势六、多维度排名6.1 多指标排名6.2 分组多维度排名6.3 时间窗口排名七、实战案例7.1 完整实时排名系统八、总结参考资料摘要本文深入讲解DolphinDB实时排名计算技术。从排名函数到Top-N计算从实时排行到动态排序从多维度排名到排名变化追踪全面介绍实时排名计算的核心方法。通过丰富的代码示例帮助读者掌握Top-N实时排行的核心技能。一、排名计算概述1.1 排名场景排名应用设备排名排名结果产品排名区域排名1.2 排名类型类型说明Top-N前N名Bottom-N后N名百分位排名百分比排名分组排名分组内排名1.3 排名函数函数说明rank排名有间隙dense_rank排名无间隙row_number行号percent_rank百分比排名二、排名函数2.1 基本排名//基本排名defbasicRank(data,valueCol){returnselect*,rank()over(order byeval(valueCol)desc)asrankfromdata}//紧凑排名defdenseRank(data,valueCol){returnselect*,dense_rank()over(order byeval(valueCol)desc)asrankfromdata}//行号defrowNumber(data,orderCol){returnselect*,row_number()over(order byeval(orderCol))asrow_numfromdata}2.2 分组排名//分组排名defgroupRank(data,groupCol,valueCol){returnselect*,rank()over(partition byeval(groupCol)order byeval(valueCol)desc)asrankfromdata}//使用示例 ttable([A,A,A,B,B,B]asgroup,[100,90,80,95,85,75]asvalue)resultgroupRank(t,group,value)2.3 百分位排名//百分位排名defpercentRank(data,valueCol){returnselect*,percent_rank()over(order byeval(valueCol))aspct_rankfromdata}三、Top-N计算3.1 Top-N查询//Top-N查询deftopN(data,valueCol,n10){returnselect top n*fromdata order byeval(valueCol)desc}//Bottom-N查询defbottomN(data,valueCol,n10){returnselect top n*fromdata order byeval(valueCol)}3.2 分组Top-N//分组Top-NdefgroupTopN(data,groupCol,valueCol,n5){returnselect*from(select*,rank()over(partition byeval(groupCol)order byeval(valueCol)desc)asrankfromdata)where rankn}3.3 实时Top-N//实时Top-N计算 share table(1:0,device_idtemperaturerank,[SYMBOL,DOUBLE,INT])astop_n_resultdefcalculateRealtimeTopN(){//获取最新数据 dataselect device_id,last(temperature)astemperaturefromsensor_stream where timestampnow()-60000group by device_id//计算排名 rankedselect*,rank()over(order by temperature desc)asrankfromdata//取Top10top10select top10*fromranked order by rank//更新结果 truncate(top_n_result)top_n_result.append!(top10)}四、动态排序4.1 实时排序//实时排序defrealtimeSort(data,sortCol,orderdesc){if(orderdesc){returnselect*fromdata order byeval(sortCol)desc}else{returnselect*fromdata order byeval(sortCol)}}4.2 多字段排序//多字段排序defmultiColumnSort(data,sortCols,orders){//构建排序语句 orderByfor(iin0..sortCols.size()){if(i0){orderBy, }orderBysortCols[i] orders[i]}returnselect*fromdata order byeval(orderBy)}4.3 动态排名更新//动态排名更新 share table(1:0,device_idvaluerankupdate_time,[SYMBOL,DOUBLE,INT,TIMESTAMP])asdynamic_rankdefupdateDynamicRank(){while(true){//获取最新值 dataselect device_id,last(temperature)asvaluefromsensor_stream where timestampnow()-60000group by device_id//计算排名 rankedselect device_id,value,rank()over(order by value desc)asrank,now()asupdate_timefromdata//更新 truncate(dynamic_rank)dynamic_rank.append!(ranked)sleep(5000)}}submitJob(dynamic_rank,动态排名,updateDynamicRank)五、排名变化追踪5.1 排名变化检测//排名变化表 share table(1:0,device_idold_ranknew_rankchangechange_time,[SYMBOL,INT,INT,INT,TIMESTAMP])asrank_change//检测排名变化defdetectRankChange(oldRank,newRank){for(deviceIdinoldRank.device_id){oldPosexecrankfromoldRank where device_iddeviceId newPosexecrankfromnewRank where device_iddeviceIdif(oldPos.size()0andnewPos.size()0){if(oldPos[0]!newPos[0]){insert into rank_change values(deviceId,oldPos[0],newPos[0],newPos[0]-oldPos[0],now())}}}}5.2 排名历史//排名历史表 share table(1:0,record_timedevice_idvaluerank,[TIMESTAMP,SYMBOL,DOUBLE,INT])asrank_history//记录排名历史defrecordRankHistory(ranked){for(rowinranked){insert into rank_history values(now(),row.device_id,row.value,row.rank)}}5.3 排名趋势//排名趋势分析defrankTrend(deviceId,periods10){returnselect record_time,rankfromrank_history where device_iddeviceId order by record_time desc limit periods}六、多维度排名6.1 多指标排名//多指标排名defmultiMetricRank(data,metrics,weights){//计算综合得分 score0for(iin0..metrics.size()){scoredata[metrics[i]]*weights[i]}data[score]scorereturnselect*,rank()over(order by score desc)asrankfromdata}6.2 分组多维度排名//分组多维度排名defgroupMultiMetricRank(data,groupCol,metrics,weights){//计算综合得分 score0for(iin0..metrics.size()){//归一化 maxValmax(data[metrics[i]])minValmin(data[metrics[i]])normalized(data[metrics[i]]-minVal)/(maxVal-minVal)scorenormalized*weights[i]}data[score]scorereturnselect*,rank()over(partition byeval(groupCol)order by score desc)asrankfromdata}6.3 时间窗口排名//时间窗口排名deftimeWindowRank(data,timeWindow3600000){returnselect device_id,bar(timestamp,timeWindow)aswindow,avg(temperature)asavg_temp,rank()over(partition by bar(timestamp,timeWindow)order by avg(temperature)desc)asrankfromdata group by device_id,bar(timestamp,timeWindow)}七、实战案例7.1 完整实时排名系统//实时排名计算系统//1.创建数据流 share streamTable(100000:0,device_idtimestamptemperaturehumiditypressure,[SYMBOL,TIMESTAMP,DOUBLE,DOUBLE,DOUBLE])assensor_stream enableTablePersistence(sensor_stream,true,true,1000000)//2.创建排名结果表 share table(1:0,device_idtemperaturerankupdate_time,[SYMBOL,DOUBLE,INT,TIMESTAMP])astemperature_rank share table(1:0,device_idhumidityrankupdate_time,[SYMBOL,DOUBLE,INT,TIMESTAMP])ashumidity_rank//3.排名计算任务defrankTask(){while(true){nownow()//获取最新数据 dataselect device_id,last(temperature)astemperature,last(humidity)ashumidityfromsensor_stream where timestampnow-60000group by device_idif(data.rows()0){//温度排名 tempRankselect device_id,temperature,rank()over(order by temperature desc)asrank,nowasupdate_timefromdata truncate(temperature_rank)temperature_rank.append!(tempRank)//湿度排名 humidRankselect device_id,humidity,rank()over(order by humidity desc)asrank,nowasupdate_timefromdata truncate(humidity_rank)humidity_rank.append!(humidRank)}sleep(5000)}}submitJob(rank_task,排名计算,rankTask)//4.Top-N接口defgetTopDevices(metric,n10){if(metrictemperature){returnselect top n*fromtemperature_rank order by rank}elseif(metrichumidity){returnselect top n*fromhumidity_rank order by rank}returnnull}addFunctionView(getTopDevices)//5.模拟数据defgenerateMockData(){while(true){datatable(take(1..20,20)asdevice_id,take(now(),20)astimestamp,rand(20.0..40.0,20)astemperature,rand(40.0..80.0,20)ashumidity,rand(1000.0..1020.0,20)aspressure)sensor_stream.append!(data)sleep(5000)}}submitJob(mock_data,模拟数据,generateMockData)print(实时排名计算系统启动完成)八、总结本文详细介绍了DolphinDB实时排名计算排名函数rank、dense_rank、row_number、percent_rankTop-N计算Top-N查询、分组Top-N、实时Top-N动态排序实时排序、多字段排序、动态更新排名追踪变化检测、排名历史、排名趋势多维度排名多指标排名、分组排名、时间窗口排名思考题如何处理排名相同的情况如何优化大规模数据的排名计算如何实现排名的实时推送参考资料DolphinDB窗口函数DolphinDB排序函数