{"id":18917,"date":"2021-07-30T08:17:48","date_gmt":"2021-07-29T22:17:48","guid":{"rendered":"https:\/\/database.guide\/?p=18917"},"modified":"2021-07-30T08:17:48","modified_gmt":"2021-07-29T22:17:48","slug":"width_bucket-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/width_bucket-function-in-oracle\/","title":{"rendered":"WIDTH_BUCKET() Function in Oracle"},"content":{"rendered":"\n

In Oracle, the WIDTH_BUCKET()<\/code> function allows you to construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size.<\/p>\n\n\n\n

For a given expression, WIDTH_BUCKET()<\/code> returns the bucket number into which the value of this expression would fall after being evaluated.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

WIDTH_BUCKET(expr, min_value, max_value, num_buckets)<\/code><\/pre>\n\n\n\n
Where:<\/p>\n\n\n\n
  • expr<\/code><\/em> is the expression for which the histogram is being created. This expression must evaluate to a numeric or datetime value or to a value that can be implicitly converted to a numeric or datetime value. If expr<\/code><\/em> evaluates to null<\/code>, then the expression returns null<\/code>.<\/li>
  • min_value<\/code><\/em> and max_value<\/code><\/em> are expressions that resolve to the end points of the acceptable range for expr<\/code><\/em>. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate to null<\/code>.<\/li>
  • num_buckets<\/code><\/em> is an expression that resolves to a constant indicating the number of buckets. This expression must evaluate to a positive integer.<\/li><\/ul>\n\n\n\n
    Example<\/h2>\n\n\n\n
    Here’s an example to demonstrate how it works.<\/p>\n\n\n\n
    SELECT \n  WIDTH_BUCKET(3, 1, 12, 3) AS r1,\n  WIDTH_BUCKET(5, 1, 12, 3) AS r2,\n  WIDTH_BUCKET(9, 1, 12, 3) AS r3\nFROM DUAL;<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
       R1    R2    R3 \n_____ _____ _____ \n    1     2     3 <\/pre>\n\n\n\n
    Here’s an explanation. Let’s examine each argument, starting from the last one and working backwards to the first one.<\/p>\n\n\n\n
    • Fourth argument<\/strong>: I specify three buckets. I do this by using 3<\/code> as the fourth argument.<\/li>
    • Second and third arguments<\/strong>: I specify that the range is between 1 and 12. In this case, my second argument is 1<\/code> and third argument is 12<\/code>.<\/li>
    • First argument<\/strong>: This value is compared against the second and third arguments, so as to know which of the three buckets it should be assigned to. In my example I call WIDTH_BUCKET()<\/code> three times in order to illustrate the concept better. I do this so that I can provide three different values as the first argument, each of which is assigned to a different bucket.<\/li><\/ul>\n\n\n\n
      The following table provides another way of visualising this:<\/p>\n\n\n\n
      Values<\/th>Bucket<\/th><\/tr>
      1, 2, 3, 4<\/td>Bucket 1<\/td><\/tr>
      5, 6, 7, 8<\/td>Bucket 2<\/td><\/tr>
      9, 10, 11, 12<\/td>Bucket 3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      So we can see that the first bucket accepts values from between 1 and 4, the second bucket between 5 and 8, and the third bucket is for values between 9 and 12.<\/p>\n\n\n\n
      If I were to change it so that there were four buckets, my code might look something like this:<\/p>\n\n\n\n
      SELECT \n  WIDTH_BUCKET(3, 1, 12, 4) AS r1,\n  WIDTH_BUCKET(5, 1, 12, 4) AS r2,\n  WIDTH_BUCKET(9, 1, 12, 4) AS r3\nFROM DUAL;<\/code><\/pre>\n\n\n\n
      And the table would look like this:<\/p>\n\n\n\n
      Values<\/th>Bucket<\/th><\/tr>
      1, 2, 3<\/td>Bucket 1<\/td><\/tr>
      4, 5, 6<\/td>Bucket 2<\/td><\/tr>
      7, 8, 9<\/td>Bucket 3<\/td><\/tr>
      10, 11, 12<\/td>Bucket 4<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      Here’s another example in which only the bucket sizes change:<\/p>\n\n\n\n
      SELECT \n  WIDTH_BUCKET(5, 1, 12, 3) AS r1,\n  WIDTH_BUCKET(5, 1, 12, 6) AS r2,\n  WIDTH_BUCKET(5, 1, 12, 10) AS r3\nFROM DUAL;<\/code><\/pre>\n\n\n\n
      Result:<\/p>\n\n\n\n
         R1    R2    R3 \n_____ _____ _____ \n    2     3     4 <\/pre>\n\n\n\n
      Out of Range<\/h3>\n\n\n\n
      If the input is outside the range of the bucket, you’ll get either 0<\/code> or num_buckets<\/code><\/em>+1, depending on whether the input is below the range or above it. In such cases, Oracle Database creates an underflow bucket numbered 0<\/code> and an overflow bucket numbered num_buckets<\/code><\/em>+1.<\/p>\n\n\n\n
      Example:<\/p>\n\n\n\n
      SELECT \n  WIDTH_BUCKET(-3, 1, 12, 3),\n  WIDTH_BUCKET(20, 1, 12, 3)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
      Result:<\/p>\n\n\n\n
         WIDTH_BUCKET(-3,1,12,3)    WIDTH_BUCKET(20,1,12,3) \n__________________________ __________________________ \n                         0                          4 <\/pre>\n","protected":false},"excerpt":{"rendered":"
      In Oracle, the WIDTH_BUCKET() function allows you to construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. For a given expression, WIDTH_BUCKET() returns the bucket number into which the value of this expression would fall after being evaluated.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,71,20],"class_list":["post-18917","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-numeric-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18917","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18917"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18917\/revisions"}],"predecessor-version":[{"id":19180,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18917\/revisions\/19180"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}