LINEAR_HISTOGRAM
描述
LINEAR_HISTOGRAM 函数用于描述数据分布情况,它使用“等宽”的分桶策略,并按照数据的值大小进行分桶。
语法
`LINEAR_HISTOGRAM(<expr>, DOUBLE <interval>[, DOUBLE <offset>)`
参数说明
参数 | 说明 |
---|---|
interval | 桶的宽度,支持类型为 TinyInt,SmallInt,Int,BigtInt,LargeInt,Float,Double,Decimal。 |
offset | 可选。默认为 0,范围是 [0, interval) ,支持类型为Double。 |
返回值
返回计算后的 JSON 类型的值。
举例
-- setup
create table histogram_test(
a int
) distributed by hash(a) buckets 1
properties ("replication_num"="1");
insert into histogram_test values
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (null);
select linear_histogram(a, 2) from histogram_test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| linear_histogram(a, 2) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"num_buckets":6,"buckets":[{"lower":0.0,"upper":2.0,"count":2,"acc_count":2},{"lower":2.0,"upper":4.0,"count":2,"acc_count":4},{"lower":4.0,"upper":6.0,"count":2,"acc_count":6},{"lower":6.0,"upper":8.0,"count":2,"acc_count":8},{"lower":8.0,"upper":10.0,"count":2,"acc_count":10},{"lower":10.0,"upper":12.0,"count":2,"acc_count":12}]} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
select linear_histogram(a, 2, 1) from histogram_test;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| linear_histogram(a, 2, 1) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"num_buckets":7,"buckets":[{"lower":-1.0,"upper":1.0,"count":1,"acc_count":1},{"lower":1.0,"upper":3.0,"count":2,"acc_count":3},{"lower":3.0,"upper":5.0,"count":2,"acc_count":5},{"lower":5.0,"upper":7.0,"count":2,"acc_count":7},{"lower":7.0,"upper":9.0,"count":2,"acc_count":9},{"lower":9.0,"upper":11.0,"count":2,"acc_count":11},{"lower":11.0,"upper":13.0,"count":1,"acc_count":12}]} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
select linear_histogram(a, 2, 1) from histogram_test where a is null;
+--------------------------------+
| linear_histogram(a, 2, 1) |
+--------------------------------+
| {"num_buckets":0,"buckets":[]} |
字段说明:
- `num_buckets`:桶的数量。
- `buckets`:直方图所包含的桶。
- `lower`:桶的下界。(包含在内)
- `upper`:桶的上界。(不包含在内)
- `count`:桶内包含的元素数量。
- `acc_count`:前面桶与当前桶元素的累计总量。