width_bucket

Description

构造等宽直方图，其中直方图范围被划分为相同大小的区间，并在计算后返回表达式的值所在的桶号。该函数返回一个整数值或空值（如果任何输入为空值则返回空值）。

Syntax

INT width_bucket(Expr expr, T min_value, T max_value, INT num_buckets)

Arguments

expr - 创建直方图的表达式。此表达式必须计算为数值或可隐式转换为数值的值。

此值的范围必须为 -(2^53 - 1) 到 2^53 - 1 (含).

min_value 和 max_value - 表达式可接受范围的最低值点和最高值点。这两个参数必须为数值并且不相等。

最低值点和最高值点的范围必须为 -(2^53 - 1) to 2^53 - 1 (含)). 此外，最高值点与最低值点的差必须小于 2^53 (例如： abs(max_value - min_value) < 2^53).

num_buckets - 分桶的数量，必须是正整数值。将表达式中的一个值分配给每个存储桶，然后该函数返回相应的存储桶编号。

Returned value

返回表达式值所在的桶号。

当表达式超出范围时，函数返回规则如下：

如果表达式的值小于min_value返回0.

如果表达式的值大于或等于max_value返回num_buckets + 1.

如果任意参数为null返回null.

example

1. DROP TABLE IF EXISTS width_bucket_test;

3. CREATE TABLE IF NOT EXISTS width_bucket_test (
4. `k1` int NULL COMMENT "",
5. `v1` date NULL COMMENT "",
6. `v2` double NULL COMMENT "",
7. `v3` bigint NULL COMMENT ""
8. ) ENGINE=OLAP
9. DUPLICATE KEY(`k1`)
10. DISTRIBUTED BY HASH(`k1`) BUCKETS 1
11. PROPERTIES (
12. "replication_allocation" = "tag.location.default: 1",
13. "storage_format" = "V2"
14. );

16. INSERT INTO width_bucket_test VALUES (1, "2022-11-18", 290000.00, 290000),
17. (2, "2023-11-18", 320000.00, 320000),
18. (3, "2024-11-18", 399999.99, 399999),
19. (4, "2025-11-18", 400000.00, 400000),
20. (5, "2026-11-18", 470000.00, 470000),
21. (6, "2027-11-18", 510000.00, 510000),
22. (7, "2028-11-18", 610000.00, 610000),
23. (8, null, null, null);

25. SELECT * FROM width_bucket_test ORDER BY k1;

27. +------+------------+-----------+--------+
28. | k1   | v1         | v2        | v3     |
29. +------+------------+-----------+--------+
30. |    1 | 2022-11-18 |    290000 | 290000 |
31. |    2 | 2023-11-18 |    320000 | 320000 |
32. |    3 | 2024-11-18 | 399999.99 | 399999 |
33. |    4 | 2025-11-18 |    400000 | 400000 |
34. |    5 | 2026-11-18 |    470000 | 470000 |
35. |    6 | 2027-11-18 |    510000 | 510000 |
36. |    7 | 2028-11-18 |    610000 | 610000 |
37. |    8 | NULL       |      NULL |   NULL |
38. +------+------------+-----------+--------+

40. SELECT k1, v1, v2, v3, width_bucket(v1, date('2023-11-18'), date('2027-11-18'), 4) AS w FROM width_bucket_test ORDER BY k1;

42. +------+------------+-----------+--------+------+
43. | k1   | v1         | v2        | v3     | w    |
44. +------+------------+-----------+--------+------+
45. |    1 | 2022-11-18 |    290000 | 290000 |    0 |
46. |    2 | 2023-11-18 |    320000 | 320000 |    1 |
47. |    3 | 2024-11-18 | 399999.99 | 399999 |    2 |
48. |    4 | 2025-11-18 |    400000 | 400000 |    3 |
49. |    5 | 2026-11-18 |    470000 | 470000 |    4 |
50. |    6 | 2027-11-18 |    510000 | 510000 |    5 |
51. |    7 | 2028-11-18 |    610000 | 610000 |    5 |
52. |    8 | NULL       |      NULL |   NULL | NULL |
53. +------+------------+-----------+--------+------+

55. SELECT k1, v1, v2, v3, width_bucket(v2, 200000, 600000, 4) AS w FROM width_bucket_test ORDER BY k1;

57. +------+------------+-----------+--------+------+
58. | k1   | v1         | v2        | v3     | w    |
59. +------+------------+-----------+--------+------+
60. |    1 | 2022-11-18 |    290000 | 290000 |    1 |
61. |    2 | 2023-11-18 |    320000 | 320000 |    2 |
62. |    3 | 2024-11-18 | 399999.99 | 399999 |    2 |
63. |    4 | 2025-11-18 |    400000 | 400000 |    3 |
64. |    5 | 2026-11-18 |    470000 | 470000 |    3 |
65. |    6 | 2027-11-18 |    510000 | 510000 |    4 |
66. |    7 | 2028-11-18 |    610000 | 610000 |    5 |
67. |    8 | NULL       |      NULL |   NULL | NULL |
68. +------+------------+-----------+--------+------+

70. SELECT k1, v1, v2, v3, width_bucket(v3, 200000, 600000, 4) AS w FROM width_bucket_test ORDER BY k1;

72. +------+------------+-----------+--------+------+
73. | k1   | v1         | v2        | v3     | w    |
74. +------+------------+-----------+--------+------+
75. |    1 | 2022-11-18 |    290000 | 290000 |    1 |
76. |    2 | 2023-11-18 |    320000 | 320000 |    2 |
77. |    3 | 2024-11-18 | 399999.99 | 399999 |    2 |
78. |    4 | 2025-11-18 |    400000 | 400000 |    3 |
79. |    5 | 2026-11-18 |    470000 | 470000 |    3 |
80. |    6 | 2027-11-18 |    510000 | 510000 |    4 |
81. |    7 | 2028-11-18 |    610000 | 610000 |    5 |
82. |    8 | NULL       |      NULL |   NULL | NULL |
83. +------+------------+-----------+--------+------+

keywords

WIDTH_BUCKET