How to select last 4 OHLCV values from each timeframe ?

Hi,

I have a table which have all 1min, 5min, 10min, 15min, 30min OHLCV values in it. I want to extract last 4 OHLCV values from each timeframe. Please guide me.

attachments-2022-01-GvAdp8tG61e807c6c04d3.png

here is the full excel file for your reference. ex1.zip

请先 登录 后评论

1 个回答

Yating Xie

solution1:Calculating the results for each timeframe separately

select symbol , ts_m1, om1, hm1,lm1,cm1,vm1 from t where isDuplicated([symbol, ts_m1], LAST)=false context by symbol limit -4

attachments-2022-01-vmcusy0H61efc40f0ce57.png

then, using "join" to concat two or more result tables

t1 = select symbol , ts_m1, om1, hm1,lm1,cm1,vm1 from t where isDuplicated([symbol, ts_m1], LAST)=false context by symbol order by ts_m1 limit -4
t2 = select symbol , ts_m5, om5, hm5,lm5,cm5,vm5 from t where isDuplicated([symbol, ts_m5], LAST)=false context by symbol order by ts_m5 limit -4
t1.join(select ts_m5, om5, hm5,lm5,cm5,vm5 from t2)

attachments-2022-01-d95hNqXJ61efc4dc3eff8.png


solution2: Using User-Defined Functions

def lastDistinct4Rows(v){
	tmp = sort(distinct(v))
	return tmp[tmp.size()-4:]
}

select symbol[symbol.size()-4:], lastDistinct4Rows(ts_m1),lastDistinct4Rows(ts_m5),lastDistinct4Rows(ts_m10),lastDistinct4Rows(ts_m15),	lastDistinct4Rows(ts_m30),lastDistinct4Rows(om1),lastDistinct4Rows(hm1),lastDistinct4Rows(lm1),lastDistinct4Rows(cm1),	lastDistinct4Rows(vm1),	lastDistinct4Rows(om5),lastDistinct4Rows(hm5),	lastDistinct4Rows(lm5),	lastDistinct4Rows(cm5),	lastDistinct4Rows(vm5),	lastDistinct4Rows(om10),lastDistinct4Rows(hm10),lastDistinct4Rows(lm10),lastDistinct4Rows(cm10),lastDistinct4Rows(vm10),lastDistinct4Rows(om15),lastDistinct4Rows(hm15),lastDistinct4Rows(lm15),lastDistinct4Rows(cm15),lastDistinct4Rows(vm15),lastDistinct4Rows(om30),lastDistinct4Rows(hm30),lastDistinct4Rows(lm30),lastDistinct4Rows(cm30),lastDistinct4Rows(vm30) from t where symbol = "BTCUSDT"

the result:

attachments-2022-01-J36ltjZY61efba3ecc3f6.png


请先 登录 后评论
  • 1 关注
  • 0 收藏,910 浏览
  • Vishvesh Upadhyay 提出于 2022-01-19 20:46

相似问题