2009-08-14

SQL Donut

Oracle version of http://code.openark.org/blog/mysql/sql-pie-chart. Just change the val query to greate your own donuts.

######
Note 17.8.2009 wm_concat does not quarantee the right col_number order. Update to this post coming in future.
Note 2.9.2009 replaced wm_concat implementation to listagg. New in 11.2
######


:s size
:r radius
:t stretch

with val as (
select 'red' name_column, 1 value_column from dual
union all
select 'blue' name_column, 2 value_column from dual
union all
select 'orange' name_column, 3 value_column from dual
union all
select 'white' name_column, 4 value_column from dual
), ratio as (
select row_number() over (order by v.value_column,rownum) name_order
, v.name_column
, v.value_column
, sum(value_column) over (order by value_column,rownum) accumulating_value
, sum(value_column) over (order by value_column,rownum)
/ sum(value_column) over () accumulating_value_ratio
, 2*3.15*sum(value_column) over (order by value_column,rownum)
/ sum(value_column) over () accumulating_value_radians
, trunc(100*value_column / sum(value_column) over (),2) pc
from val v
), t1 as (
select level-1 value from dual connect by level <= to_number(:s)*to_number(:t)
), t2 as (
select level-1 value from dual connect by level <= :s
), c as (
select '#;o:X"@+-=123456789abcdef' as colors from dual
), ro as (
select t1.value col_number
, t2.value row_number
, t1.value/:t - (:s-1)/2 dx
, (:s-1)/2 - t2.value dy
from t1, t2
)
, ro2 as (
select ro.*, case when dx = 0
then 3.14/2
else (atan(abs(dy/dx)))
end abs_radians
from ro)
, ro3 as (
select ro2.*, case when sign(dy) >= 0 and sign(dx) >=0 then abs_radians
when sign(dy) >= 0 and sign(dx) <=0 then 3.14-abs_radians
when sign(dy) <= 0 and sign(dx) <=0 then 3.14+abs_radians
when sign(dy) <= 0 and sign(dx) >=0 then 2*3.14-abs_radians
end radians
from ro2
)
select listagg(case when round(sqrt(power(col_number/:t-0.5-(:s-1)/2, 2)
+ power(row_number-(:s-1)/2, 2))) BETWEEN :r/2 AND :r
then rpad(name_order,:t,name_order)
else rpad(' ',:t,' ')
end) within group (order by col_number) circle
from (select substr(colors, name_order, 1) name_order
, row_number
, col_number
, row_number() over (partition by col_number,row_number
order by name_order) c
from ratio, ro3, c
where accumulating_value_radians >= radians
) where c = 1
group by row_number
union all
select rpad(substr(colors,name_order,1),:t,substr(colors,name_order,1))||' '||
name_column||' '||
value_column||' ('||
pc||'%)'
from ratio, c
;


CIRCLE
oooooo;;;;;;;;
oooooooooo;;;;;;;;;;;;
oooooooo ;;######
oooooooo ########
oooooooo ::::::::
oo::::::::::::::::::::
::::::::::::::
## red 1 (10%)
;; blue 2 (20%)
oo orange 3 (30%)
:: white 4 (40%)


No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.