使用纯Oracle SQL有更好的Fibonacci系列生成器吗? [英] Any better Fibonacci series generator using pure Oracle SQL?

问题描述

我想知道是否有任何方法可以产生比我写的斐波那契数简单而有效的数字:

I wonder if there is any way to generate Fibonacci numbers that beat in simplicity and efficiency this one I wrote:

WITH d (seq) AS
       (SELECT     LEVEL
        FROM       DUAL
        CONNECT BY LEVEL < 195)
SELECT   seq
        ,fib
FROM     d
MODEL
  DIMENSION BY(seq)
  MEASURES(0 AS fib)
  RULES
    (fib [1] = 0,
    fib [2] = 1,
    fib [seq BETWEEN 3 AND 194] = fib[CV(seq) - 2] + fib[CV(seq) - 1],
    fib [seq > 194] = NULL)
ORDER BY 1
/
Execution Plan
----------------------------------------------------------
Plan hash value: 2245903385

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED             |      |     1 |    13 |            |          |
|   2 |   VIEW                         |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<195)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       4798  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        194  rows processed

SQL>

注意:LEVEL < 195不是任意选择的，较高的值会使算法失去精度，因此我决定不包括它们，只是为了保持正确的结果.

Note: LEVEL < 195 was not arbitrarily chosen, higher values make the algorithm lose precision so I decided not to include them in order to keep correct results only.

推荐答案

就事物的简单性而言，查询可以依赖MODEL的内置功能(ITERATE ()和ITERATION_NUMBER):

on the simplicity side of things, the query can rely on the built in features (ITERATE () and ITERATION_NUMBER) of MODEL:

select * from dual
model
  dimension by (0 seq)
  measures (0 val)
  rules iterate (195) 
  (
     val[iteration_number] = val[iteration_number-1] + val[iteration_number-2],
     val[2] = 1, 
     val[1] = 0, 
     val[0] = 0
  )
;

这篇关于使用纯Oracle SQL有更好的Fibonacci系列生成器吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！