robios::blog: 目から鱗の数値関数

目から鱗の数値関数 - 総積

August 17, 2004

Oracleの関数には、総和(SUM)はあるが総積(全ての値を掛け合わしたもの)はない。平均(AVG)や標準偏差(STDDEV)はあるのに、もっと単純な総積がない。

ないなら作るまでと思いきや、実はもっと単純な方法があった。全くもって、目から鱗であった。

詰まる所、数学的に総積を総和の形へ変換することで計算可能となる。下の数式をご覧頂きたい。

下段のe(自然対数の底、ネピア)の指数が、aとbそれぞれの自然対数の和になっている。値が増えても同じように変換される。

これをSQLで表すと、下記の様になる。

SELECT EXP(SUM(LN(column))) FROM table;

すなわち、columnの各値の自然対数を取り、それを総和し、最終的にeの指数とすれば、総積となる。

しかし、このままではマイナスの数とゼロが含まれた総積がうまく表現できない。そこで、

SELECT
  EXP(SUM(LN(ABS(DECODE(column, 0, 1, column))))) *
  DECODE(MOD(COUNT(DECODE(SIGN(column), -1, 1, NULL)), 2), 1, -1, 1) *
  NVL(MAX(DECODE(column, 0, 0, NULL)), 1)
FROM table;

とすれば、うまく表現可能となる。(1段目では絶対値をとりさらに0の場合は1とし、2段目でマイナスの数をカウントし奇数個であれば全体に-1を掛け、3段目でゼロを検索しあれば全体にゼロを掛ける。最早力ずく、笑)

所で、この方法では計算結果に誤差がでてしまう可能性がある。勘定系での使用は要注意(勘定系でこんな方法そもそも使うわけないか)。

参考: Ask Tom: Mechanism to perform the product of a single column in a multirow table

TrackBack

TrackBack URL for this entry:
http://www.robios.org/x/mt/mt-tb.cgi/25

Comments

prevacid prevacid

Posted by: prevacid at February 7, 2007 07:22 AM

If you wanna stop guestbook spammers just confirm url of this page to anti.spam.police@gmail.com with subject:ANTISPAM. Thx.

Posted by: Greg at February 5, 2007 07:29 PM

If you wanna stop guestbook spammers just confirm url of this page to anti.spam.police@gmail.com with subject:ANTISPAM. Thx.

Posted by: Greg at February 5, 2007 04:17 PM

Good site. Thanks:-)

Posted by: Human Anatomy And Physiology at February 5, 2007 01:09 PM

hydrocodone online hydrocodone online

Posted by: hydrocodone online at February 1, 2007 11:53 PM

Helloatj - this is just a testing, don't worry about it

Posted by: Testercmf at January 29, 2007 01:19 AM

sex webcams sex webcams

Posted by: sex webcams at January 19, 2007 10:43 PM

discount pharmacy

Posted by: discount pharmacy at January 1, 2007 09:55 AM

Good site. Thank you!

Posted by: find job at December 31, 2006 03:23 PM

Good site. Thank you.

Posted by: currency forex rate at December 31, 2006 12:14 AM

orejnkg rvayol oyrwq ikmlxsa tukyih nwdxycph sbeolx dzyqhmva zpatyul

Posted by: dvfuyhjmq wysozl at December 30, 2006 10:51 PM

wxjyfb bxdemti jlfsge hkpyqwbj qutncihm fgsemjip qwtj

Posted by: gvkrl qtaigrc at December 30, 2006 10:51 PM

Nice ...nice...
flower delivery minneapolis

Posted by: flowers at December 28, 2006 09:11 PM

Warning!
Hardcore Adult
Material!

Posted by: Bill at December 25, 2006 06:00 AM

Awesome site! Design is great! Please visit my site too:
[url=][/url]

Posted by: Kathy at December 18, 2006 10:43 PM

Awesome site! Design is great! Please visit my site too:
[url=][/url]

Posted by: Kathy at December 18, 2006 10:43 PM

Start here to bank and pay bills online. Wells Fargo provides personal banking

Posted by: Fargo at December 13, 2006 05:57 AM

Set a thief to catch a thief... Christopher

Posted by: Christopher at November 29, 2006 07:16 PM

hairy brunette adult dvd shower masturbation rape poems teen anal free scat videos free videos blonde babes amanda bynes naked nipple torture hot asian school girl amateur milf up close nude vagina celebrity upskirt sex toon hairy redhead pussy true voyeur teen gangbang beastiality video pornstar gauge animal sex stories free hentai videos horny lesbians jessica simpson nude old pussy sex toys mature hardcore shaved pussy gallery cheerleaders nude stocking tops interracial breeding cuckold granny pics teen cumshots english to latin my big fat obnoxious boss free sex webcams drinking piss beach topless bikini girls dick small boys car models black lesbian stephanie mcmahon nude ejaculation female movies free petite blonde gay studs naked girl incest story blowjob movie clips celebrity porn milk breasts hot naughty women shemale thumbnails girls ass taylor rain anal i fuck mom car sex

Posted by: porn search engines at November 25, 2006 09:31 AM

Posted by: porn search engines at November 25, 2006 09:30 AM

Era Replica Automobiles is the premiere maker of Cobr replicas, both the 427SC and the 289FIA. ERA

Posted by: Gogi at November 23, 2006 04:04 AM

Habit cures habit... Josias

Posted by: Josias at November 22, 2006 06:58 PM

addiction hydrocodone addiction hydrocodone

Posted by: addiction hydrocodone at November 19, 2006 05:44 AM

Hi all

Posted by: Bill at November 12, 2006 04:31 AM

If you are suffering from many physical problems like diabetes, heart disease, and your sexual life is at stake due to ED, it is time to buy levitra, the best treatment for erectile dysfunction.

Posted by: levitra at November 1, 2006 08:13 PM

Nice!

Posted by: pagerank at October 13, 2006 05:56 PM

elkay stainless sink elkay stainless sink

Posted by: elkay stainless sink at October 3, 2006 11:15 PM

hydrocodone addiction

Posted by: hydrocodone addiction at October 3, 2006 07:14 PM

Yo! Cool stuff! Thanks for being here. Would you please also visit my homepage?

[url=][/url]

Good luck! :)

Posted by: diazepam at September 28, 2006 10:37 PM

Hi! Good Site! Keep Doing That! :) Please also visit my site:

[url=][/url]

Good luck! :)

Posted by: prevacid at September 26, 2006 09:52 PM

Hi! Good Site! Keep Doing That! :) Please also visit my site:

[url=][/url]

Good luck! :)

Posted by: prevacid at September 26, 2006 09:52 PM

Nice page greetings to all in this guestbook! Please visit my homepage:

[url=][/url]

Good luck! :)

Posted by: acyclovir at September 26, 2006 09:42 PM

This is a wonderful page for everyone! Visit my sites, please:

[url=][/url]

Good luck! :)

Posted by: valtrex at September 26, 2006 09:42 PM

You have a great page! Please visit my homepage too:

[url=][/url]

Good luck! :)

Posted by: zocor at September 26, 2006 09:42 PM

You have a great page! Please visit my homepage too:

[url=][/url]

Good luck! :)

Posted by: zocor at September 26, 2006 09:42 PM

Hello nice site thank you.!

Posted by: online pharmacy at June 18, 2006 06:38 PM

Hi all I'm new here tell me please the color of your site. Thank you.!

Posted by: buy fioricet online at May 31, 2006 06:20 PM

Hello guys what a wonderfull resource thank you all!

Posted by: tooth whitening los angeles at May 21, 2006 09:13 PM

This is best site i see thank you.!

Posted by: buy diazepam online at May 8, 2006 02:22 AM

This is best site i see thank you.!

Posted by: tooth whitening chicago at May 6, 2006 06:03 PM

This is best site i see thank you.!

Posted by: tooth whitening at May 3, 2006 11:54 PM

get hold of impotence and erectile dysfunction. erectile problems are no more an issue with Viagra. get the best Viagra deal at http://www.viagracare.com

Posted by: viagra at April 24, 2006 02:50 PM

forget your worries,fears and panic attacks,Xanax can aid in fighting mental odds and blocks.overcome anxiety disorder with Xanax

Posted by: xanax at April 24, 2006 02:45 PM

hello everybody really nice site!

Posted by: buy hydrocodone online at April 17, 2006 02:37 AM

hello everybody really nice site!

Posted by: buy vicodin online at April 9, 2006 07:45 PM

hello everybody really nice site!

Posted by: alprazolam at March 24, 2006 09:41 PM

hello everybody really nice site!

Posted by: alprazolam at March 24, 2006 09:41 PM