مساله:
بله باز هم فیبوناچی! اما این بار با SQL.
شما باید select ای بنویسید که 90 عبارت اول فیبوناچی را تولید کند. نام ستون باید number باشد.
دنباله فیبوناچی به صورت زیر است:
x
1
1
0, 1, 1, 2, 3, 5, 8, 13, ..., 89, 144, 233, 377, ...
که :
1
4
1
f(0) = 0
2
f(1) = 1
3
...
4
f(n) = f(n-1) + f(n-2)
Description:
Yes it’s Fibonacci yet again ! But this time it’s SQL.
You need to create a select statement which will produce first 90 Fibonnacci numbers. The column name is – number
Fibbonaccii sequence is:
Java
1
2
1
0, 1, 1, 2, 3, 5, 8, 13, ..., 89, 144, 233, 377, ...
2
where
Java
1
5
1
f(0) = 0
2
f(1) = 1
3
...
4
f(n) = f(n-1) + f(n-2)
5
Have fun!
SQL
1
7
1
WITH RECURSIVE fib(number,n2) AS (
2
SELECT 0::bigint,1::bigint
3
UNION ALL
4
SELECT n2::bigint,number+n2::bigint
5
FROM fib
6
)
7
SELECT number FROM fib LIMIT 90
SQL
1
12
12
1
select * from (
2
values
3
(0), (1), (1), (2), (3), (5), (8), (13), (21), (34),
4
(55), (89), (144), (233), (377), (610), (987), (1597), (2584), (4181),
5
(6765), (10946), (17711), (28657), (46368), (75025), (121393), (196418), (317811), (514229),
6
(832040), (1346269), (2178309), (3524578), (5702887), (9227465), (14930352), (24157817), (39088169), (63245986),
7
(102334155), (165580141), (267914296), (433494437), (701408733), (1134903170), (1836311903), (2971215073), (4807526976), (7778742049),
8
(12586269025), (20365011074), (32951280099), (53316291173), (86267571272), (139583862445), (225851433717), (365435296162), (591286729879), (956722026041),
9
(1548008755920), (2504730781961), (4052739537881), (6557470319842), (10610209857723), (17167680177565), (27777890035288), (44945570212853), (72723460248141), (117669030460994),
10
(190392490709135), (308061521170129), (498454011879264), (806515533049393), (1304969544928657), (2111485077978050), (3416454622906707), (5527939700884757), (8944394323791464), (14472334024676221),
11
(23416728348467685), (37889062373143906), (61305790721611591), (99194853094755497), (160500643816367088), (259695496911122585), (420196140727489673), (679891637638612258), (1100087778366101931), (1779979416004714189)
12
) as fibonacci (number)
SQL
1
90
90
1
select 0 as number union all
2
select 1 as number union all
3
select 1 as number union all
4
select 2 as number union all
5
select 3 as number union all
6
select 5 as number union all
7
select 8 as number union all
8
select 13 as number union all
9
select 21 as number union all
10
select 34 as number union all
11
select 55 as number union all
12
select 89 as number union all
13
select 144 as number union all
14
select 233 as number union all
15
select 377 as number union all
16
select 610 as number union all
17
select 987 as number union all
18
select 1597 as number union all
19
select 2584 as number union all
20
select 4181 as number union all
21
select 6765 as number union all
22
select 10946 as number union all
23
select 17711 as number union all
24
select 28657 as number union all
25
select 46368 as number union all
26
select 75025 as number union all
27
select 121393 as number union all
28
select 196418 as number union all
29
select 317811 as number union all
30
select 514229as number union all
31
select 832040 as number union all
32
select 1346269 as number union all
33
select 2178309 as number union all
34
select 3524578 as number union all
35
select 5702887 as number union all
36
select 9227465 as number union all
37
select 14930352 as number union all
38
select 24157817 as number union all
39
select 39088169 as number union all
40
select 63245986 as number union all
41
select 102334155as number union all
42
select 165580141 as number union all
43
select 267914296 as number union all
44
select 433494437as number union all
45
select 701408733 as number union all
46
select 1134903170 as number union all
47
select 1836311903 as number union all
48
select 2971215073 as number union all
49
select 4807526976 as number union all
50
select 7778742049 as number union all
51
select 12586269025 as number union all
52
select 20365011074 as number union all
53
select 32951280099 as number union all
54
select 53316291173 as number union all
55
select 86267571272 as number union all
56
select 139583862445 as number union all
57
select 225851433717 as number union all
58
select 365435296162 as number union all
59
select 591286729879 as number union all
60
select 956722026041 as number union all
61
select 1548008755920 as number union all
62
select 2504730781961 as number union all
63
select 4052739537881 as number union all
64
select 6557470319842 as number union all
65
select 10610209857723 as number union all
66
select 17167680177565 as number union all
67
select 27777890035288 as number union all
68
select 44945570212853 as number union all
69
select 72723460248141 as number union all
70
select 117669030460994 as number union all
71
select 190392490709135 as number union all
72
select 308061521170129 as number union all
73
select 498454011879264 as number union all
74
select 806515533049393 as number union all
75
select 1304969544928657 as number union all
76
select 2111485077978050 as number union all
77
select 3416454622906707 as number union all
78
select 5527939700884757 as number union all
79
select 8944394323791464 as number union all
80
select 14472334024676221 as number union all
81
select 23416728348467685 as number union all
82
select 37889062373143906 as number union all
83
select 61305790721611591 as number union all
84
select 99194853094755497 as number union all
85
select 160500643816367088 as number union all
86
select 259695496911122585 as number union all
87
select 420196140727489673 as number union all
88
select 679891637638612258 as number union all
89
select 1100087778366101931 as number union all
90
select 1779979416004714189 order by number;
SQL
1
17
17
1
CREATE OR REPLACE FUNCTION comb(n integer, k integer) RETURNS decimal AS $$
2
BEGIN
3
RETURN (n!)/((k!)*((n-k)!));
4
END;
5
$$ LANGUAGE plpgsql;
6
7
8
CREATE FUNCTION f(n integer) RETURNS numeric AS $$
9
SELECT
10
SUM(comb(n-k-1,k))::numeric AS result
11
FROM generate_series(0,(floor((n-1)/2))::integer) k;
12
$$ LANGUAGE SQL;
13
14
SELECT 0 AS number
15
UNION ALL
16
SELECT f(i)::bigint AS number
17
FROM generate_series(1,89) i;
SQL
1
15
15
1
CREATE TABLE numbers (
2
id INTEGER,
3
number bigint
4
);
5
6
INSERT INTO numbers (id, number) VALUES (1, 0);
7
INSERT INTO numbers (id, number) VALUES (2, 1);
8
DO $$
9
BEGIN
10
FOR counter IN 3..90 LOOP
11
INSERT INTO numbers (id, number) VALUES (counter, (SELECT SUM(number) FROM numbers n WHERE n.id = counter - 1 OR n.id = counter - 2));
12
END LOOP;
13
END; $$;
14
15
SELECT number FROM numbers
SQL
1
18
18
1
create function fib(n int) returns setof bigint
2
as $$
3
declare
4
a bigint := 0;
5
b bigint := 1;
6
m bigint := 0;
7
i int := 0;
8
begin
9
while i < n loop
10
return next a;
11
m := a;
12
a := b;
13
b := b + m;
14
i := i + 1;
15
end loop;
16
end $$
17
language plpgsql;
18
select * from fib(90) as number