Numeric functions

OLAP numeric functions supported by Sybase IQ include CEILING (CEIL is an alias), EXP (EXPONENTIAL is an alias), FLOOR, LN (LOG is an alias), SQRT, and WIDTH_BUCKET.

<numeric value function> :: =
 <natural logarithm>
| <exponential function>
| <power function>
| <square root>
| <floor function>
| <ceiling function>
| <width bucket function>

The syntax for each supported numeric value function is shown in Table 2-3.

Table 2-3: Numeric value functions and syntax

Numeric value function

Syntax

Natural logarithm

LN ( numeric-expression )

Exponential function

EXP ( numeric-expression )

Power function

POWER ( numeric-expression1, numeric-expression2 )

Square root

SQRT ( numeric-expression )

Floor function

FLOOR ( numeric-expression )

Ceiling function

CEILING ( numeric-expression )

Width bucket function

WIDTH_BUCKET ( expression, min_value, max_value, num_buckets)

The semantics of the numeric value functions are:

WIDTH_BUCKET function

The WIDTH_BUCKET function is somewhat more complicated than the other numeric value functions. It accepts four arguments: “live value,” two range boundaries, and the number of equal-sized (or as nearly so as possible) partitions into which the range indicated by the boundaries is to be divided. WIDTH_BUCKET returns a number indicating the partition into which the live value should be placed, based on its value as a percentage of the difference between the higher range boundary and the lower boundary. The first partition is partition number one.

To avoid errors when the live value is outside the range of boundaries, live values that are less than the smaller range boundary are placed into an additional first bucket, bucket zero, and live values that are greater than the larger range boundary are placed into an additional last bucket, bucket N+1.

Visualizing the meaning of width bucket

For example, WIDTH_BUCKET (14, 5, 30, 5) returns 2 because:

WIDTH_BUCKET example

The following example creates a ten-bucket histogram on the credit_limit column for customers in Massachusetts in the sample table and returns the bucket number (“Credit Group”) for each customer. Customers with credit limits greater than the maximum value are assigned to the overflow bucket, 11:

NoteThis example is for illustration purposes only and was not generated using the iqdemo database.

SELECT customer_id, cust_last_name, credit_limit,
  WIDTH_BUCKET(credit_limit, 100, 5000, 10) "Credit
  Group" 
  FROM customers WHERE territory = 'MA'
  ORDER BY "Credit Group";
CUSTOMER_ID  CUST_LAST_NAME  CREDIT_LIMIT  Credit Group
-----------  --------------  ------------  ------------
825          Dreyfuss                 500             1
826          Barkin                   500             1
853          Palin                    400             1
827          Siegel                   500             1
843          Oates                    700             2
844          Julius                   700             2
835          Eastwood                1200             3
840          Elliott                 1400             3
842          Stern                   1400             3
841          Boyer                   1400             3
837          Stanton                 1200             3
836          Berenger                1200             3
848          Olmos                   1800             4
847           Streep                 	5000          	   11

When the bounds are reversed, the buckets are open-closed intervals. For example: WIDTH_BUCKET (credit_limit, 5000, 0, 5). In this example, bucket number 1 is (4000, 5000], bucket number 2 is (3000, 4000], and bucket number 5 is (0, 1000]. The overflow bucket is numbered 0 (5000, +infinity), and the underflow bucket is numbered 6 (-infinity, 0].

See also

“BIT_LENGTH function [String],” “EXP function [Numeric],” “FLOOR function [Numeric],” “POWER function [Numeric],” “SQRT function [Numeric],” and “WIDTH_BUCKET function [Numerical],” Chapter 4, “SQL Functions,” in Reference: Building Blocks, Tables, and Procedures.