I recently posted an article on my GitHub Pages blog, Coupons, Caps and Functions.
[Here is the introduction…]
On 18 April 2025 a poster asked a question on the AskTom Oracle forum:
Solving a logical problem using analytical functions. The problem turned out to be surprisingly difficult to solve in pure SQL.
I posted two solution methods on the thread for a generalised form of the problem. One solution used Oracle’s MODEL clause, the other a pipelined function. In this article, I explain how these work, and add a third method based on recursive subquery factoring. In addition, I analyse the performance characteristics of the three methods using an approach I presented at the 2017 Irish Oracle User Group conference in Dublin, Dimensional Performance Benchmarking of SQL. The analysis includes curve-fitting using Excel graphs and statistical functions.
The methods are tested using The Math Function Unit Testing Design Pattern, with test results in HTML and text format included.
Everything is automated.
GitHub
