Re: dividing to group with the same weight

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 10 Feb 2006 17:33:47 +0100
Message-ID: <43ecc06c$0$17046$626a54ce@news.free.fr>

<liorlew_at_gmail.com> a écrit dans le message de news: 1139477809.896487.98670_at_g14g2000cwa.googlegroups.com...
|I did the following:
| select name, salary, dept , mod(row_number() over(order by salary
| ),3) group_no from info_table
| order by salary desc;
|
| the answer I got is not exactly what I need, because I do not need the
| salaries (weight) to be put each time in the following group. I need
| them in the group with the lowest weight.
|
| I need that if I have the following
| 20,20,13,5,5,4,4,4,3,2,1,1,1,1,1,1,1
| the answer for 3 group will be
| 20, 20,13
| 5
| 5
| 4, 4, 4
| 3, 2,
| 1, 1
| 1 1, 1
| 1
| ----------------------------
| 29 , 28, 29 <-- total
| The query I wrote would return the following instead:
| 20 ,20 ,13
| 5 ,5 ,4
| 4 ,4 ,3
| 2 ,1 ,1
| 1 ,1 ,1
| 1
| ----------------
| 33, 31, 22 <-- wrong total
|
| of course the is always the possibility of opening a cursor and writing
| a C++ or pl/sql program, but I will be more satisfy if I could get
| oracle 10g to do it.
|
| I am sorry if you got the impression that I want you do do my work for
| me. trust me I am working on it too and will supply an answer if I
| found one.
|

Yes you can do it in 10g:

SQL> set head on
SQL> set head off
SQL> col nop noprint
SQL> col r   format a04
SQL> col g   format a100
SQL> def X=3
SQL> col cnt new_value cnt
SQL> select count(*) cnt from t;
       CNT
----------
        17

1 row selected.

SQL> with

  2    c as ( select count(*) cnt from t ),
  3    g as ( select rownum g from dual connect by level <= &X  ),
  4    s as (
  5      select r, g
  6      from ( select rownum-1 r from dual, c connect by level <= c.cnt-&X+1 ),
  7           g
  8      ),
  9    m as (
 10     select r, g, v
 11     from s
 12     model
 13       return all rows
 14       reference val on
 15         ( select val, row_number () over (order by val desc) rn
 16           from t order by val desc )
 17         dimension by (rn n)
 18         measures (val)
 19       dimension by (r, g)
 20       measures (cast(null as number) v, cast(null as number) s,
 21                 cast(null as number) m, cast(null as number) f)
 22       rules upsert all -- sequential order
 23       iterate (&cnt)
 24       ( -- calculate sum
 25         s[iteration_number,ANY]
 26           = nvl(sum(v)[r between 1 and iteration_number-1, cv(g)],0),
 27         -- calculate min sum
 28         m[iteration_number,1] = min(s)[cv(r),ANY],
 29         -- determine which group to assign -> set flag
 30         f[iteration_number,ANY] =
 31           case when s[cv(r),cv(g)] = m[cv(r),1]
 32                then 1 - nvl(sum(f)[cv(r),g between 1 and cv(g)-1],0)
 33                else 0
 34                end,
 35         -- assign value
 36         v[iteration_number,ANY] =
 37           case when f[cv(r),cv(g)] = 1 then val.val[iteration_number] end
 38       )

 39 )
 40 select 1 nop, 'Step' r,
 41 translate(max(substr(sys_connect_by_path(g,' '),2)),'#',' ') g  42 from ( select lpad('Grp_'||g,6,'#') g,

 43                row_number () over (order by g) curr,
 44                row_number () over (order by g) - 1 prev
 45         from g )

 46 connect by prior curr = prev
 47 start with curr = 1
 48 union all
 49 select 2 nop, '----' r,
 50 max(substr(sys_connect_by_path(rpad('-',length(g),'-'),' '),2)) g  51 from ( select lpad('Grp '||g,6,'#') g,

 52                row_number () over (order by g) curr,
 53                row_number () over (order by g) - 1 prev
 54         from g )

 55 connect by prior curr = prev
 56 start with curr = 1
 57 union all
 58 select 3 nop, to_char(r,'999') r,
 59 translate(max(substr(sys_connect_by_path(v,' '),2)),'#',' ') g  60 from ( select r, lpad('#'||v,6,'#') v,

 61                row_number () over (partition by r order by g) curr,
 62                row_number () over (partition by r order by g) - 1 prev
 63         from m
 64         where r > 0 )

 65 connect by prior curr = prev and prior r = r  66 start with curr = 1
 67 group by r
 68 union all
 69 select 4 nop, '----' r,
 70 max(substr(sys_connect_by_path(rpad('-',length(g),'-'),' '),2)) g  71 from ( select lpad('Grp '||g,6,'#') g,

 72                row_number () over (order by g) curr,
 73                row_number () over (order by g) - 1 prev
 74         from g )

 75 connect by prior curr = prev
 76 start with curr = 1
 77 union all
 78 select 5 nop, 'Tot.' r,
 79 translate(max(substr(sys_connect_by_path(v,' '),2)),'#',' ') g  80 from ( select lpad('#'||sum(v),6,'#') v,

 81                row_number () over (order by g) curr,
 82                row_number () over (order by g) - 1 prev
 83         from m
 84         where r > 0
 85         group by g )

 86 connect by prior curr = prev
 87 start with curr = 1
 88 order by 1, r
 89 /
Step Grp_1 Grp_2 Grp_3
---- ------ ------ ------

   1     20
   2            20
   3                   13
   4                    5
   5                    5
   6      4
   7             4
   8                    4
   9      3
  10             2
  11             1
  12      1
  13             1
  14                    1
  15      1
  16             1
---- ------ ------ ------
Tot.     29     29     28

20 rows selected.

Regards
Michel Cadot Received on Fri Feb 10 2006 - 10:33:47 CST