:tocdepth: 3
******************************
Numeric/Mathematical Functions
******************************
.. contents::
ABS
===
.. function:: ABS (number_expr)
The **ABS** function returns the absolute value of a given number. The data type of the return value is the same as that of the argument. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param number_expr: An operator which returns a numeric value
:rtype: same as that of the argument
.. code-block:: sql
--it returns the absolute value of the argument
SELECT ABS(12.3), ABS(-12.3), ABS(-12.3000), ABS(0.0);
::
abs(12.3) abs(-12.3) abs(-12.3000) abs(0.0)
================================================================================
12.3 12.3 12.3000 .0
ACOS
====
.. function:: ACOS ( x )
The **ACOS** function returns an arc cosine value of the argument. That is, it returns a value whose cosine is *x* in radian. The return value is a **DOUBLE** type. x must be a value between -1 and 1, inclusive. Otherwise, **NULL** is returned. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT ACOS(1), ACOS(0), ACOS(-1);
::
acos(1) acos(0) acos(-1)
==================================================================================
0.000000000000000e+00 1.570796326794897e+00 3.141592653589793e+00
ASIN
====
.. function:: ASIN ( x )
The **ASIN** function returns an arc sine value of the argument. That is, it returns a value whose sine is *x* in radian. The return value is a **DOUBLE** type. x must be a value between -1 and 1, inclusive. Otherwise, **NULL** is returned. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT ASIN(1), ASIN(0), ASIN(-1);
::
asin(1) asin(0) asin(-1)
==============================================================================
1.570796326794897e+00 0.000000000000000e+00 -1.570796326794897e+00
ATAN
====
.. function:: ATAN ( [y,] x )
The **ATAN** function returns a value whose tangent is *x* in radian. The argument *y* can be omitted. If *y* is specified, the function calculates the arc tangent value of *y/x*. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x,y: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT ATAN(1), ATAN(-1), ATAN(1,-1);
::
atan(1) atan(-1) atan2(1, -1)
==============================================================================
7.853981633974483e-01 -7.853981633974483e-01 2.356194490192345e+000
ATAN2
=====
.. function:: ATAN2 ( y, x )
The **ATAN2** function returns the arc tangent value of *y/x* in radian. This function is working like the :func:`ATAN`. Arguments *x* and *y* must be specified. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x,y: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT ATAN2(1,1), ATAN2(-1,-1), ATAN2(Pi(),0);
::
atan2(1, 1) atan2(-1, -1) atan2( pi(), 0)
==============================================================================
7.853981633974483e-01 -2.356194490192345e+00 1.570796326794897e+00
CEIL
====
.. function:: CEIL( number_operand )
The **CEIL** function returns the smallest integer that is not less than its argument. The return value is determined based on the valid number of digits that are specified as the *number_operand* argument. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param number_operand: An expression that returns a numeric value
:rtype: INT
.. code-block:: sql
SELECT CEIL(34567.34567), CEIL(-34567.34567);
::
ceil(34567.34567) ceil(-34567.34567)
============================================
34568.00000 -34567.00000
SELECT CEIL(34567.1), CEIL(-34567.1);
::
ceil(34567.1) ceil(-34567.1)
=============================
34568.0 -34567.0
CONV
====
.. function:: CONV (number,from_base,to_base)
The **CONV** function converts numbers between different number bases. This function returns a string representation of a converted number. The minimum value is 2 and the maximum value is 36. If *to_base* (representing the base to be returned) is negative, *number* is regarded as a signed number. Otherwise, it regarded as a unsigned number. When you input the string which cannot be transformed into the number to *from_base* or *to_base*, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param number: An input number
:param from_base: The base of an input number
:param to_base: The base of an returned value
:rtype: STRING
.. code-block:: sql
SELECT CONV('f',16,2);
::
'1111'
.. code-block:: sql
SELECT CONV('6H',20,8);
::
'211'
.. code-block:: sql
SELECT CONV(-30,10,-20);
::
'-1A'
COS
===
.. function:: COS ( x )
The **COS** function returns a cosine value of the argument. The argument *x* must be a radian value. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT COS(pi()/6), COS(pi()/3), COS(pi());
::
cos( pi()/6) cos( pi()/3) cos( pi())
==============================================================================
8.660254037844387e-01 5.000000000000001e-01 -1.000000000000000e+00
COT
===
.. function:: COT ( x )
The **COT** function returns the cotangent value of the argument *x*. That is, it returns a value whose tangent is *x* in radian. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT COT(1), COT(-1), COT(0);
::
cot(1) cot(-1) cot(0)
==========================================================================
6.420926159343306e-01 -6.420926159343306e-01 NULL
CRC32
=====
.. function:: CRC32 ( string )
The **CRC32** function returns a cyclic redundancy check value as 32-bit integer. When NULL is given as input, it returns NULL.
:param string: An expression that returns a string value
:rtype: INTEGER
.. code-block:: sql
SELECT CRC32('cubrid');
::
crc32('cubrid')
==================
908740081
DEGREES
=======
.. function:: DEGREES ( x )
The **DEGREES** function returns the argument *x* specified in radian converted to a degree value. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT DEGREES(pi()/6), DEGREES(pi()/3), DEGREES (pi());
::
degrees( pi()/6) degrees( pi()/3) degrees( pi())
==============================================================================
3.000000000000000e+01 5.999999999999999e+01 1.800000000000000e+02
DRANDOM, DRAND
==============
.. function:: DRANDOM ( [seed] )
.. function:: DRAND ( [seed] )
The function **DRANDOM** or **DRAND** returns a random double-precision floating point value in the range of between 0.0 and 1.0. A *seed* argument that is **INTEGER** type can be specified. It rounds up real numbers and an error is returned when it exceeds the range of **INTEGER**.
When *seed* value is not given, the **DRAND** function performs the operation only once to produce only one random number regardless of the number of rows where the operation is output, but the **DRANDOM** function performs the operation every time the statement is repeated to produce a different random value for each row. Therefore, to output rows in a random order, you must use the **DRANDOM** function in the **ORDER BY** clause. To obtain a random integer value, use the :func:`RANDOM`.
:param seed: seed value
:rtype: DOUBLE
.. code-block:: sql
SELECT DRAND(), DRAND(1), DRAND(1.4);
::
drand() drand(1) drand(1.4)
==============================================================================
2.849646518006921e-001 4.163034446537495e-002 4.163034446537495e-002
.. code-block:: sql
CREATE TABLE rand_tbl (
id INT,
name VARCHAR(255)
);
INSERT INTO rand_tbl VALUES
(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'),
(6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j');
SELECT * FROM rand_tbl;
::
id name
===================================
1 'a'
2 'b'
3 'c'
4 'd'
5 'e'
6 'f'
7 'g'
8 'h'
9 'i'
10 'j'
.. code-block:: sql
--drandom() returns random values on every row
SELECT DRAND(), DRANDOM() FROM rand_tbl;
::
drand() drandom()
==============================================================================
7.638782921842098e-001 1.018707846308786e-001
7.638782921842098e-001 3.191320535905026e-001
7.638782921842098e-001 3.461714529862361e-001
7.638782921842098e-001 6.791894283883175e-001
7.638782921842098e-001 4.533829767754143e-001
7.638782921842098e-001 1.714224677266762e-001
7.638782921842098e-001 1.698049867244484e-001
7.638782921842098e-001 4.507583849604786e-002
7.638782921842098e-001 5.279091769157994e-001
7.638782921842098e-001 7.021088290047914e-001
.. code-block:: sql
--selecting rows in random order
SELECT * FROM rand_tbl ORDER BY DRANDOM();
::
id name
===================================
6 'f'
2 'b'
7 'g'
8 'h'
1 'a'
4 'd'
10 'j'
9 'i'
5 'e'
3 'c'
EXP
===
.. function:: EXP( x )
The **EXP** function returns e x (the base of natural logarithm) raised to a power. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An operator which returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT EXP(1), EXP(0);
::
exp(1) exp(0)
====================================================
2.718281828459045e+000 1.000000000000000e+000
.. code-block:: sql
SELECT EXP(-1), EXP(2.00);
::
exp(-1) exp(2.00)
====================================================
3.678794411714423e-001 7.389056098930650e+000
FLOOR
=====
.. function:: FLOOR( number_operand )
The **FLOOR** function returns the largest integer that is not greater than its argument. The data type of the return value is the same as that of the argument. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param number_operand: An operator which returns a numeric value
:rtype: same as that of the argument
.. code-block:: sql
--it returns the largest integer less than or equal to the arguments
SELECT FLOOR(34567.34567), FLOOR(-34567.34567);
::
floor(34567.34567) floor(-34567.34567)
============================================
34567.00000 -34568.00000
.. code-block:: sql
SELECT FLOOR(34567), FLOOR(-34567);
::
floor(34567) floor(-34567)
=============================
34567 -34567
HEX
===
.. function:: HEX(n)
The **HEX** function returns a hexadecimal string about the string which is specified as an argument; it returns a hexadecimal string of the number if a number is specified as an argument. If a number is specified as an argument, it returns a value like CONV(num, 10, 16).
:param n: A string or a number
:rtype: STRING
.. code-block:: sql
SELECT HEX('ab'), HEX(128), CONV(HEX(128), 16, 10);
::
hex('ab') hex(128) conv(hex(128), 16, 10)
==================================================================
'6162' '80' '128'
LN
==
.. function:: LN ( x )
The **LN** function returns the natural log value (base = e) of an antilogarithm *x*. The return value is a **DOUBLE** type. If the antilogarithm is 0 or a negative number, an error is returned. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a positive number
:rtype: DOUBLE
.. code-block:: sql
SELECT ln(1), ln(2.72);
::
ln(1) ln(2.72)
=====================================================
0.000000000000000e+00 1.000631880307906e+00
LOG2
====
.. function:: LOG2 ( x )
The **LOG2** function returns a log value whose antilogarithm is *x* and base is 2. The return value is a **DOUBLE** type. If the antilogarithm is 0 or a negative number, an error is returned. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a positive number
:rtype: DOUBLE
.. code-block:: sql
SELECT log2(1), log2(8);
::
log2(1) log2(8)
======================================================
0.000000000000000e+00 3.000000000000000e+00
LOG10
=====
.. function:: LOG10 ( x )
The **LOG10** function returns the common log value of an antilogarithm *x*. The return value is a **DOUBLE** type. If the antilogarithm is 0 or a negative number, an error is returned. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a positive number
:rtype: DOUBLE
.. code-block:: sql
SELECT log10(1), log10(1000);
::
log10(1) log10(1000)
====================================================
0.000000000000000e+00 3.000000000000000e+00
MOD
===
.. function:: MOD (m, n)
The **MOD** function returns the remainder of the first parameter *m* divided by the second parameter *n*. If *n* is 0, *m* is returned without the division operation being performed. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
Note that if the dividend, the parameter m of the **MOD** function, is a negative number, the function operates differently from a typical operation (classical modulus) method.
**Result of MOD**
+-------+-------+---------------+-----------------------+
| m | n | MOD(m, n) | Classical Modulus |
| | | | m-n*FLOOR(m/n) |
+=======+=======+===============+=======================+
| 11 | 4 | 3 | 3 |
+-------+-------+---------------+-----------------------+
| 11 | -4 | 3 | -1 |
+-------+-------+---------------+-----------------------+
| -11 | 4 | -3 | 1 |
+-------+-------+---------------+-----------------------+
| -11 | -4 | -3 | -3 |
+-------+-------+---------------+-----------------------+
| 11 | 0 | 11 | Divided by 0 error |
+-------+-------+---------------+-----------------------+
:param m: Represents a dividend. It is an expression that returns a numeric value.
:param n: Represents a divisor. It is an expression that returns a numeric value.
:rtype: INT
.. code-block:: sql
--it returns the reminder of m divided by n
SELECT MOD(11, 4), MOD(11, -4), MOD(-11, 4), MOD(-11, -4), MOD(11,0);
::
mod(11, 4) mod(11, -4) mod(-11, 4) mod(-11, -4) mod(11, 0)
=====================================================================
3 3 -3 -3 11
.. code-block:: sql
SELECT MOD(11.0, 4), MOD(11.000, 4), MOD(11, 4.0), MOD(11, 4.000);
::
mod(11.0, 4) mod(11.000, 4) mod(11, 4.0) mod(11, 4.000)
=========================================================================
3.0 3.000 3.0 3.000
PI
==
.. function:: PI ()
The **PI** function returns the ? value of type **DOUBLE**.
:rtype: DOUBLE
.. code-block:: sql
SELECT PI(), PI()/2;
::
pi() pi()/2
====================================================
3.141592653589793e+00 1.570796326794897e+00
POW, POWER
==========
.. function:: POW( x, y )
.. function:: POWER( x, y )
The **POW** function returns *x* to the power of *y*. The functions **POW** and **POWER** are used interchangeably. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: It represents the base. It is an expression that returns a numeric value. An expression that returns a numeric value.
:param y: It represents the exponent. An expression that returns a numeric value. If the base is a negative number, an integer must specified as the exponent.
:rtype: DOUBLE
.. code-block:: sql
SELECT POWER(2, 5), POWER(-2, 5), POWER(0, 0), POWER(1,0);
::
power(2, 5) power(-2, 5) power(0, 0) power(1, 0)
====================================================================================================
3.200000000000000e+01 -3.200000000000000e+01 1.000000000000000e+00 1.000000000000000e+00
.. code-block:: sql
--it returns an error when the negative base is powered by a non-int exponent
SELECT POWER(-2, -5.1), POWER(-2, -5.1);
::
ERROR: Argument of power() is out of range.
RADIANS
=======
.. function:: RADIANS ( x )
The **RADIANS** function returns the argument *x* specified in degrees converted to a radian value. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT RADIANS(90), RADIANS(180), RADIANS(360);
::
radians(90) radians(180) radians(360)
==============================================================================
1.570796326794897e+00 3.141592653589793e+00 6.283185307179586e+00
RANDOM, RAND
============
.. function:: RANDOM ( [seed] )
.. function:: RAND ( [seed] )
The function **RANDOM** or **RAND** returns any integer value, which is greater than or equal to 0 and less than 2 31, and a *seed* argument that is **INTEGER** type can be specified. It rounds up real numbers and an error is returned when it exceeds the range of **INTEGER**.
When *seed* value is not given, the **RAND** function performs the operation only once to produce only one random number regardless of the number of rows where the operation is output, but the **RANDOM** function performs the operation every time the statement is repeated to produce a different random value for each row. Therefore, to output rows in a random order, you must use the **RANDOM** function.
To obtain a random real number, use the :func:`DRANDOM`.
:param seed:
:rtype: INT
.. code-block:: sql
SELECT RAND(), RAND(1), RAND(1.4);
::
rand() rand(1) rand(1.4)
=======================================
1526981144 89400484 89400484
.. code-block:: sql
--creating a new table
SELECT * FROM rand_tbl;
::
id name
===================================
1 'a'
2 'b'
3 'c'
4 'd'
5 'e'
6 'f'
7 'g'
8 'h'
9 'i'
10 'j'
.. code-block:: sql
--random() returns random values on every row
SELECT RAND(),RANDOM() FROM rand_tbl;
::
rand() random()
============================
2078876566 1753698891
2078876566 1508854032
2078876566 625052132
2078876566 279624236
2078876566 1449981446
2078876566 1360529082
2078876566 1563510619
2078876566 1598680194
2078876566 1160177096
2078876566 2075234419
.. code-block:: sql
--selecting rows in random order
SELECT * FROM rand_tbl ORDER BY RANDOM();
::
id name
===================================
6 'f'
1 'a'
5 'e'
4 'd'
2 'b'
7 'g'
10 'j'
9 'i'
3 'c'
8 'h'
ROUND
=====
.. function:: ROUND ( number_operand, integer )
The **ROUND** function returns the specified argument, *number_operand*, rounded to the number of places after the decimal point specified by the *integer*. If the *integer* argument is a negative number, it rounds to a place before the decimal point, that is, at the integer part. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param number_operand: An expression that returns a numeric value
:param integer: Specifies the place to round to. If a positive integer *n* is specified, the number is represented to the nth place after the decimal point; if a negative integer *n* is specified, the number is rounded to the *n* th place before the decimal point.
:rtype: same type as the *number_operand*
.. code-block:: sql
--it rounds a number to one decimal point when the second argument is omitted
SELECT ROUND(34567.34567), ROUND(-34567.34567);
::
round(34567.34567, 0) round(-34567.34567, 0)
============================================
34567.00000 -34567.00000
.. code-block:: sql
--it rounds a number to three decimal point
SELECT ROUND(34567.34567, 3), ROUND(-34567.34567, 3) FROM db_root;
::
round(34567.34567, 3) round(-34567.34567, 3)
============================================
34567.34600 -34567.34600
.. code-block:: sql
--it rounds a number three digit to the left of the decimal point
SELECT ROUND(34567.34567, -3), ROUND(-34567.34567, -3);
::
round(34567.34567, -3) round(-34567.34567, -3)
============================================
35000.00000 -35000.00000
SIGN
====
.. function:: SIGN (number_operand)
The **SIGN** function returns the sign of a given number. It returns 1 for a positive value, -1 for a negative value, and 0 for zero. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param number_operand: An operator which returns a numeric value
:rtype: INT
.. code-block:: sql
--it returns the sign of the argument
SELECT SIGN(12.3), SIGN(-12.3), SIGN(0);
::
sign(12.3) sign(-12.3) sign(0)
========================================
1 -1 0
SIN
===
.. function:: SIN ( x )
The **SIN** function returns a sine value of the parameter. The argument *x* must be a radian value. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT SIN(pi()/6), SIN(pi()/3), SIN(pi());
::
sin( pi()/6) sin( pi()/3) sin( pi())
==============================================================================
4.999999999999999e-01 8.660254037844386e-01 1.224646799147353e-16
SQRT
====
.. function:: SQRT ( x )
The **SQRT** function returns the square root of *x* as a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value. An error is returned if this value is a negative number.
:rtype: DOUBLE
.. code-block:: sql
SELECT SQRT(4), SQRT(16.0);
::
sqrt(4) sqrt(16.0)
====================================================
2.000000000000000e+00 4.000000000000000e+00
TAN
===
.. function:: TAN ( x )
The **TAN** function returns a tangent value of the argument. The argument *x* must be a radian value. The return value is a **DOUBLE** type. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:rtype: DOUBLE
.. code-block:: sql
SELECT TAN(pi()/6), TAN(pi()/3), TAN(pi()/4);
::
tan( pi()/6) tan( pi()/3) tan( pi()/4)
==============================================================================
5.773502691896257e-01 1.732050807568877e+00 9.999999999999999e-01
TRUNC, TRUNCATE
===============
.. function:: TRUNC ( x[, dec] )
.. function:: TRUNCATE ( x, dec )
The function **TRUNC** or **TRUNCATE** truncates the numbers of the specified argument *x* to the right of the *dec* position. If the *dec* argument is a negative number, it displays 0s to the *dec-* th position left to the decimal point. Note that the *dec* argument of the **TRUNC** function can be omitted, but that of the **TRUNCATE** function cannot be omitted. If the *dec* argument is a negative number, it displays 0s to the *dec* -th position left to the decimal point. The number of digits of the return value to be represented follows the argument *x*. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
:param x: An expression that returns a numeric value
:param dec: The place to be truncated is specified. If a positive integer *n* is specified, the number is represented to the *n-*\th place after the decimal point; if a negative integer *n* is specified, the number is truncated to the *n-*\th place before the decimal point. It truncates to the first place after the decimal point if the *dec* argument is 0 or omitted. Note that the *dec* argument cannot be omitted in the **TRUNCATE** function.
:rtype: same type as the *x*
.. code-block:: sql
--it returns a number truncated to 0 places
SELECT TRUNC(34567.34567), TRUNCATE(34567.34567, 0);
::
trunc(34567.34567, 0) trunc(34567.34567, 0)
============================================
34567.00000 34567.00000
.. code-block:: sql
--it returns a number truncated to three decimal places
SELECT TRUNC(34567.34567, 3), TRUNC(-34567.34567, 3);
::
trunc(34567.34567, 3) trunc(-34567.34567, 3)
============================================
34567.34500 -34567.34500
.. code-block:: sql
--it returns a number truncated to three digits left of the decimal point
SELECT TRUNC(34567.34567, -3), TRUNC(-34567.34567, -3);
::
trunc(34567.34567, -3) trunc(-34567.34567, -3)
============================================
34000.00000 -34000.00000
WIDTH_BUCKET
============
.. function:: WIDTH_BUCKET(expression, from, to, num_buckets)
**WIDTH_BUCKET** distributes the rows in an ordered partition into a specified number of buckets. The buckets are numbered, starting from one. That is, **WIDTH_BUCKET** function creates an equi-width histogram. The return value is an integer. When you input the string which cannot be transformed into the number, it returns an error if the value of **return_null_on_function_errors** in **cubrid.conf** is no(the default), or returns NULL if it is yes.
This function equally divides the range by the given number of buckets and assigns the bucket number to each bucket. That is, every interval (bucket) has the identical size.
Note that :func:`NTILE` function equally divides the number of rows by the given number of buckets and assigns the bucket number to each bucket. That is, every bucket has the same number of rows.
:param expression: an input value to assign the bucket number. It specifies a certain expression which returns the number.
:param from: a start value of the range, which is given to *expression*. It is included in the entire range.
:param to: an end value of the range, which is given to *expression*. It is not included in the entire range.
:param num_buckets: the number of buckets. The #0 bucket and the #(*num_buckets* + 1) bucket are created to include the contents beyond the range.
:rtype: INT
*expression* is an input value to assign the bucket number. *from* and *to* should be numeric values, date/time values, or the string which can be converted to date/time value. *from* is included in the acceptable range, but *to* is beyond the range.
For example, WIDTH_BUCKET (score, 80, 50, 3) returns
* 0 when the score is larger than 80,
* 1 for [80, 70),
* 2 for [70, 60),
* 3 for [60, 50),
* and 4 when the score is 50 or smaller.
The following example divides the range equal to 80 or smaller and larger than 50 into the score range that has the identical score range from 1 to 3. If any score is beyond the range, 0 is given for the score larger than 80 and 4 is given for the score of 50 or smaller than 50.
.. code-block:: sql
CREATE TABLE t_score (name VARCHAR(10), score INT);
INSERT INTO t_score VALUES
('Amie', 60),
('Jane', 80),
('Lora', 60),
('James', 75),
('Peter', 70),
('Tom', 50),
('Ralph', 99),
('David', 55);
SELECT name, score, WIDTH_BUCKET (score, 80, 50, 3) grade
FROM t_score
ORDER BY grade ASC, score DESC;
::
name score grade
================================================
'Ralph' 99 0
'Jane' 80 1
'James' 75 1
'Peter' 70 2
'Amie' 60 3
'Lora' 60 3
'David' 55 3
'Tom' 50 4
In the following example, **WIDTH_BUCKET** function evenly divides the birthdate range into buckets and assigns the bucket number based on the range. It divides the range of eight customers from '1950-01-01' to '1999-12-31' into five buckets based on their dates of birth. If the birthdate value is beyond the range, 0 or 6 (*num_buckets* + 1) is returned.
.. code-block:: sql
CREATE TABLE t_customer (name VARCHAR(10), birthdate DATE);
INSERT INTO t_customer VALUES
('Amie', date'1978-03-18'),
('Jane', date'1983-05-12'),
('Lora', date'1987-03-26'),
('James', date'1948-12-28'),
('Peter', date'1988-10-25'),
('Tom', date'1980-07-28'),
('Ralph', date'1995-03-17'),
('David', date'1986-07-28');
SELECT name, birthdate, WIDTH_BUCKET (birthdate, date'1950-01-01', date'2000-1-1', 5) age_group
FROM t_customer
ORDER BY birthdate;
::
name birthdate age_group
===============================================
'James' 12/28/1948 0
'Amie' 03/18/1978 4
'Tom' 07/28/1980 4
'Jane' 05/12/1983 5
'David' 07/28/1986 5
'Lora' 03/26/1987 5
'Peter' 10/25/1988 5
'Ralph' 03/17/1995 6
Day widened into its first perfection as we moved down the highroad toward a near fork whose right was to lead Harry and his solemn cortége southward, while the left should be our eastward course. Camille and I rode horseback, side by side, with no one near enough to smile at my sentimental laudations of the morning's splendors, or at her for repaying my eloquence with looks so full of tender worship, personal acceptance and self-bestowal, that to tell of them here would make as poor a show as to lift a sea-flower out of the sea; they call for piccolo notes and I am no musician. The little man elected to have a cab. When Bow Street was reached Prout had the satisfaction of finding that all his birds had been netted. He received the warm congratulations of his inspector modestly. Caloric or air engines. I did not feel very comfortable after what had happened to those soldiers who lost their lives so cruelly sudden, or in any case had been seriously wounded, while the officers took little notice of them. But it was desirable to behave as discreetly as possible, and so to get a permit to Maastricht. "Louvain, “It will check up, that way, too,” smiled Larry. "Me run away," thought Shorty, as they walked along. "Hosses couldn't drag me away. I only hope that house is 10 miles off." Reuben began to take off his coat—young Realf drew back almost in disgust. "Well, would Robert have stolen money, or Albert disgraced your name, to get free, if you and your farm hadn't made them slaves? If you hadn't been a heartless slave-driver would George have died the other night alone on the Moor?—or would Richard have taken advantage of a neighbour's charity to escape from you? Don't you see that your ambition has driven you to make slaves of your children?" "D?an't tell me," said Coalbran in the bar, "as it wurn't his fault. Foot-and-mouth can't just drop from heaven. He must have bought some furriners, and they've carried it wud 'em, surelye." But meantime a strange restlessness consumed her, tinctured by a horrible boldness. There were moments when she no longer was afraid of Handshut, when she felt herself impelled to seek him out, and make the most of the short time they had together. There could be no danger, for he was going so soon ... so few more words, so few more glances.... Thus her mind worked. "What mean you, woman?" quickly returned De Boteler; "do you accuse the keeper of my chase as having plotted against your son, or whom do you suspect?" Her face was shrivelled and yellow, and the dark full eyes that now, as it were, stood forth from the sunken cheeks, looked with a strange brightness on the scene, and seemed well adapted to stamp the character of witch on so withered a form. And perhaps there were few of those entirely uninterested in the matter who now gazed upon her, who would not have sworn that she merited the stake. "Thou art set over the people, and to the Lord's anointed I come to seek for justice." HoME欧美一级毛片免费高清日韩
ENTER NUMBET 0018hzlinfeng.com.cn
jy-fuding.com.cn
www.fouyin5v36r.com.cn
www.taboohunter.com.cn
bngban.net.cn
www.jqzws.com.cn
www.kartek.com.cn
www.ivpe.com.cn
www.unxe.com.cn
gzblzlyj.com.cn