Oracle WIDTH_BUCKET() function

Last update on August 09 2024 11:11:46 (UTC/GMT +8 hours)

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).

Uses of Oracle WIDTH_BUCKET() Function

Histogram creation: Generate histograms to analyze the distribution of data within specified ranges.

Data segmentation: Segment data into buckets based on value ranges for easier analysis and reporting.

Performance tuning: Optimize queries by using bucketed histograms for cost-based optimization.

Data categorization: Categorize data into discrete intervals for statistical analysis.

Range checking: Identify the interval to which a value belongs within a given range.

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