#37_140805_TUNING_MODEL 절

# MODEL 절

select empno, ename,year2,sal from emp700 model partition by (empno, ename) - 기준이 되는 컬럼 dimension by (year2) - 예상 연도에 대한 컬럼 measures (sal) - 새로 계산이 되는 컬럼   rules ( sal[1983] = sal[1982] * (sal[1982]/sal[1981]) ) order by year2,ename;

점심문제62. SQL model 절을 이용해서(게스글 299번) OECD년간 근로시간의 2014년도 데이터를 예상하시오

SELECT * FROM (  SELECT nation, SUBSTR(year,6) AS year, CNT   FROM LAB_TIME   unpivot (cnt FOR year IN (YEAR_2000,YEAR_2001,YEAR_2002,YEAR_2003,YEAR_2004,   YEAR_2005,YEAR_2006,YEAR_2007,YEAR_2008,YEAR_2009,YEAR_2010,YEAR_2011,YEAR_2012,YEAR_2013))  ) model   partition by (nation)   dimension by (year)   measures     (cnt)   rules (   cnt[2014] = cnt[2013] * (cnt[2013]/avg(cnt) [ year between 2000 and 2013 ])   ) order by year desc;

# R 에서 worktime4 <- expection.csv="" header="TRUE)<o:p" read.csv="">

t2 <- gvismotionchart="" idvar="NATION" o:p="" timevar="YEAR" worktime4="">

plot(t2)

데이터 그래프 조회 : http://127.0.0.1:11066/custom/googleVis/MotionChartID86869bb47cc.html

점심문제. model 절. 1회부터 현재까지 담청 로또번호를 테이블에 집어넣고 모델절을 이용해서 담첨 예상 로또번호를 생성. 각번호별 평균값으로 예상 로또번호 생성

drop table lotto;

create table lotto

(num number(10),

lot_date date,

num1 number(10),

num2 number(10),

num3 number(10),

num4 number(10),

num5 number(10),

num6 number(10),

num_bonus number(10));

select * from lotto;

commit;

SELECT * FROM (   SELECT num, TO_CHAR(lot_date,'RRRR') year, numm, cnt   FROM lotto   unpivot (cnt FOR numm IN (NUM1,NUM2,NUM3,NUM4,NUM5,NUM6,num_bonus)) ) model   partition by (numm)   dimension by (num)   measures     (cnt)   rules (   cnt[607] = TRUNC(avg(cnt) [ num between 1 and 606 ])   ) order by num desc, numm asc;

SELECT *   FROM lotto model   dimension by (num)   measures     (num1,num2,num3,num4,num5,num6,num_bonus)   rules (   num1[607] = TRUNC(avg(num1) [ num between 1 and 606 ]),   num2[607] = TRUNC(avg(num2) [ num between 1 and 606 ]),   num3[607] = TRUNC(avg(num3) [ num between 1 and 606 ]),   num4[607] = TRUNC(avg(num4) [ num between 1 and 606 ]),   num5[607] = TRUNC(avg(num5) [ num between 1 and 606 ]),   num6[607] = TRUNC(avg(num6) [ num between 1 and 606 ]),   num_bonus[607] = TRUNC(avg(num_bonus) [ num between 1 and 606 ])   ) order by num desc;