Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

duplicate rank()

607061
607061 Member Posts: 5
edited September 2008 in SQL & PL/SQL
i output like this :

date code oil rank
Jan-07 a01 10 1
Jan-07 a03 5 2
Jan-07 a04 4 3
Jan-07 a05 23 4
Jan-07 a06 3 5
Feb-07 a01 25 1
Feb-07 a02 32 2
Feb-07 a03 42 3
Feb-07 a04 33 4
Feb-07 a05 52 5
Feb-07 a06 11 6

i want code a02 include altought in other month there is no code a02:

date code oil rank
Jan-07 a01 10 1
Jan-07 a02 0 2
Jan-07 a03 5 3
Jan-07 a04 4 4
Jan-07 a05 23 5
Jan-07 a06 3 6
Feb-07 a01 25 1
Feb-07 a02 32 2
Feb-07 a03 42 3
Feb-07 a04 33 4
Feb-07 a05 52 5
Feb-07 a06 11 6

and i would like high performance
thank you for your attention..

Comments

  • NicloeiW
    NicloeiW Member Posts: 1,811
    i dont understand ?
  • jeneesh
    jeneesh Member Posts: 7,168
  • SKP
    SKP Member Posts: 844 Gold Badge
    Can you please post a sample of the query what you are using to get the output
  • 607061
    607061 Member Posts: 5
    edited November 2007
    sorry,
    select date, code,net_vol_oil, rank()over(partition by date order by code)
    from my_table

    i have output like this

    date code net_vol_oil rank
    Jan-07 a01 10 1
    Jan-07 a03 5 2
    Jan-07 a04 4 3
    Jan-07 a05 23 4
    Jan-07 a06 3 5
    Feb-07 a01 25 1
    Feb-07 a02 32 2
    Feb-07 a03 42 3
    Feb-07 a04 33 4
    Feb-07 a05 52 5
    Feb-07 a06 11 6

    i use rank()over(partition by ...order by ...)
    system give rank 2 on jan 07,the code is a03..
    in february there is a02 and system give rank 2
    i want code a02 appear in january 07 and system give rank 2 to this code
    like this output:


    date code net_vol_oil rank
    Jan-07 a01 10 1
    Jan-07 a02 0 2
    Jan-07 a03 5 3
    Jan-07 a04 4 4
    Jan-07 a05 23 5
    Jan-07 a06 3 6
    Feb-07 a01 25 1
    Feb-07 a02 32 2
    Feb-07 a03 42 3
    Feb-07 a04 33 4
    Feb-07 a05 52 5
    Feb-07 a06 11 6

    thank you

    Message was edited by:
    user604058
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Assuming:

    - you are at least on 10g
    - the 'a01' record is present for every month

    Then you can use this:
    SQL> create table my_table
    2 as
    3 select date '2007-01-01' mydate, 'a01' code, 10 net_vol_oil from dual union all
    4 select date '2007-01-01', 'a03', 5 from dual union all
    5 select date '2007-01-01', 'a04', 4 from dual union all
    6 select date '2007-01-01', 'a05', 23 from dual union all
    7 select date '2007-01-01', 'a06', 3 from dual union all
    8 select date '2007-02-01', 'a01', 25 from dual union all
    9 select date '2007-02-01', 'a02', 32 from dual union all
    10 select date '2007-02-01', 'a03', 42 from dual union all
    11 select date '2007-02-01', 'a04', 33 from dual union all
    12 select date '2007-02-01', 'a05', 52 from dual union all
    13 select date '2007-02-01', 'a06', 11 from dual
    14 /

    Tabel is aangemaakt.

    SQL> select mydate
    2 , 'a' || to_char(i,'fm00') code
    3 , net_vol_oil
    4 , i rnk
    5 from my_table
    6 model
    7 partition by (mydate)
    8 dimension by (to_number(substr(code,2)) i)
    9 measures (net_vol_oil, to_number(substr(max(code) over (),2)) max_no, code)
    10 rules
    11 ( net_vol_oil[for i from 1 to max_no[1] increment 1] = nvl(net_vol_oil[cv()],0)
    12 )
    13 order by mydate
    14 , i
    15 /

    MYDATE CODE NET_VOL_OIL RNK
    ------------------- ---- ----------- ----------
    01-01-2007 00:00:00 a01 10 1
    01-01-2007 00:00:00 a02 0 2
    01-01-2007 00:00:00 a03 5 3
    01-01-2007 00:00:00 a04 4 4
    01-01-2007 00:00:00 a05 23 5
    01-01-2007 00:00:00 a06 3 6
    01-02-2007 00:00:00 a01 25 1
    01-02-2007 00:00:00 a02 32 2
    01-02-2007 00:00:00 a03 42 3
    01-02-2007 00:00:00 a04 33 4
    01-02-2007 00:00:00 a05 52 5
    01-02-2007 00:00:00 a06 11 6

    12 rijen zijn geselecteerd.
    Regards,
    Rob.
  • Md Iqbal Hassan
    Md Iqbal Hassan Member Posts: 22
    is there any real time requirement of ur problem????? i think some one has given u this query for confusing.......Rob van Wijk effort is excellent ,but if table have huge amount of date column value and some more CODES are missing then u will face the problem.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited September 2008
    I used "partitioned outer join".
    575147
    create table my_table(mydate,code,net_vol_oil) as
    select to_date('20070101','YYYYMMDD'),'a01',10 from dual union
    select to_date('20070101','YYYYMMDD'),'a03', 5 from dual union
    select to_date('20070101','YYYYMMDD'),'a04', 4 from dual union
    select to_date('20070101','YYYYMMDD'),'a05',23 from dual union
    select to_date('20070101','YYYYMMDD'),'a06', 3 from dual union
    select to_date('20070201','YYYYMMDD'),'a01',25 from dual union
    select to_date('20070201','YYYYMMDD'),'a02',32 from dual union
    select to_date('20070201','YYYYMMDD'),'a03',42 from dual union
    select to_date('20070201','YYYYMMDD'),'a04',33 from dual union
    select to_date('20070201','YYYYMMDD'),'a05',52 from dual union
    select to_date('20070201','YYYYMMDD'),'a06',11 from dual union
    select to_date('20070301','YYYYMMDD'),'a01',98 from dual union
    select to_date('20070301','YYYYMMDD'),'a03',76 from dual union
    select to_date('20070301','YYYYMMDD'),'a05',54 from dual union
    select to_date('20070401','YYYYMMDD'),'a02',32 from dual union
    select to_date('20070401','YYYYMMDD'),'a04',10 from dual;
    
    select b.mydate,a.code,nvl(b.net_vol_oil,0) as net_vol_oil,
    Row_Number() over(partition by b.mydate order by a.code) as RNK
      from (select distinct code from my_table) a
      Left Join my_table b
      partition by (b.mydate)
        on (a.code=b.code)
    order by b.mydate,a.code;
    
    MYDATE    COD  NET_VOL_OIL  RNK
    --------  ---  -----------  ---
    07-01-01  a01           10    1
    07-01-01  a02            0    2
    07-01-01  a03            5    3
    07-01-01  a04            4    4
    07-01-01  a05           23    5
    07-01-01  a06            3    6
    07-02-01  a01           25    1
    07-02-01  a02           32    2
    07-02-01  a03           42    3
    07-02-01  a04           33    4
    07-02-01  a05           52    5
    07-02-01  a06           11    6
    07-03-01  a01           98    1
    07-03-01  a02            0    2
    07-03-01  a03           76    3
    07-03-01  a04            0    4
    07-03-01  a05           54    5
    07-03-01  a06            0    6
    07-04-01  a01            0    1
    07-04-01  a02           32    2
    07-04-01  a03            0    3
    07-04-01  a04           10    4
    07-04-01  a05            0    5
    07-04-01  a06            0    6
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > I used "partitioned outer join".

    I would do so too, if there is a small "code" table present. Unfortunately there isn't, so the query has to access the same table twice. Maybe with proper indexes the effect won't be noticed, however the plan looks worse:
    SQL> set autotrace on explain
    SQL> select mydate
    2 , 'a' || to_char(i,'fm00') code
    3 , net_vol_oil
    4 , i rnk
    5 from my_table
    6 model
    7 partition by (mydate)
    8 dimension by (to_number(substr(code,2)) i)
    9 measures (net_vol_oil, to_number(substr(max(code) over (),2)) max_no, code)
    10 rules
    11 ( net_vol_oil[for i from 1 to max_no[1] increment 1] = nvl(net_vol_oil[cv()],
    12 )
    13 order by mydate
    14 , i
    15 /

    MYDATE CODE NET_VOL_OIL RNK
    ------------------- ---- ----------- ----------
    01-01-2007 00:00:00 a01 10 1
    01-01-2007 00:00:00 a02 0 2
    01-01-2007 00:00:00 a03 5 3
    01-01-2007 00:00:00 a04 4 4
    01-01-2007 00:00:00 a05 23 5
    01-01-2007 00:00:00 a06 3 6
    01-02-2007 00:00:00 a01 25 1
    01-02-2007 00:00:00 a02 32 2
    01-02-2007 00:00:00 a03 42 3
    01-02-2007 00:00:00 a04 33 4
    01-02-2007 00:00:00 a05 52 5
    01-02-2007 00:00:00 a06 11 6

    12 rijen zijn geselecteerd.


    Uitvoeringspan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=11 Bytes=297)
    1 0 SORT (ORDER BY) (Cost=5 Card=11 Bytes=297)
    2 1 SQL MODEL (ORDERED) (Cost=5 Card=11 Bytes=297)
    3 2 WINDOW (BUFFER) (Cost=5 Card=11 Bytes=297)
    4 3 TABLE ACCESS (FULL) OF 'MY_TABLE' (TABLE) (Cost=4 Card=11 Bytes=297)


    SQL> select b.mydate,a.code,nvl(b.net_vol_oil,0) as net_vol_oil,
    2 Row_Number() over(partition by b.mydate order by a.code) as RNK
    3 from (select distinct code from my_table) a
    4 Left Join my_table b
    5 partition by (b.mydate)
    6 on (a.code=b.code)
    7 order by b.mydate,a.code
    8 /

    MYDATE COD NET_VOL_OIL RNK
    ------------------- --- ----------- ----------
    01-01-2007 00:00:00 a01 10 1
    01-01-2007 00:00:00 a02 0 2
    01-01-2007 00:00:00 a03 5 3
    01-01-2007 00:00:00 a04 4 4
    01-01-2007 00:00:00 a05 23 5
    01-01-2007 00:00:00 a06 3 6
    01-02-2007 00:00:00 a01 25 1
    01-02-2007 00:00:00 a02 32 2
    01-02-2007 00:00:00 a03 42 3
    01-02-2007 00:00:00 a04 33 4
    01-02-2007 00:00:00 a05 52 5
    01-02-2007 00:00:00 a06 11 6

    12 rijen zijn geselecteerd.


    Uitvoeringspan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=55)
    1 0 WINDOW (SORT) (Cost=11 Card=1 Bytes=55)
    2 1 VIEW (Cost=11 Card=1 Bytes=55)
    3 2 MERGE JOIN (PARTITION OUTER) (Cost=11 Card=1 Bytes=32)
    4 3 SORT (JOIN) (Cost=6 Card=11 Bytes=55)
    5 4 VIEW (Cost=5 Card=11 Bytes=55)
    6 5 HASH (UNIQUE) (Cost=5 Card=11 Bytes=55)
    7 6 TABLE ACCESS (FULL) OF 'MY_TABLE' (TABLE) (Cost=4 Card=11 Bytes=55)
    8 3 SORT (PARTITION JOIN) (Cost=5 Card=11 Bytes=297)
    9 8 TABLE ACCESS (FULL) OF 'MY_TABLE' (TABLE) (Cost=4 Card=11 Bytes=297)
    Regards,
    Rob.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Oh
    Model is useful.
    Using Model method can decrease count of TableScan.

    thanks for your research.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Other model version which has const max value 6 :8}
    select mydate,code,net_vol_oil
      from my_table
     model
     partition by (mydate)
     dimension by (code)
     measures(net_vol_oil)
     rules(
     net_vol_oil[for code like 'a0%' from 1 to 6 INCREMENT 1] = nvl(net_vol_oil[cv()],0))
    order by mydate,code;
    I used like expression usage from here SQLFile.
    http://wiki.oracle.com/page/SQL+Model?t=anon
This discussion has been closed.