需求场景:读取期货5分钟数据表,判断每日各个品种的主次合约
解决方案:只要根据每日第一个5分钟线的持仓量排序就能获取当日主次合约
代码实现:根据日期(精确到日)、品种进行分组,在每个分组中按照日期(精确到分钟)、持仓量排序,取排序前面2行的合约则为主次合约。
首先简单增加一个排序列以及日期 看看效果
set @order_num = 0; select *,convert(Date, date) as Day,@order_num:=@order_num+1 rownum from 2021_05 order by productid,Date,openinterest desc
set @order_num = 0; set @product= ''; select *,convert(Date, date) as Day,case when @product=ProductID then @order_num:=@order_num+1 else @order_num:=1 end rownum,@product:=ProductID from 2021_05 order by productid,Date,openinterest desc
set @order_num = 0; set @product= ''; set @day=''; select Day,ProductID,InstrumentID from (select *,case when @product=ProductID and @day=Day then @order_num:=@order_num+1 else @order_num:=1 end rownum,@product:=ProductID,@day:=Day from (select a.*,b.Day from 2021_05 a inner join (select id,convert(Date, date) as Day from 2021_05)b on a.id=b.id)c order by productid,Date,openinterest desc)d where rownum<=2
性能方面还有很大优化空间 后续再继续修改