A bit of fun expressing ratios

Sometimes answering questions on the OTN forum leads to a little fun trying to be creative in SQL ;-) A user wished to express a ratio as 1:1 or 1:2. That lead to a little fun with CONNECT BY on DUAL for recursion.


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.