Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Width Bucket – Analytic Function

WIDTH_BUCKET function is used to assign bucket value for a list of values based on maximum and minimum number. This is one of the new analytic function introduced in Oracle 10g. Consider the following table.

StudentId Mark
1 56
2 43
3 76
4 89
5 90
6 91
7 49
8 63
9 83
10 74

If you want to assign buckets based on the mark they got, the SQL to that would be

SQL> select StudentId
2       , Mark
3       , wIDTH_BUCKET(Mark, 50, 100, 5) as Grade#
4    from hr.Students
5  /

STUDENTI MARK   GRADE#
-------- ------ ------
1        56     1
2        43     0
3        76     3
4        89     4
5        90     5
6        91     5
7        49     0
8        63     2
9        83     4
10       74     3

If you want to assign grade 1 for 90-99, then change the WIDTH_BUCKET (MARK, 100, 50, 5).

Referrence

Oracle documentation for WIDTH_FUNCTION.

2 responses to “Width Bucket – Analytic Function

  1. Sambath November 30, 2009 at 6:28 AM

    Is it available in 10g ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: