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.
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:
LN – returns the natural logarithm of the argument value. Raises an error condition if the argument value is 0 or negative. LN is a synonym for LOG.
EXP – returns the value computed by raising the value of e (the base of natural logarithms) to the power specified by the value of the argument.
POWER – returns the value computed by raising the value of the first argument to the power specified by the value of the second argument. If the first argument is 0 and the second is 0, returns one. If the first argument is 0 and the second is positive, returns 0. If the first argument is 0 and the second argument is negative, raises an exception. If the first argument is negative and the second is not an integer, raises an exception.
SQRT – returns the square root of the argument value, defined by syntax transformation to “POWER (expression, 0.5).”
FLOOR – returns the integer value nearest to positive infinity that is not greater than the value of the argument.
CEILING – returns the integer value nearest to negative infinity that is not less than the value of the argument. CEIL is a synonym for CEILING.
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.
For example, WIDTH_BUCKET (14, 5, 30, 5) returns 2 because:
(30-5)/5 is 5, so the range is divided into 5 partitions, each 5 units wide.
The first bucket represents values from 0.00% to 19.999 …%; the second represents values from 20.00% to 39.999 …%; and the fifth bucket represents values from 80.00% to 100.00%.
The bucket chosen is determined by computing (5*(14-5)/(30-5)) + 1 — one more than the number of buckets times the ratio of the offset of the specified value from the lower value to the range of possible values, which is (5*0/25) + 1, which is 2.8. This value is the range of values for bucket number 2 (2.0 through 2.999 …), so bucket number 2 is chosen.
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:
This 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].
“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.
