使用纯Oracle SQL的任何更好的Fibonacci系列生成器？

发布时间：2021-01-24 12:08 所属栏目：[站长百科] 来源：网络整理

导读：我想知道是否有任何方法可以生成斐波那契数字,这个数字在简单和效率方面超过了我写的： WITH d (seq) AS (SELECT LEVEL FROM DUAL CONNECT BY LEVEL 195)SELECT seq,fibFROM dMODEL DIMENSION BY(seq) MEASURES(0 AS fib) RULES (fib [1] = 0,fib [2] = 1,

我想知道是否有任何方法可以生成斐波那契数字,这个数字在简单和效率方面超过了我写的：

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不是任意选择的,较高的值会使算法失去精度,因此我决定不包括它们以便仅保留正确的结果.

解决方法

在简单方面,查询可以依赖于MODEL的内置功能(ITERATE()和ITERATION_NUMBER)：

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
  )
;

【免责声明】本站内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。