Factorial of n using a simple SQL statement. Oracle Row Prefetching
have ever wondered why there is no aggregation function PROD, to obtain the product of a numeric column or expression from a table I do not think, right?. Very probably the majority, it has never generated this concern, because in fact, does not, a priori, much use in real systems. Anyway, the idea of \u200b\u200bthe note is to show how simple and elegant can be a simple solution using just a SELECT statement, regardless of whether I found some useful or not.
Friday, February 18, 2011
Does Hallee Hirsh Smoke
As a brief introduction, I will review the functions of aggregation, which are varied. The best known and most frequently used are:
SUM: Returns the sum of a column or expression
AVG: Returns the average of a column I or expression
MIN: Returns the minimum value
MAX: Returns the maximum value
Other features are the statistics that I use when I need to analyze trends and patterns, and are
STDDEV: Returns the standard deviation of a column or expression
VARIANCE: Returns the variance of a column or expression
MEDIAN: Returns the median of a column or expression
there are many more to use aggregate functions, see detail in the official manual for the BD version you need, for instance to 11g R2: SQL Reference 11.2 R2 (Aggregation Functions )
This time, I will show how to simulate PROD function using some simple mathematical rules, and finally, use this function to get the factorial of a given number:
To begin, I will define the two Functions I use:
ln (n) = Returns the natural logarimo
n and its inverse function:
exp (n) = Returns e raised to the n
Now, based on the following mathematical property of logarithms:
ln (a) + Ln (b) = ln (a * b)
high and with each side of the equation Previous:
exp (ln (a) + ln (b)) = exp (ln (a * b))
Since exp and ln are inverse functions, then vanish and we have a multiplied by b:
exp (ln (a * b)) = a * b
At this point, we have defined and explained the rule. Now I'll show an example of use to obtain the factorial
The SQL statement to get the factorial of N is as follows: select
exp (sum (ln (rownum))) from dual connect by rownum
<= N ;
as an example using sqlplus console:
rop @ DESA10G> variable n number
rop @ DESA10G> exec: n: = 3;
PL / SQL procedure successfully completed.
rop @ DESA10G> select exp (sum (ln (rownum))) from dual connect by rownum
2 <= :n ;
EXP (SUM (LN (ROWNUM))) ------------
-------- 6
rop @ DESA10G> exec: n: = 6;
PL / SQL procedure successfully completed.
rop @ DESA10G> select exp (sum (ln (rownum)))
2 from dual connect by rownum
<= :n ;
EXP (SUM (LN (ROWNUM))) --------------------
720
As shown, You can get the factorial of any number with a simple sentence. Could also be used to multiply the columns of a table, multiply expressions, etc..
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment