Re:1. Tic Tac Toe 9개의 컬럼중 가장 중요한 컬럼이 무엇인지 학습 데이터로 정보획득률을 출력하시오

[5기_지혜승]

create or replace procedure p1
is
v_stmt1 varchar2(1000);
v_stmt2 varchar2(1000);
v_num1 number(10,3);
v_num2 number(10,3);
v_num3 number(10,3);
v_num4 number(10,3);
v_result varchar2(20);
v_entropy1 number(10,3):=0;
v_entropy2 number(10,3):=0;
v_info    number(10,3);
v_colname varchar2(20);
type refcursor is ref cursor;
entropy_cv1 refcursor;
entropy_cv2 refcursor;

begin

 for entropy_record in (select column_name
            from dba_tab_columns
       where table_name='TIC_TAC_TOE') loop
       
 v_colname := entropy_record.column_name;
 if v_colname!= 'RESULT' then
 v_stmt1 := 'select count(*), (select count(*) from tic_tac_toe where '||v_colname||'!=1)
    from tic_tac_toe
    where '||v_colname||' !=1
    group by '||v_colname;
 
 v_stmt2 := 'select result, cnt1,sum(cnt1) over (partition by '||v_colname||'),sum(cnt1) over (),(select count(*) from tic_tac_toe) 
from (          select result,'||v_colname||',count(*) cnt1, (select count(*) from tic_tac_toe where '||v_colname||'!=1 group by '||v_colname||')
    from tic_tac_toe
    where '||v_colname||' !=1
    group by '||v_colname||',result)';
 
 open entropy_cv1 for v_stmt1;
 
 loop
  fetch entropy_cv1 into v_num1,v_num2;
  exit when entropy_cv1%notfound;
   v_entropy1 := v_entropy1 - (v_num1/v_num2)*log(2, (v_num1/v_num2));
 end loop;
 
 open entropy_cv2 for v_stmt2;
 
 loop
  fetch entropy_cv2 into v_result,v_num1,v_num2,v_num3,v_num4;
  exit when entropy_cv2%notfound;
   v_entropy2 := v_entropy2 - (v_num2/v_num3)*(v_num1/v_num2)*log(2, (v_num1/v_num2));
 end loop;
 
  v_info := v_entropy1 - v_entropy2;
  dbms_output.put_line(v_colname||'에 두었을 때 분할 전 엔트로피:'||v_entropy1);
  dbms_output.put_line(v_colname||'에 두었을 때 분할 후 엔트로피:'||v_entropy2);
  dbms_output.put_line('정보획득량: '||v_info);
 
  v_entropy1:=0;
  v_entropy2:=0;
  end if;
  end loop;
end;
/