Баловство с модельками


Первые мои баловства с модельками: Таблица умножения:
  1. select * from
  2. (
  3.     SELECT *
  4.     FROM dual d
  5.         MODEL
  6.              DIMENSION BY (1 x,1 y)
  7.              MEASURES (1 mpl)
  8.              RULES ITERATE (100) (
  9.                  mpl[
  10.                     trunc(iteration_number/10)+1,
  11.                     mod(iteration_number,10)+1
  12.                  ] = (trunc(iteration_number/10)+1)*(mod(iteration_number,10)+1)
  13.              )
  14. )
  15. pivot(
  16.  sum(mpl)
  17.  for (y) in (1,2,3,4,5,6,7,8,9,10)
  18. )
  19. order by x
* This source code was highlighted with Source Code Highlighter.
Генерация дат для групп:
with facility_month as (                         select 'F001' facility,to_date('1/1/2008','mm/dd/yyyy') eff_month from dual union all                         select 'F001',to_date('2/1/2008','mm/dd/yyyy') from dual union all                         select 'F001',to_date('3/1/2008','mm/dd/yyyy') from dual union all                         select 'F001',to_date('4/1/2008','mm/dd/yyyy') from dual union all                         select 'F001',to_date('5/1/2008','mm/dd/yyyy') from dual union all                         select 'F001',to_date('6/1/2008','mm/dd/yyyy') from dual union all                         select 'F001',to_date('7/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('4/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('5/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('6/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('7/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('8/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('9/1/2008','mm/dd/yyyy') from dual union all                         select 'F002',to_date('10/1/2008','mm/dd/yyyy') from dual                       ) ,t as (      select facility, min(EFF_MONTH) min      from FACILITY_MONTH f      group by facility ) select * from t model      partition by (facility,min)      dimension by (1 as y)      measures(sysdate as dt)      rules iterate(1000) until (dt[iteration_number]>=trunc(sysdate,'mm')) (         dt[iteration_number] = add_months(cv(min),iteration_number)      ) order by facility,y * This source code was highlighted with Source Code Highlighter.

Comments

Отправка комментария