digoal
2015-06-16
PostgreSQL , width_bucket , 归类 , 分级 , 阶梯
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
