tag:blogger.com,1999:blog-7395977411859619892.post1294904748088672271..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: Exponential Moving AverageRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-7395977411859619892.post-65145949032750616572012-03-29T00:14:02.842+02:002012-03-29T00:14:02.842+02:00I tackled another question: how to optimize the sm...I tackled another question: how to optimize the smoothing constant?<br /><br />SELECT k -- smoothing constant<br /> , mse -- mean square error<br /> FROM (<br />SELECT *<br /> FROM sales<br />MODEL<br /> DIMENSION <br /> BY ( product<br /> , ROW_NUMBER() OVER <br /> (PARTITION BY product<br /> ORDER BY month ASC ) rn <br /> )<br /> MEASURES<br /> ( amount -DukeGanotehttps://www.blogger.com/profile/01714681520904502051noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-42899135752606684562010-06-05T15:33:29.558+02:002010-06-05T15:33:29.558+02:00Hi Raymond,
This is a great example of "fun ...Hi Raymond,<br /><br />This is a great example of "fun with SQL" :-)<br /><br />A combination of XMLQuery, the undocumented wm_concat, and analytic functions with the windowing clause. I like it. Although it is not as comprehensive as the model clause variant and the Rafu's recursive with one, as you said yourself.<br /><br />And sure, I'd like to see the proof of the closed Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-59858065372578331832010-06-03T17:35:40.825+02:002010-06-03T17:35:40.825+02:00Hi Rob,
after computing the closed form I came up...Hi Rob,<br /><br />after computing the closed form I came up with the following code that if more like an obfuscation than anything comprehensive. <br />The idea is to create running multiples using a string concatenation and the xml-eval-functionality.<br />The closed forms of the special cases only need running sums.<br /><br /><br />There's a general case and two special cases that are Raymond Martenshttps://www.blogger.com/profile/01744035066455048881noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-28721709194445211112010-06-02T00:46:11.580+02:002010-06-02T00:46:11.580+02:00Nice one, Rafu!Nice one, Rafu!Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-44888087355121455302010-06-01T23:16:11.945+02:002010-06-01T23:16:11.945+02:0011.2 features in use
with dat as (
select 'A&...11.2 features in use<br /><br />with dat as (<br />select 'A' product , date '2009-01-01' month, 10 amount from dual union all<br />select 'A', date '2009-02-01', 15 from dual union all<br />select 'A', date '2009-03-01', 17 from dual union all<br />select 'A', date '2009-04-01', 20 from dual union all<br />select 'A', date &Rafuhttps://www.blogger.com/profile/09149025430133739471noreply@blogger.com