Syntax

    Arguments

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

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

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

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

    Returned value

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

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

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

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

    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),
    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);
    24. SELECT * FROM width_bucket_test ORDER BY k1;                                      
    26. +------+------------+-----------+--------+
    27. | k1   | v1         | v2        | v3     |
    28. +------+------------+-----------+--------+
    29. |    1 | 2022-11-18 |    290000 | 290000 |
    30. |    2 | 2023-11-18 |    320000 | 320000 |
    31. |    3 | 2024-11-18 | 399999.99 | 399999 |
    32. |    4 | 2025-11-18 |    400000 | 400000 |
    33. |    5 | 2026-11-18 |    470000 | 470000 |
    34. |    6 | 2027-11-18 |    510000 | 510000 |
    35. |    7 | 2028-11-18 |    610000 | 610000 |
    36. |    8 | NULL       |      NULL |   NULL |
    37. +------+------------+-----------+--------+
    39. 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;
    41. +------+------------+-----------+--------+------+
    42. | k1   | v1         | v2        | v3     | w    |
    43. +------+------------+-----------+--------+------+
    44. |    1 | 2022-11-18 |    290000 | 290000 |    0 |
    45. |    2 | 2023-11-18 |    320000 | 320000 |    1 |
    46. |    3 | 2024-11-18 | 399999.99 | 399999 |    2 |
    47. |    4 | 2025-11-18 |    400000 | 400000 |    3 |
    48. |    5 | 2026-11-18 |    470000 | 470000 |    4 |
    49. |    6 | 2027-11-18 |    510000 | 510000 |    5 |
    51. |    8 | NULL       |      NULL |   NULL | NULL |
    52. +------+------------+-----------+--------+------+
    53. SELECT k1, v1, v2, v3, width_bucket(v2, 200000, 600000, 4) AS w FROM width_bucket_test ORDER BY k1;
    55. +------+------------+-----------+--------+------+
    56. | k1   | v1         | v2        | v3     | w    |
    57. +------+------------+-----------+--------+------+
    58. |    1 | 2022-11-18 |    290000 | 290000 |    1 |
    59. |    2 | 2023-11-18 |    320000 | 320000 |    2 |
    60. |    3 | 2024-11-18 | 399999.99 | 399999 |    2 |
    61. |    4 | 2025-11-18 |    400000 | 400000 |    3 |
    62. |    5 | 2026-11-18 |    470000 | 470000 |    3 |
    63. |    6 | 2027-11-18 |    510000 | 510000 |    4 |
    64. |    7 | 2028-11-18 |    610000 | 610000 |    5 |
    65. |    8 | NULL       |      NULL |   NULL | NULL |
    66. +------+------------+-----------+--------+------+
    68. SELECT k1, v1, v2, v3, width_bucket(v3, 200000, 600000, 4) AS w FROM width_bucket_test ORDER BY k1;
    70. +------+------------+-----------+--------+------+
    71. | k1   | v1         | v2        | v3     | w    |
    72. +------+------------+-----------+--------+------+
    73. |    1 | 2022-11-18 |    290000 | 290000 |    1 |
    74. |    2 | 2023-11-18 |    320000 | 320000 |    2 |
    75. |    3 | 2024-11-18 | 399999.99 | 399999 |    2 |
    76. |    4 | 2025-11-18 |    400000 | 400000 |    3 |
    77. |    5 | 2026-11-18 |    470000 | 470000 |    3 |
    78. |    6 | 2027-11-18 |    510000 | 510000 |    4 |
    79. |    7 | 2028-11-18 |    610000 | 610000 |    5 |
    80. |    8 | NULL       |      NULL |   NULL | NULL |
    81. +------+------------+-----------+--------+------+

    WIDTH_BUCKET