Home » SQL & PL/SQL » SQL & PL/SQL » model-clause (10g)
model-clause [message #442766] Wed, 10 February 2010 07:57 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Dear all,

In a table temp, I have monthly data (datex [yyyymm]) for several products (prodid). The field datex_lastmonth shows the last month of datex. Now, I want to set a flag IsOK, which shall be 1 as long as data is available for every month and 0 when a month has been missing (see temp_res). I have a code which works, but it is very slow when I apply this for large tables. I'm not very familiar with the model clause, but I'm not sure whether I have to set all parameters (P1-P7) as measures since I don't need them in the model (but I want them in the result table). Any idea how I can solve this problem faster and better?

Thanks, Stefan


create table temp_res as
select *
from ( select a.*,
              rank() over ( partition by prodid 
                            order by datex desc )
                            as RANK,
              1 as IsOK
       from temp a ) 
model
partition by (prodid)
dimension by (RANK)                                                                      
measures ( datex,
           datex_lastmonth,
           p1,
           p2,
           p3,
           p4,
           p5,
           p6,
           p7,
           IsOK  
         )
rules ( IsOK[any] 
        = case when IsOK[cv()-1] is null  then 1        /* first Record of prodid (RANK=1)      */
               when IsOK[cv()-1] = 0      then 0        /* if data break                        */
               else ( case when datex_lastmonth[cv()-1] = datex[cv()+0]
                           then 1
                           else 0
                      end )
          end )



create table temp (prodid integer, datex integer, 
datex_lastmonth integer, p1 number, p2 number, p3 number, 
p4 number, p5 number, p6 number, p7 number);
insert into temp values(1,201001,200912,92.8,10.5,65.8,21.9,2.1,89,97.2);
insert into temp values(1,200912,200911,89.7,32.1,16.1,26.1,58.5,25.1,93.9);
insert into temp values(1,200911,200910,84,92.3,47,97.9,10.9,98.3,79);
insert into temp values(1,200910,200909,16.3,25.2,53,9,46,62.5,72.8);
insert into temp values(1,200909,200908,80.4,86.9,19.8,97.8,24.4,56.9,46.9);
insert into temp values(1,200906,200905,57.7,64.6,11.6,89.2,51.2,2.7,90.2);
insert into temp values(1,200905,200904,3.8,35.3,16.4,17.9,2.3,25.1,19.4);
insert into temp values(1,200904,200903,15.7,46.6,63.9,52.3,38.5,98.9,25.5);
insert into temp values(1,200903,200902,97.1,32.7,95.6,31.2,62.8,31.5,25.2);
insert into temp values(2,201001,200912,14.8,93.1,69.2,15,48.8,21.5,62.1);
insert into temp values(2,200912,200911,37.7,46.2,96,99.5,24.1,59.6,23.6);
insert into temp values(2,200910,200909,70.7,86.6,58.1,13.5,82.4,6.5,84.3);
insert into temp values(2,200909,200908,21.3,35.4,73.1,92.1,55.3,43,11.5);
insert into temp values(2,200908,200907,78.3,78.2,21.6,20.4,30.3,73.4,73.2);
insert into temp values(2,200907,200906,17.6,45,27.9,33.1,50.8,24.4,39.5);
insert into temp values(2,200906,200905,36.2,55.5,41.7,15.3,41,53.6,46.8);
insert into temp values(3,201001,200912,41.1,11.9,6.3,62.1,85,81.7,56.2);
insert into temp values(3,200912,200911,62.4,0.6,34,44.3,51.5,89.3,4.8);
insert into temp values(3,200911,200910,57.5,12.4,14.8,92.2,89.5,59.1,80.5);
insert into temp values(3,200910,200909,14,98.2,69.7,38.8,57.6,37.5,80.9);
insert into temp values(3,200909,200908,16.3,30.5,56,14.9,69.6,56.2,64.3);
insert into temp values(3,200908,200907,61,92.4,64.6,22.5,11.3,67,63);
insert into temp values(3,200907,200906,79.9,76.5,19,69.2,22.5,98.6,5.9);
insert into temp values(3,200906,200905,82.2,62.1,7.6,35.9,86.5,44.5,77.4);
insert into temp values(3,200905,200904,93.2,30.7,92.1,82.9,73.6,54.4,52);
insert into temp values(3,200903,200902,8.2,61,18,62.1,95.4,73.2,45.2);
insert into temp values(3,200902,200901,19.4,47.3,47.7,12.6,94.3,57.9,79.7);



Result:
create table temp_res (prodid integer, rank integer, datex integer, datex_lastmonth integer, p1 number, 
p2 number, p3 number, p4 number, p5 number, p6 number, 
p7 number, IsOK integer);
insert into temp_res values(1,1,201001,200912,92.8,10.5,65.8,21.9,2.1,89,97.2,1);
insert into temp_res values(1,2,200912,200911,89.7,32.1,16.1,26.1,58.5,25.1,93.9,1);
insert into temp_res values(1,3,200911,200910,84,92.3,47,97.9,10.9,98.3,79,1);
insert into temp_res values(1,4,200910,200909,16.3,25.2,53,9,46,62.5,72.8,1);
insert into temp_res values(1,5,200909,200908,80.4,86.9,19.8,97.8,24.4,56.9,46.9,1);
insert into temp_res values(1,6,200906,200905,57.7,64.6,11.6,89.2,51.2,2.7,90.2,0);
insert into temp_res values(1,7,200905,200904,3.8,35.3,16.4,17.9,2.3,25.1,19.4,0);
insert into temp_res values(1,8,200904,200903,15.7,46.6,63.9,52.3,38.5,98.9,25.5,0);
insert into temp_res values(1,9,200903,200902,97.1,32.7,95.6,31.2,62.8,31.5,25.2,0);
insert into temp_res values(2,1,201001,200912,14.8,93.1,69.2,15,48.8,21.5,62.1,1);
insert into temp_res values(2,2,200912,200911,37.7,46.2,96,99.5,24.1,59.6,23.6,1);
insert into temp_res values(2,3,200910,200909,70.7,86.6,58.1,13.5,82.4,6.5,84.3,0);
insert into temp_res values(2,4,200909,200908,21.3,35.4,73.1,92.1,55.3,43,11.5,0);
insert into temp_res values(2,5,200908,200907,78.3,78.2,21.6,20.4,30.3,73.4,73.2,0);
insert into temp_res values(2,6,200907,200906,17.6,45,27.9,33.1,50.8,24.4,39.5,0);
insert into temp_res values(2,7,200906,200905,36.2,55.5,41.7,15.3,41,53.6,46.8,0);
insert into temp_res values(3,1,201001,200912,41.1,11.9,6.3,62.1,85,81.7,56.2,1);
insert into temp_res values(3,2,200912,200911,62.4,0.6,34,44.3,51.5,89.3,4.8,1);
insert into temp_res values(3,3,200911,200910,57.5,12.4,14.8,92.2,89.5,59.1,80.5,1);
insert into temp_res values(3,4,200910,200909,14,98.2,69.7,38.8,57.6,37.5,80.9,1);
insert into temp_res values(3,5,200909,200908,16.3,30.5,56,14.9,69.6,56.2,64.3,1);
insert into temp_res values(3,6,200908,200907,61,92.4,64.6,22.5,11.3,67,63,1);
insert into temp_res values(3,7,200907,200906,79.9,76.5,19,69.2,22.5,98.6,5.9,1);
insert into temp_res values(3,8,200906,200905,82.2,62.1,7.6,35.9,86.5,44.5,77.4,1);
insert into temp_res values(3,9,200905,200904,93.2,30.7,92.1,82.9,73.6,54.4,52,1);
insert into temp_res values(3,10,200903,200902,8.2,61,18,62.1,95.4,73.2,45.2,0);
insert into temp_res values(3,11,200902,200901,19.4,47.3,47.7,12.6,94.3,57.9,79.7,0);

[Updated on: Wed, 10 February 2010 07:58] by Moderator

Report message to a moderator

Re: model-clause [message #443005 is a reply to message #442766] Fri, 12 February 2010 00:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
I'm not sure whether I have to set all parameters (P1-P7) as measures since I don't need them in the model (but I want them in the result table).


So why don't you try that?
Execute only select query to check it out.
One hint is, Oracle will display only PARTITION BY column, DIMENSION BY column and all MEASURES column.


And do you know the purpose of MODEL clause?
MODEL is used as row generator, to add or update rows in the result set. While in your case you want to add one column. So although you can get your required result using MODEL, there is another way which will be better.

for more details, go through this link.

regards,
Delna
Re: model-clause [message #443026 is a reply to message #443005] Fri, 12 February 2010 02:25 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
What kind of other solution do you mean? Your link is about model clause only.
Re: model-clause [message #443028 is a reply to message #443026] Fri, 12 February 2010 02:49 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can add column like
select col1, col2,
(case when col1=col2 then 1
      else 0
 end) col3
from tbl


regards,
Delna
Re: model-clause [message #443036 is a reply to message #443028] Fri, 12 February 2010 03:35 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Your solution will give wrong results, since I need to check whether a previous value of IsOK has been 0 already. Any idea how to solve this issue?
Re: model-clause [message #443041 is a reply to message #442766] Fri, 12 February 2010 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not sure I understand your requirements but the following gives the result you posted:
SQL> select prodid, datex, isok from temp_res order by prodid, datex desc;
PRODID  DATEX   ISOK
------ ------ ------
     1 201001      1
     1 200912      1
     1 200911      1
     1 200910      1
     1 200909      1
     1 200906      0
     1 200905      0
     1 200904      0
     1 200903      0
     2 201001      1
     2 200912      1
     2 200910      0
     2 200909      0
     2 200908      0
     2 200907      0
     2 200906      0
     3 201001      1
     3 200912      1
     3 200911      1
     3 200910      1
     3 200909      1
     3 200908      1
     3 200907      1
     3 200906      1
     3 200905      1
     3 200903      0
     3 200902      0

27 rows selected.

SQL> with 
  2    data as (
  3      select prodid, datex, 
  4             decode(nvl(to_date(lag(datex) over (partition by prodid order by datex desc),
  5                                'YYYYMM'),
  6                        add_months(to_date(datex,'YYYYMM'),1)),
  7                    add_months(to_date(datex,'YYYYMM'),1), 1,
  8                    0) flag                  
  9      from temp
 10    )
 11  select prodid, datex, 
 12         min(flag) over (partition by prodid order by datex desc) isok
 13  from data
 14  order by prodid, datex desc
 15  /
PRODID  DATEX   ISOK
------ ------ ------
     1 201001      1
     1 200912      1
     1 200911      1
     1 200910      1
     1 200909      1
     1 200906      0
     1 200905      0
     1 200904      0
     1 200903      0
     2 201001      1
     2 200912      1
     2 200910      0
     2 200909      0
     2 200908      0
     2 200907      0
     2 200906      0
     3 201001      1
     3 200912      1
     3 200911      1
     3 200910      1
     3 200909      1
     3 200908      1
     3 200907      1
     3 200906      1
     3 200905      1
     3 200903      0
     3 200902      0

27 rows selected.

Regards
Michel
Re: model-clause [message #443066 is a reply to message #443041] Fri, 12 February 2010 06:50 Go to previous message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Excellent, thank you very much for your help Michel.

Regards, Stefan
Previous Topic: Performance Issues
Next Topic: oracle tree calculation
Goto Forum:
  


Current Time: Sun Sep 25 18:03:41 CDT 2016

Total time taken to generate the page: 0.07902 seconds