loading..
Русский    English
21:15

LOG and EXP functions

The LOG(x) function returns the natural logarithm of the expression x. The returned result is of type   Тип данных с плавающей точкой. float.

Starting with  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2012, this function received an optional argument for specifying the base of the logarithm.

Console
Execute
  1. SELECT LOG(2) ln, LOG(2, 10) log10;

logn    log10
0.693147180559945    0.301029995663981

This query returns the natural and the base-10 logarithms of 2.

Besides, there is the function LOG10(x) inherited from previous versions that returns the base-10 logarithm of the expression x. It has been redundant from the very beginning, since instead of it, the well-known change-of-base formula for logarithms can be used:

LOGba = LOGca/LOGcb

Thus, the following three expressions will return the same result (0.301029995663981):

Console
Execute
  1. SELECT LOG(2,10) log_1, LOG10(2) log_2, LOG(2)/LOG(10) log_3;

The function EXP(x), or the exponential function, returns the number e raised to the power of x. The returned result is of type FLOAT.

This function is inversely related to the LOG function:

Console
Execute
  1. SELECT EXP(LOG(2)) a, LOG(EXP(2)) b;

a    b
2    2

Another useful feature of logarithms - namely, the fact that the logarithm of a product is the sum of the logarithms of the factors - allows us to calculate the product of values in a column, i.e.

  1. log(a*b*c) = log(a) + log(b) + log(c).

We can easily prove the correctness of this equality by the following example:

Console
Execute
  1. SELECT LOG(2*5*7) log_prod,  LOG(2) + LOG(5) + LOG(7) sum_log;

log_prod    sum_log
4,24849524204936    4,24849524204936

Among the SQL aggregate functions, there isn't any for multiplying values. However, using the aforementioned feature of logarithms and some elementary transformations, we can reduce this task to calculating a sum of values. Indeed,

  1. a*b*c = exp(log(a*b*c)) = exp(log(a) + log(b) + log(c)).

Calculate the factorial of the number equal to the quantity of rows in the Laptop table.

Solution

Console
Execute
  1. SELECT EXP(SUM(LOG(rn))) FROM(
  2. SELECT ROW_NUMBER() OVER(ORDER BY code) rn FROM laptop
  3. ) X;

720
In the nested query, we numbered the records in the Laptop table using the ROW_NUMBER ranking fuction. (Note that the order in which the rows are numbered doesn't matter here). Then, we just multiplied these numbers.

Suggested exercises: 106

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
today
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100