Re: More complex median calculation in Oracle SQL
Date: Fri, 27 Aug 2010 09:34:44 -0700 (PDT)
Message-ID: <644ef813-5a90-4075-85ed-8fcc77468d8c_at_o7g2000prg.googlegroups.com>
On Aug 27, 12:09 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
> Hello,
>
> Thanks for your answers.
>
> Mark: No, those functions don't do what I want. However I use 10g.
>
> Joel: Yes, you are right, this is a solution. However I thought there
> might have been an easier solution than the following sql which's
> first line is the answer I look for.
>
> select
> unit_price,
> running_total / total as perc
> from
> (select
> unit_price,
> sum(total_turnover) over(order by unit_price ROWS UNBOUNDED
> PRECEDING) running_total,
> sum(total_turnover) over() total
> from
> (select
> unit_price,
> sum(unit_price * sold_units) total_turnover
> from
> tmp_articles
> group by
> unit_price
> order by
> unit_price
> )
> )
> where
> running_total / total >= 0.5
> order by
> 1 ;
>
> But on the other hand the sql is not so complicated for the very
> specific thing I was looking for. I just thought that within
> statistics such questions where very common and thus a predefined
> function would exist.
>
> Best,
>
> Hans
That's true. People who do serious statistics often have third party software to do the specific things they need. Oracle analytics is catching up, plus with newer standards of SQL more can be pushed into the database engine. On the other hand, sometimes pushing things into the engine hits up against limitations within the engine. Since those limitations are evolving (like what Exadata can do, like http://kerryosborne.oracle-guy.com/2010/08/oracle-exadata-storage-indexes/ ), this could be an exciting time for actuaries. And you know what they say about actuaries - someone has to make accountants look exciting.
It's hard to overcome inertia of a mature market. But that's why Larry runs around buying companies, some of whom are past their prime. There's some predictions floating around about what Larry will buy next ( http://www.softwareadvice.com/articles/enterprise/oracle-mergers-acquisitions-whos-next-1080310/ ), I think the stats market has perhaps been overlooked, overshadowed by BI. Or maybe it is just too open-source oriented (besides SAS) for Larry to comprehend.
jg
-- _at_home.com is bogus. http://projects.washingtonpost.com/top-secret-america/functions/cyber-ops/Received on Fri Aug 27 2010 - 11:34:44 CDT