This is the SQL I ended up creating:

with r as ( select .2233 ratio from dual union all select .2500 ratio from dual union all select .2666 ratio from dual union all select .2750 ratio from dual union all select .2828 ratio from dual ) select r.ratio ratio_num , ( select to_char( max(level) , 'TM9' ) || ':' || to_char( max(round(level / r.ratio)) keep (dense_rank last order by level) , 'TM9' ) from dual connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > 1 and level <= 1000 ) ratio_txt from r /

It outputs this:

RATIO_NUM RATIO_TXT ---------- ---------- ,2233 1:4 ,25 1:4 ,2666 1:4 ,275 1:4 ,2828 1:4

Now I change a number 1 to 0.1:

with r as ( select .2233 ratio from dual union all select .2500 ratio from dual union all select .2666 ratio from dual union all select .2750 ratio from dual union all select .2828 ratio from dual ) select r.ratio ratio_num , ( select to_char( max(level) , 'TM9' ) || ':' || to_char( max(round(level / r.ratio)) keep (dense_rank last order by level) , 'TM9' ) from dual connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .1 and level <= 1000 ) ratio_txt from r /

Now the output becomes:

RATIO_NUM RATIO_TXT ---------- ---------- ,2233 2:9 ,25 1:4 ,2666 1:4 ,275 1:4 ,2828 2:7

If I use 0.01 it becomes:

RATIO_NUM RATIO_TXT ---------- ---------- ,2233 2:9 ,25 1:4 ,2666 4:15 ,275 3:11 ,2828 7:25

If I use 0.001 it becomes:

RATIO_NUM RATIO_TXT ---------- ---------- ,2233 21:94 ,25 1:4 ,2666 4:15 ,275 11:40 ,2828 13:46

How it works?

The connect by abs(.....) > <precision> keeps recursing until the difference between the true ratio and the fraction is less than the desired precision. When we ask for precision 0.1, we get to 2 divided by 7 = 0.28571429 which is sufficiently precise. When we ask for precision 0.01 we continue recursing until 7 divided by 25 = 0.28000000.

The and level <= 1000 is just to make sure we don't get an infinite loop.

keep dense_rank last order by level is used to get the last level / ratio value of the recursion.

For a different example try combining it with ratio_to_report():

select deptno , ename , sal , ratio_to_report(sal) over ( partition by deptno ) ratio from scott.emp order by deptno , empno /

That analytic function gives us this output:

DEPTNO ENAME SAL RATIO ---------- ---------- ---------- ---------- 10 CLARK 2450 ,28 10 KING 5000 ,571428571 10 MILLER 1300 ,148571429 20 SMITH 800 ,073563218 20 JONES 2975 ,273563218 20 SCOTT 3000 ,275862069 20 ADAMS 1100 ,101149425 20 FORD 3000 ,275862069 30 ALLEN 1600 ,170212766 30 WARD 1250 ,132978723 30 MARTIN 1250 ,132978723 30 BLAKE 2850 ,303191489 30 TURNER 1500 ,159574468 30 JAMES 950 ,10106383 14 rows selected.

And then apply the connect by on it:

select deptno , ename , sal , dept_sal , ( select to_char( max(level) , 'TM9' ) || ':' || to_char( max(round(level / ratio)) keep (dense_rank last order by level) , 'TM9' ) from dual connect by abs(((level-1) / round((level-1) / ratio) / ratio)-1) > .1 and level <= 1000 ) ratio_txt from ( select deptno , empno , ename , sal , sum(sal) over ( partition by deptno ) dept_sal , ratio_to_report(sal) over ( partition by deptno ) ratio from scott.emp ) order by deptno , empno /

And you get a ratio stating that 3 out of 5 salary dollars in department 10 goes to King:

DEPTNO ENAME SAL DEPT_SAL RATIO_TXT ---------- ---------- ---------- ---------- ---------- 10 CLARK 2450 8750 2:7 10 KING 5000 8750 3:5 10 MILLER 1300 8750 1:7 20 SMITH 800 10875 1:14 20 JONES 2975 10875 1:4 20 SCOTT 3000 10875 1:4 20 ADAMS 1100 10875 1:10 20 FORD 3000 10875 1:4 30 ALLEN 1600 9400 1:6 30 WARD 1250 9400 1:8 30 MARTIN 1250 9400 1:8 30 BLAKE 2850 9400 1:3 30 TURNER 1500 9400 1:6 30 JAMES 950 9400 1:10 14 rows selected.

Peter vd Zwan gave a different answer in the forum using Greatest Common Denominator to give a ratio expression without any loss of precision. Also a fun piece of SQL ;-)

But for human beings reading the ratio, it is more difficult to relate to

*69 out of 244*or even

*13 out of 46*, but

*7 out of 25*is more acceptable and

*2 out of 7*is very quickly understood. So the precision you want is always a compromise between exactness and readability.

## No comments:

## Post a Comment