hive几种判断字符串开头的方法对比

select count(*) from tds_sk_his  t where dt='2020-10-27' and user_num like '0086%';      --50.508 seconds
select count(*) from tds_sk_his  t where dt='2020-10-27' and user_num rlike '^0086';     --46.483 seconds
select count(*) from tds_sk_his  t where dt='2020-10-27' and user_num REGEXP  '^0086';   --50.408 seconds
select count(*) from tds_sk_his  t where dt='2020-10-27' and  instr (user_num,'0086')=1; --56.551 seconds

 

从5400万数据中，筛选出以0086开头的75万数据，从结果上看，似乎rlike效率最高