PostgreSQL 9.5 new feature - width_bucket return the bucket number

1 minute read

背景

PostgreSQL 9.5 新增的一个函数width_bucket , 用来计算一个值在一个bucket范围内的位置信息,如果这个值超出了给定的范围,返回 0 或者 总buckets+1 。

bucket除了用数字来表示,还可以直接用数组来表示。

Function Return Type Description Example Result
width_bucket(operand dp, b1 dp, b2 dp, count int) int return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2; returns 0 or count+1 for an input outside the range width_bucket(5.35, 0.024, 10.06, 5) 3
width_bucket(operand numeric, b1 numeric, b2 numeric, count int) int return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2; returns 0 or count+1 for an input outside the range width_bucket(5.35, 0.024, 10.06, 5) 3
width_bucket(operand anyelement, thresholds anyarray) int return the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained width_bucket(now(), array[‘yesterday’, ‘today’, ‘tomorrow’]::timestamptz[]) 2

例子

0.0, 5.0是边界,其中包含0.0,但是不包含5.0, 最后一个参数表示一共分为5个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

Flag Counter

digoal’s 大量PostgreSQL文章入口