以Postgresql为主,再聊聊资料库 PostgreSQL 提供精确分数的 pg_rational Extensio

在一般的运算中, 分数会使用浮点数来近似.PostgreSQL 有许多强大的 extension,提供了许多extension,让我们更方便.这个 extension 的作者同时也是 PostgREST的作者.https://github.com/begriffs/pg_rational安装方式很简单,解压, make , make install然后在 资料库中,使用 create extension 安装.我是使用指定 schema 的方式,安装在 public schemacreate extension pg_rational with schema public;commit;--接着来看安装以后, 新增了哪些运算子.查询了 pg_catalog, 语法在此从略.有兴趣的可以看我以前写的介绍 pg_catalog 及 hstore, array 等.+-----------------------------------+--------------+|               expr                |   oprcode    |+-----------------------------------+--------------+|   rational >=   ration = boolean  | rational_ge  ||   rational >    ration = boolean  | rational_gt  ||   rational =    ration = boolean  | rational_eq  ||   rational <>   ration = boolean  | rational_ne  ||   rational <=   ration = boolean  | rational_le  ||   rational <    ration = boolean  | rational_lt  ||   rational /    ration = rational | rational_div ||            -    ration = rational | rational_neg ||   rational -    ration = rational | rational_sub ||   rational +    ration = rational | rational_add ||   rational *    ration = rational | rational_mul |+-----------------------------------+--------------+(11 rows)新增的函数+--------+-----------------------+-----------+----------+--------------------+-------------+| schema |         proc          |   kind    | language |     arguments      | return_type |+--------+-----------------------+-----------+----------+--------------------+-------------+| public | max                   | AGGREGATE | internal | rational           | rational    || public | min                   | AGGREGATE | internal | rational           | rational    || public | rational_add          | FUNCTION  | c        | rational, rational | rational    || public | rational_cmp          | FUNCTION  | c        | rational, rational | int4        || public | rational_div          | FUNCTION  | c        | rational, rational | rational    || public | rational_eq           | FUNCTION  | c        | rational, rational | bool        || public | rational_ge           | FUNCTION  | c        | rational, rational | bool        || public | rational_gt           | FUNCTION  | c        | rational, rational | bool        || public | rational_hash         | FUNCTION  | c        | rational           | int4        || public | rational_intermediate | FUNCTION  | c        | rational, rational | rational    || public | rational_larger       | FUNCTION  | c        | rational, rational | rational    || public | rational_le           | FUNCTION  | c        | rational, rational | bool        || public | rational_lt           | FUNCTION  | c        | rational, rational | bool        || public | rational_mul          | FUNCTION  | c        | rational, rational | rational    || public | rational_ne           | FUNCTION  | c        | rational, rational | bool        || public | rational_neg          | FUNCTION  | c        | rational           | rational    || public | rational_out          | FUNCTION  | c        | rational           | cstring     || public | rational_out_float    | FUNCTION  | c        | rational           | float8      || public | rational_send         | FUNCTION  | c        | rational           | bytea       || public | rational_simplify     | FUNCTION  | c        | rational           | rational    || public | rational_smaller      | FUNCTION  | c        | rational, rational | rational    || public | rational_sub          | FUNCTION  | c        | rational, rational | rational    || public | sum                   | AGGREGATE | internal | rational           | rational    || public | tuple_to_rational     | FUNCTION  | sql      | ratt               | rational    |+--------+-----------------------+-----------+----------+--------------------+-------------+(24 rows)可以观察到有部分函数是 operator 呼叫的.两部分的差异为+-----------------------+|         proc          |+-----------------------+| max                   || min                   || rational_cmp          || rational_hash         || rational_intermediate || rational_larger       || rational_out          || rational_out_float    || rational_send         || rational_simplify     || rational_smaller      || sum                   || tuple_to_rational     |+-----------------------+(13 rows)max, min, sum 为聚合函数.-------接着来看一些基本使用select 1::rational / 3 * 3 = 1     , 1::rational / 4 * 4 = 1     , 1::rational / 5 * 5 = 1;+----------+----------+----------+| ?column? | ?column? | ?column? |+----------+----------+----------+| t        | t        | t        |+----------+----------+----------+(1 row)Time: 3.790 msselect '1/3'::rational + '2/7'     , '2/3'::rational - '1/3'     , rational_simplify('2/3'::rational - '1/3');     +----------+----------+-------------------+| ?column? | ?column? | rational_simplify |+----------+----------+-------------------+| 13/21    | 3/9      | 1/3               |+----------+----------+-------------------+(1 row)Time: 0.715 msselect '-1/2'::rational::float     , 0.263157894737::float::rational     , (1.0/3.0)::float::rational     , (1.0/4.0)::float::rational;+--------+----------+----------+----------+| float8 | rational | rational | rational |+--------+----------+----------+----------+|   -0.5 | 5/19     | 1/3      | 1/4      |+--------+----------+----------+----------+(1 row)Time: 2.526 msselect (i,i+1)::ratt      , 0 + (i,i+1)::ratt     , 1 + (i,i+1)::ratt     , (0.5)::float::rational + (i,i+1)::ratt  from generate_series(1,5) as i;+-------+----------+----------+----------+|  row  | ?column? | ?column? | ?column? |+-------+----------+----------+----------+| (1,2) | 1/2      | 3/2      | 4/4      || (2,3) | 2/3      | 5/3      | 7/6      || (3,4) | 3/4      | 7/4      | 10/8     || (4,5) | 4/5      | 9/5      | 13/10    || (5,6) | 5/6      | 11/6     | 16/12    |+-------+----------+----------+----------+(5 rows)Time: 0.892 ms简单介绍到此,先告一段落.