PostgreSQL 9.5新特性 width_bucket

postgres=# select width_bucket(-1, 0.0, 5.0, 5);  width_bucket  --------------             0 (1 row) postgres=# select width_bucket(5.0, 0.0, 5.0, 5);  width_bucket  --------------             6 (1 row) postgres=# select width_bucket(-0.0000001, 0.0, 5.0, 5);  width_bucket  --------------             0 (1 row) postgres=# select width_bucket(5.1, 0.0, 5.0, 5);  width_bucket  --------------             6 (1 row)

 

  在边界内：

 

postgres=# select width_bucket(0, 0.0, 5.0, 5);  width_bucket  --------------             1 (1 row) postgres=# select width_bucket(1, 0.0, 5.0, 5);  width_bucket  --------------             2 (1 row) postgres=# select width_bucket(1.9, 0.0, 5.0, 5);  width_bucket  --------------             2 (1 row) postgres=# select width_bucket(1.9999999, 0.0, 5.0, 5);  width_bucket  --------------             2 (1 row) postgres=# select width_bucket(2, 0.0, 5.0, 5);  width_bucket  --------------             3 (1 row) postgres=# select width_bucket(4.9999, 0.0, 5.0, 5);  width_bucket  --------------             5 (1 row)

 

  直接使用数组代表边界： 注意参数类型必须一致。

 

postgres=# select width_bucket(now(), array["yesterday", "today", "tomorrow"]::timestamptz[]);  width_bucket  --------------             2 (1 row) postgres=# select width_bucket(now(), array["yesterday", "today", "tomorrow"]::timestamp[]); ERROR:  function width_bucket(timestamp with time zone, timestamp without time zone[]) does not exist LINE 1: select width_bucket(now(), array["yesterday", "today", "tomo...                ^ HINT:  No function matches the given name and argument types. You might need to add explicit type casts. postgres=# select width_bucket(1,"{0,1,100,200,300}"::int[]);  width_bucket  --------------             2 (1 row)   边界表示如下，所以1落在第二个bucket。 [0,1) [1,100) [100,200) [200,300)

 

  [参考] 1. Http://www.postgresql.org/docs/devel/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE