使用NTILE拆分Oracle数据时MAX值小于MIN值的问题排查

采用NTILE百分位法拆分Oracle表数据大多有效，但使用以下查询按trxn_no将processed_trxns表拆分为15批次时，第5批次出现MAX(trxn_no)-1小于MIN(trxn_no)的异常：

SELECT batch_number, MIN(trxn_no), MAX(trxn_no) - 1, COUNT(1) AS batch_size
  FROM (SELECT trxn_no, NTILE(15) OVER (ORDER BY trxn_no) AS batch_number
          FROM processed_trxns)
 GROUP BY batch_number
 ORDER BY batch_number;

异常结果 #

1 1                  2028014   13978092
  2 2028015            2693634   13978091
  3 2693635            3171854   13978091
  4 3171855            3433231   13978091
**5 3433232             371815   13978091**
  6 371816             4080200   13978091
  7 4080201            4566643   13978091
  8 4566644            4983410   13978091
  9 4983411            5463275   13978091
 10 5463276            5947051   13978091
 11 5947052            6595154   13978091
 12 6595155            7271102   13978091
 13 7271104            8035539   13978091
 14 8035540            8852157   13978091
 15 8852158            9999998   13978091

期望结果 #

1 1           201262   13992990
   2 201263     2693256   13992990
   3 2693257        3171596   13992990
   4 3171597        3432904   13992990
   5 3432905        3718367   13992989
   6 3718368        4080955   13992989
   7 4080956        4567284   13992989
   8 4567285        4984142   13992989
   9 4984143        5464390   13992989
  10 5464391        5948080   13992989
  11 5948081        6596460   13992989
  12 6596461        7272562   13992989
  13 7272563        8036943   13992989
  14 8036944        8853310   13992989
  15 8853311        9999998   13992989

异常原因 #

核心问题是**trxn_no字段为字符串类型（如VARCHAR2）而非数值类型**，导致ORDER BY trxn_no执行的是字符串字典序排序，而非数值大小排序。

字符串排序时从左到右逐字符比较：比如"3433232"和"371816"，第一个字符都是'3'，第二个字符'4' < '7'，所以"3433232"作为字符串会排在"371816"之后，但数值上3433232远大于371816。NTILE基于这个错误的排序逻辑分配批次，就会把数值更大但字符串更小的trxn_no分到同一批次，最终出现批次内MIN(trxn_no)（数值大）大于MAX(trxn_no)（数值小）的情况，MAX(trxn_no)-1自然就小于MIN(trxn_no)。

解决方法 #

将排序逻辑改为按数值大小排序，修正后的SQL如下：

SELECT batch_number, MIN(trxn_no), MAX(trxn_no) - 1, COUNT(1) AS batch_size
  FROM (SELECT trxn_no, NTILE(15) OVER (ORDER BY TO_NUMBER(trxn_no)) AS batch_number
          FROM processed_trxns)
 GROUP BY batch_number
 ORDER BY batch_number;

内容的提问来源于stack exchange，提问作者Abhay Singh