ITPub博客

首页 > 数据库 > Oracle > 巧用oracle正则表达式解决查询问题

巧用oracle正则表达式解决查询问题

原创 Oracle 作者:bestpaydata 时间:2016-04-28 21:29:29 0 删除 编辑

     业务人员需要对一张表中的交易数据,按照月份,统计2015年6月到2016年4月分月的交易笔数和总的交易金额。需求很简单,也非常明确,sql实现也非常简单。考虑到涉及的表比较大,有好几十个G,因此查询时采用了并行处理:

select /*+ parallel(t 18) */
 substr(t.XXX_DATE, 1, 6), sum(t.XXX_TRANS), count(*)
  from xxx_yyyy_txn_his t
 where to_date(t.XXX_DATE, 'yyyymmdd') >= date
 '2015-06-01't.XXX_TRANS
   and to_date(t.XXX_DATE, 'yyyymmdd') < date '2016-04-13'
 group by substr(t.XXX_DATE, 1, 6);

    实际运行上述sql,但是在运行10多秒后,却出现了如下报错:


    报 错很明确,ORA-01722表示有无效数字。看到该报错后,立即检查了表结构。出乎意料,交易金额字段XXX_TRANS确实使用的是VARCHAR2 类型。至此,可以比较肯定的是,上述的sql运行时,sum(t.XXX_TRANS)时,oracle进行了隐式转换,将字符转为了数字进行数学运算。 但是由于交易金额字段可能存在了非数字字符,因此隐式转换时报了ORA-01722错误。如果要使得上述sql能够正常运行,则必须找出有问题的记录。但 是由于表中的记录数目非常多,如果逐一排查记录,找出有问题的记录,则需要耗费太多的时间。

    由于业务人员只需要大概的数字,不需要 完全精确的结果,因此考虑使用case进行判断后,然后取值。但是由于该字段包含的字符不确定,所以使用普通的like判断无法实现需求。好在 Oracle提供了正则表达式,可以对XXX_TRANS进行基于正则表达式的判断,然后再确定其值,即:如果此字段包含非数字字符(不包括小数点),则 将结果记为0,否则使用原来的值。按照该想法,sql如下:

select /*+ parallel(t 18) */
 substr(t.XXX_DATE, 1, 6),
 sum(case
       when regexp_like(t.XXX_TRANS, '[^0-9\.]+') then
        '0'
       else
       t.XXX_TRANS
     end),
 count(*)
  from xxx_yyyy_txn_his t
 where to_date(t.XXX_DATE, 'yyyymmdd') >= date
 '2015-06-01'
   and to_date(t.XXX_DATE, 'yyyymmdd') < date '2016-04-13'
 group by substr(t.XXX_DATE, 1, 6) order by 1 asc

    其中红色部分即为正则表达式,各项解释如下:

1)[]表示一个集合,

2)^则表示对后面的字符集合取反,

3)0-9表示数字0到9

4)\.表示小数点

5)+则表示至少匹配一次

    综合在一起,该正则表达式就是表示,如果不是数字或小数点的字符出现,则结果匹配,也就是case表达式为真,此时将XXX_TRANS的值记为0。再次运行该sql,获得查询结果。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-2090346/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985700