LN

Updated at:

Calculates the natural logarithm of the number parameter.

Syntax

double|decimal ln(<number>)

Parameters

number: Required. The input value must be of the DOUBLE or DECIMAL data type. If the input value is of the STRING or BIGINT data type, it is implicitly converted to the DOUBLE data type for the calculation.

Return value

The return value is of the DOUBLE or DECIMAL data type. The following rules apply:

  • If number is a DOUBLE or DECIMAL, a value of the corresponding type is returned.

  • If number is a STRING or BIGINT, a DOUBLE value is returned.

  • If number is less than or equal to 0, NULL is returned.

  • If number is NULL, NULL is returned.

Sample data

This section provides sample source data and examples for you to understand how to use the function. In this topic, a table named mf_math_fun_t is created and data is inserted into the table. Sample statements:

CREATE TABLE IF NOT EXISTS mf_math_fun_t(
     int_data     INT,
     bigint_data  BIGINT,
     double_data  DOUBLE,
     decimal_data DECIMAL,
     float_data   FLOAT,
     string_data  STRING
    );
    
INSERT INTO mf_math_fun_t VALUES 
(null, -10, 0.525, 0.525BD, CAST(0.525 AS FLOAT), '10'),
(-20, null, -0.1, -0.1BD, CAST(-0.1 AS FLOAT), '-10'),
(0, -1, null, 20.45BD, CAST(-1 AS FLOAT), '30'),
(-40, 4, 0.89, null, CAST(0.89 AS FLOAT), '-30'),
(5, -50, -1, -1BD, null, '50'),
(-60, 6, 1.5, 1.5BD, CAST(1.5 AS FLOAT), '-50'),
(-1, -70, -7.5, -7.5BD, CAST(-7.5 AS FLOAT),null ),
(-80, 1, -10.2, -10.2BD, CAST(-10.2 AS FLOAT), '-1' ),
(9, -90, 2.58, 2.58BD, CAST(2.58 AS FLOAT), '0'),
(-100, 10, -5.8, -5.8BD, CAST(-5.8 AS FLOAT), '-90');

Query data from the mf_math_fun_t table. Sample statement:

SELECT * FROM mf_math_fun_t;
-- The following result is returned: 
+------------+-------------+-------------+--------------+------------+-------------+
| int_data   | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL       | -10         | 0.525       | 0.525        | 0.525      | 10          |
| -20        | NULL        | -0.1        | -0.1         | -0.1       | -10         |
| 0          | -1          | NULL        | 20.45        | -1.0       | 30          |
| -40        | 4           | 0.89        | NULL         | 0.89       | -30         |
| 5          | -50         | -1.0        | -1           | NULL       | 50          |
| -60        | 6           | 1.5         | 1.5          | 1.5        | -50         |
| -1         | -70         | -7.5        | -7.5         | -7.5       | NULL        |
| -80        | 1           | -10.2       | -10.2        | -10.2      | -1          |
| 9          | -90         | 2.58        | 2.58         | 2.58       | 0           |
| -100       | 10          | -5.8        | -5.8         | -5.8       | -90         |
+------------+-------------+-------------+--------------+------------+-------------+

Example with static data

-- Returns 1.144729868791239.
select ln(3.1415926);
-- Returns NULL.
select ln(null);

Example with table data

Calculate the natural logarithm based on the sample data using the following statement.

-- Disable strict mode.
set odps.sql.udf.strict.mode = false;
-- Set the data type version to 2.0.
set odps.sql.type.system.odps2 = true;
select ln(bigint_data) as bigint_new, ln(double_data) as double_new, ln(decimal_data) as decimal_new, ln(string_data) as string_new from mf_math_fun_t;

The following result is returned.

+--------------------+----------------------+---------------------+---------------------+
| bigint_new         | double_new           | decimal_new         | string_new          |
+--------------------+----------------------+---------------------+---------------------+
| NULL               | -0.6443570163905132  | -0.6443570163905132 | 2.302585092994046   |
| NULL               | NULL                 | NULL                | NULL                |
| NULL               | NULL                 | 3.017982882488811   | 3.4011973816621555  |
| 1.3862943611198906 | -0.11653381625595151 | NULL                | NULL                |
| NULL               | NULL                 | NULL                | 3.912023005428146   |
| 1.791759469228055  | 0.4054651081081644   | 0.4054651081081644  | NULL                |
| NULL               | NULL                 | NULL                | NULL                |
| 0.0                | NULL                 | NULL                | NULL                |
| NULL               | 0.9477893989335261   | 0.9477893989335261  | NULL                |
| 2.302585092994046  | NULL                 | NULL                | NULL                |
+--------------------+----------------------+---------------------+---------------------+

Related functions

LN is a mathematical function. For more information about functions for data calculation and conversion, see Mathematical functions.