Oracle WIDTH_BUCKET() function
Description
The Oracle WIDTH_BUCKET function is used to get the bucket number into which the value of this expression would fall after being evaluated.
Basically, the WIDTH_BUCKET construct the equiwidth histograms, where the histogram range is divided into lots of intervals that have unique size. A bucket can be assigned to scores between 10.00 and 19.999... to indicate that 10 is included in the interval and 20 is excluded. This is sometimes denoted (10, 20).
Syntax:
WIDTH_BUCKET (expr, min_value, max_value, num_buckets)
Arguments:
| Name | Description |
|---|---|
| expr | Expression, for which the histogram is being created. The expression returns null when the expr evaluates to null. |
| min_value and max_value | Expressions, that resolve to the end points of the acceptable range. Both expressions must also evaluate to numeric or datetime values, and no null value can be evaluated. |
| num_buckets | Expression, that indicating the number of buckets. |
Example:
The example below creates a ten-bucket histogram on the manager_id column for employees within the department_id 119 in the table employees and returns the bucket number ("Exists in Departments") for each employee. Employees with manager_id greater than the maximum value are assigned to the overflow bucket, 11:
SELECT employee_id, first_name,last_name,department_id,manager_id,
WIDTH_BUCKET(department_id,30,100,10) "Exists in Departments"
FROM employees
WHERE manager_id < 120
ORDER BY "Exists in Departments";
Here is the result.
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID Exists in Departments
----------- -------------------- ------------------------- ------------- ---------- ---------------------
200 Jennifer Whalen 10 101 0
201 Michael Hartstein 20 100 0
118 Guy Himuro 30 114 1
119 Karen Colmenares 30 114 1
117 Sigal Tobias 30 114 1
116 Shelli Baida 30 114 1
115 Alexander Khoo 30 114 1
114 Den Raphaely 30 100 1
203 Susan Mavris 40 101 2
120 Matthew Weiss 50 100 3
121 Adam Fripp 50 100 3
122 Payam Kaufling 50 100 3
123 Shanta Vollman 50 100 3
124 Kevin Mourgos 50 100 3
104 Bruce Ernst 60 103 5
105 David Austin 60 103 5
106 Valli Pataballa 60 103 5
107 Diana Lorentz 60 103 5
103 Alexander Hunold 60 102 5
204 Hermann Baer 70 101 6
147 Alberto Errazuriz 80 100 8
149 Eleni Zlotkey 80 100 8
148 Gerald Cambrault 80 100 8
146 Karen Partners 80 100 8
145 John Russell 80 100 8
102 Lex De Haan 90 100 9
101 Neena Kochhar 90 100 9
112 Jose Manuel Urman 100 108 11
109 Daniel Faviet 100 108 11
110 John Chen 100 108 11
111 Ismael Sciarra 100 108 11
205 Shelley Higgins 110 101 11
113 Luis Popp 100 108 11
108 Nancy Greenberg 100 101 11
34 rows selected.
Previous:
TRUNC
Next:
Oracle Datetime Functions Introduction
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics