Re: dividing to group with the same weight


<liorlew@xxxxxxxxx> a écrit dans le message de news: 1139477809.896487.98670@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|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


.

Relevant Pages

  • logging users against an update
    ... I don’t want to upsize to sql. ... I can then see how many records a user has updated Dailey? ... James ... Prev by Date: ...
    (microsoft.public.access.security)
  • Knowing the name and the type for each column in a table
    ... I developed this function in Visual Fox Pro but now I develop with C# ... and SQL Ce and It's not the same thing and I'm lost... ... Prev by Date: ...
    (microsoft.public.sqlserver.ce)
  • passing artibrary strings into a database
    ... lines or other such things that would foul the sql insert call and or ... be a security hazard? ... This feels like a newbee type question, ... Prev by Date: ...
    (comp.lang.python)
  • list navigation web part
    ... I would like to show in a Sharepoint Web Part some data from a SQL ... Server database table of other application. ... in the form of a list with navigation ... Prev by Date: ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: union all vs. left outer join
    ... > Warning long mail ... ... > For several days I have tried to figure out why two sql with an union all ... > is much faster than using a single sql with left outer join. ... Prev by Date: ...
    (comp.databases.theory)