entrust数据处理.dos

idate=2022.04.14
entrustTB=select * from loadTable("dfs://level_2", "entrust")where DateTime.date()=2022.04.14 and  SecurityID in ["000001.SZ","000040.SZ"]
tradeTB=select * from  loadTable("dfs://level_2", "trade")where DateTime.date()=2022.04.14 and  SecurityID in ["000001.SZ","000040.SZ"] and  ExecType!=52
/*
 * 计算深交所股票每分钟的买卖委托金额
 */
defg calcSZOrderValue(side, price, orderQty, tradePrice, orderType, bsFlag){
	price_ = iif(orderType =="50",price,NULL).nullFill(tradePrice)
	return sum(iif(side==bsFlag,price_*orderQty,NULL)).nullFill(0)
}
res=select calcSZOrderValue(side,price,orderQty,tradePrice,orderType,"B") as BuyOrderValue, 
calcSZOrderValue(side,price,orderQty,tradePrice,orderType,"S") as SellOrderValue
from aj(entrustTB,tradeTB,`SecurityID`ApplSeqNum) group by SecurityID,bar(DateTime,1m) as DateTime

/*
 * 买卖撤单金额
 */

entrustTB=select * from loadTable("dfs://level_2", "entrust")where DateTime.date()=2022.04.14 and  SecurityID in ["000001.SZ","000040.SZ"]
tradeTB=select * from   loadTable("dfs://level_2", "trade")where DateTime.date()=2022.04.14 and  SecurityID in ["000001.SZ","000040.SZ"] 

trade=select SecurityID,DateTime, max(BidApplSeqNum,OfferApplSeqNum) as ApplSeqNum,TradeQty from tradeTB where  ExecType=52
entrust=select  SecurityID,ApplSeqNum, Price,Side from entrustTB
select  sum(iif(side=="B",Price*TradeQty,NULL)).nullFill(0) as buywithdrawOrderValue,
sum(iif(side=="S",Price*TradeQty,NULL)).nullFill(0) as sellwithdrawOrderValue 
from lsj(trade,entrust,`SecurityID`ApplSeqNum) group by SecurityID,bar(DateTime,1m) as DateTime