Last week at the ODTUG conference, I did a presentation on Analytic Functions. Later on someone came up to me and asked me if there is also a Median-function in Oracle. At that time I was pretty sure there is no such a function in Oracle SQL.
He told me that he wrote a quite elaborate function to perform a Median calculation, but it wat quite costly. This functions worked quite well. There was a downside however, using this function with a large dataset took a long time to complete.
My first suggestion was to use the Oracle Data Cartridge to leverage the power of the Oracle database to perform such calculations. This might improve performance significantly.
The nice thing of using the Oracle Data Cartridge is that it's "Analytic"-friendly. This means that the function you create that implements the Oracle Data Cartridge can be used as an Analytic Function. In the next section I will show you an example of using the Oracle Data Cartridge as an Analytic Function.
Analytic Function and the Oracle Data Cartridge
In order to use the Oracle Data Cartridge, you need to cohere to the specification of the Oracle Data Cartridge. The complete reference guide can be found here.
In short it boils down to:
- Create an Object that implements the Oracle Data Cartridge Interface
- Also Implement the Objects body which specifies the functionality
- Create a function that uses this Object, with the AGGREGATE USING keywords
And that's it.
In the demo-file that comes in this blog, I create a function that "aggregates" strings. With this function you can execute queries just like:
SQL> select Amisvarchar2Sum(dname) sumstring 2 from dept d 3 / SUMSTRING -------------------------------------------- ACCOUNTING, RESEARCH, SALES, OPERATIONS
And because these User Defined Aggregates are "Analytic Friendly" you can also use them like
SQL> select ename 2 , amisvarchar2sum (ename) over (partition by deptno 3 ) sumstring 4 from emp 5 / ENAME SUMSTRING ---------- -------------------------------------------------- CLARK CLARK, KING, MILLER KING CLARK, KING, MILLER MILLER CLARK, KING, MILLER SMITH SMITH, ADAMS, FORD, SCOTT, JONES ADAMS SMITH, ADAMS, FORD, SCOTT, JONES FORD SMITH, ADAMS, FORD, SCOTT, JONES SCOTT SMITH, ADAMS, FORD, SCOTT, JONES JONES SMITH, ADAMS, FORD, SCOTT, JONES ALLEN ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD BLAKE ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD MARTIN ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD JAMES ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD TURNER ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD WARD ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD 14 rows selected.
Or do an "upside down anchored window" (I don't know why you would want to, but it's possible):
SQL> select ename 2 , amisvarchar2sum(ename) over (partition by deptno 3 order by ename 4 range between current row 5 and unbounded following 6 ) sumstring 7 from emp 8 / ENAME SUMSTRING ---------- -------------------------------------------------- CLARK CLARK, KING, MILLER KING KING, MILLER MILLER MILLER ADAMS ADAMS, FORD, JONES, SCOTT, SMITH FORD FORD, JONES, SCOTT, SMITH JONES JONES, SCOTT, SMITH SCOTT SCOTT, SMITH SMITH SMITH ALLEN ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD BLAKE BLAKE, JAMES, MARTIN, TURNER, WARD JAMES JAMES, MARTIN, TURNER, WARD MARTIN MARTIN, TURNER, WARD TURNER TURNER, WARD WARD WARD 14 rows selected.
Google is your friend
Before I started out to create a Median function using the Oracle Data Cartridge, it's always a good idea to Google and see if something similar already exists.
And lo and behold the Median is implemented in Oracle 10 release 2.
But what to do if your database has a lower version? You can use the Analytic Function PERCENTILE_CONT with a value of 0.5 percentile. This will give you the same answer but is less descriptive than the new MEDIAN function.
Conclusion
In conclusion, before you start developing all kind of fancy new functionality. Check if it is not implemented in the Database already, it can save you lots of time and effort. When I found that the MEAN function was already implemented I told the man asking the question and that really made his day. It's always nice if you can help someone, gives you a nice fuzzy feeling
For a definition of the Median function I refer you to this Google listing.
The Demo script can be found here.
Ok, I didn’t read the first weblog. A colleque pointed me to this log, because we use a lot of Analytical functions ourselves and we were dealing with string aggregation some time ago. And the code in the example looked very familiar, I used it and it workes fine, I found it on the website I pointed to, there are also some other techniques described to aggregate strings. And yes, I sure like it that the User Defined Aggregate functions can be used as an Analytical Function.
Not sure what your point is. The issue here is not creating the user defined aggregate – that was first described on our weblog back in July 2004 (Oracle Data Cartridge – Extending the Database) so that is not what we tried to dazzle you with (not sure what the link you refer to has its script from though….). The news if you like in this article is that User Defined Aggregate functions can be used as an Analytical Function! That means that you not only can calculate simple aggregates using straightforward group by expressions, but you can also apply the over () clause with partitioning, order by and windowing clause to them! Now there is something cool.
best regards,
Lucas
Het demo-script komt me bekend voor, ben ik enige tijd geleden op onderstaande site tegengekomen:
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php#user_defined_aggregate_function