width_bucket

Description

Constructs equi-width histograms, in which the histogram range is divided into intervals of identical size, and returns the bucket number into which the value of an expression falls, after it has been evaluated. The function returns an integer value or null (if any input is null).

Syntax

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

Arguments

expr - The expression for which the histogram is created. This expression must evaluate to a numeric value or to a value that can be implicitly converted to a numeric value.

The value must be within the range of -(2^53 - 1) to 2^53 - 1 (inclusive).

min_value and max_value - The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.

The low and high end points must be within the range of -(2^53 - 1) to 2^53 - 1 (inclusive). In addition, the difference between these points must be less than 2^53 (i.e. abs(max_value - min_value) < 2^53).

num_buckets - The desired number of buckets; must be a positive integer value. A value from the expression is assigned to each bucket, and the function then returns the corresponding bucket number.

Returned value

It returns the bucket number into which the value of an expression falls.

When an expression falls outside the range, the function returns:

0 if the expression is less than min_value.

num_buckets + 1 if the expression is greater than or equal to max_value.

null if any input is 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