@H_502_1@比如我们要计算10! @H_502_1@
1、 可以用sql给它展开:
t_girl=# select 1*2*3*4*5*6*7*8*9*10 as mutiply_10; mutiply_10 ------------ 3628800 (1 row) Time: 0.854 ms
2、 用WITH递归
t_girl=# with recursive g(m,n) as t_girl-# (select 1 m,10 n t_girl(# union all t_girl(# select m*n,(n-1) n from g where n > 1 t_girl(# ) t_girl-# select max(m) as factorial_10 from g; factorial_10 -------------- 3628800 (1 row) Time: 3.893 ms
3、 用简单的函数来展开
create or replace function func_get_factorial( f_number int ) returns bigint as $ytt$ declare i int :=1; declare v_result bigint := 1; begin for i in 1 .. f_number loop v_result := v_result * i; end loop; return v_result; end; $ytt$ language plpgsql; t_girl=# select func_get_factorial(10) as factorial_10; factorial_10 -------------- 3628800 (1 row) Time: 1.022 ms
4、 用游标和序列函数generate_series来展开
create or replace function func_get_factorial2( f_number int ) returns bigint as $ytt$ declare cs1 cursor for select n from generate_series(1,f_number,1) as g(n); declare v_result bigint := 1; declare v_n bigint := 0; begin open cs1; loop fetch cs1 into v_n; exit when not found; v_result := v_result * v_n; end loop; close cs1; return v_result; end; $ytt$ language plpgsql; t_girl=# select func_get_factorial2(10) factorial_10; factorial_10 -------------- 3628800 (1 row) Time: 2.238 ms t_girl=#
5、 用自定义python函数
create or replace function func_get_factorial_py( f_number int ) returns bigint as $ytt$ m = 1 n = 1 for i in range(1,f_number+1): m = m * i n = m return n $ytt$ language plpythonu; t_girl=# select func_get_factorial_py(10) factorial_10; factorial_10 -------------- 3628800 (1 row) Time: 1.060 ms