{"id":3150,"date":"2025-06-30T08:49:30","date_gmt":"2025-06-30T08:49:30","guid":{"rendered":"https:\/\/aprogrammerwrites.eu\/?p=3150"},"modified":"2025-06-30T08:49:30","modified_gmt":"2025-06-30T08:49:30","slug":"coupons-caps-and-functions-intro","status":"publish","type":"post","link":"https:\/\/aprogrammerwrites.eu\/?p=3150","title":{"rendered":"Coupons, Caps and Functions – Intro"},"content":{"rendered":"

I recently posted an article on my GitHub Pages blog, Coupons, Caps and Functions<\/a>.<\/p>\n

[Here is the introduction…]<\/h2>\n

On 18 April 2025 a poster asked a question on the AskTom Oracle forum:
\nSolving a logical problem using analytical functions<\/a>. The problem turned out to be surprisingly difficult to solve in pure SQL.<\/p>\n

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<\/a>. The analysis includes curve-fitting using Excel graphs and statistical functions.<\/p>\n

The methods are tested using The Math Function Unit Testing Design Pattern<\/a>, with test results in HTML and text format included.<\/p>\n

Everything is automated.<\/p>\n

GitHub<\/h3>\n

<\/p>\n