PostgreSQL資料庫實現階乘方法幾個執行個體總結

來源:互聯網
上載者:User

PostgreSQL 功能龐大,對實現乘法這類運算有諸多的方法,今天我來簡單列舉下以下幾種便捷的途徑。
比如我們要計算10!

1、 可以用SQL給它展開:


    t_girl=# select 1*2*3*4*5*6*7*8*9*10 as multiply_10;
     multiply_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


相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.