Forum Stats

  • 3,825,220 Users
  • 2,260,482 Discussions
  • 7,896,450 Comments

Discussions

Assign group value based on lag value change

325647
325647 Member Posts: 311
edited Feb 20, 2010 5:21PM in SQL & PL/SQL
I have some old data that has a structure determined by order. The groups are in ascending order, based that the amount column, and delineated by when the previous value is less than the current value. I just need to assign a unique group ID to each group. This is what I have:
Order	Amount	Group
1 56
2 63
3 41
4 52
5 59
6 74
7 39
8 42
9 44
10 51
11 62
12 77
and this is what I would like:
Order	Amount	Group
1 56 1
2 63 1
3 41 2
4 52 2
5 59 2
6 74 2
7 39 3
8 42 3
9 44 3
10 51 3
11 62 3
12 77 3
I can get the previous value with the lag function but am struggling with assigning the number. I am a sql more than pl/sql person. TIA.

Answers

  • 576633
    576633 Member Posts: 235
    Something very similiar was up a few days ago. See this thread:

    1033531
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    with t as (
               select 1 ord,56 amount from dual union all
               select 2,63 from dual union all
               select 3,41 from dual union all
               select 4,52 from dual union all
               select 5,59 from dual union all
               select 6,74 from dual union all
               select 7,39 from dual union all
               select 8,42 from dual union all
               select 9,44 from dual union all
               select 10,51 from dual union all
               select 11,62 from dual union all
               select 12,77 from dual
              )
    select  ord,
            amount,
            sum(start_of_group) over(order by ord) grp
      from  (
             select  ord,
                     amount,
                     case
                       when lag(amount,1,amount + 1) over(order by ord) > amount then 1
                     end start_of_group
               from  t
            )
      order by ord
    /
    
           ORD     AMOUNT        GRP
    ---------- ---------- ----------
             1         56          1
             2         63          1
             3         41          2
             4         52          2
             5         59          2
             6         74          2
             7         39          3
             8         42          3
             9         44          3
            10         51          3
            11         62          3
    
           ORD     AMOUNT        GRP
    ---------- ---------- ----------
            12         77          3
    
    12 rows selected.
    
    SQL> 
    SY.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    SQL> with t as (
     select 1 o, 56 amount from dual union all
     select 2, 63 from dual union all
     select 3, 41 from dual union all
     select 4, 52 from dual union all
     select 5, 59 from dual union all
     select 6, 74 from dual union all
     select 7, 39 from dual union all
     select 8, 42 from dual union all
     select 9, 44 from dual union all
     select 10, 51 from dual union all
     select 11, 62 from dual union all
     select 12, 77 from dual
    )
    ---
    ---
    select o "ORDER", amount, sum (grp) over (order by o) + 1 "GROUP"
      from (select t.*,
                   case
                      when lag (amount) over (order by o) > amount then 1
                      else 0
                   end grp
              from t) t
    order by o
    /
         ORDER     AMOUNT      GROUP
    ---------- ---------- ----------
             1         56          1
             2         63          1
             3         41          2
             4         52          2
             5         59          2
             6         74          2
             7         39          3
             8         42          3
             9         44          3
            10         51          3
            11         62          3
            12         77          3
    
    12 rows selected.
  • 325647
    325647 Member Posts: 311
    Thanks. All of these solutions solve the problem.
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    I stumbled upon this question and played a bit with it and I'm wondering what point I'm missing.
    On first thought I'd wanted to update T using the UPDATE A JOIN technique (as a small 'weekend-exercise' ;-) ), but I keep on hitting ORA-01779 although I added a PK constraint and all columns of T are updatable/insertable/deletable. I tried several ways, below is the last effort, but all to no luck.
    But using the same query with MERGE works without any problem.
    What point am I missing? Perhaps it's very obvious, but as to now I don't really get it.
    Could it be the analytics?

    See this example:
    SQL> drop table t;
    
    Table dropped.
    
    SQL> create table t as 
      2  select 1 col_order, 56 amount from dual union all
      3  select 2, 63 from dual union all
      4  select 3, 41 from dual union all
      5  select 4, 52 from dual union all
      6  select 5, 59 from dual union all
      7  select 6, 74 from dual union all
      8  select 7, 39 from dual union all
      9  select 8, 42 from dual union all
     10  select 9, 44 from dual union all
     11  select 10, 51 from dual union all
     12  select 11, 62 from dual union all
     13  select 12, 77 from dual;
    
    Table created.
    
    SQL> alter table t add constraint t_pk primary key (col_order);
    
    Table altered.
    
    SQL> alter table t add (grp number);
    
    Table altered.
    
    SQL> select t.* 
      2  from   all_updatable_columns t
      3  where  t.table_name = 'T';
    
    OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
    ------------------------------ ------------------------------ ------------------------------ --- --- ---
    HOEK                           T                              COL_ORDER                      YES YES YES
    HOEK                           T                              AMOUNT                         YES YES YES
    HOEK                           T                              GRP                            YES YES YES
    
    3 rows selected.
    
    SQL> select * from t;
    
     COL_ORDER     AMOUNT        GRP
    ---------- ---------- ----------
             1         56
             2         63
             3         41
             4         52
             5         59
             6         74
             7         39
             8         42
             9         44
            10         51
            11         62
            12         77
    
    12 rows selected.
    
    SQL> update(
      2         select t1.grp
      3         ,      t2.new_grp
      4         from   t t1
      5         ,    ( select col_order
      6                ,      sum(ranges) over (order by col_order) new_grp
      7                from ( select col_order
      8                       ,      case 
      9                                when lag(amount) over (order by col_order) < amount
     10                                then 0
     11                                else 1
     12                              end  ranges
     13                       from   t 
     14                     )
     15              ) t2
     16         where  t1.col_order = t2.col_order       
     17        )
     18  set grp = new_grp;
    set grp = new_grp
        *
    ERROR at line 18:
    ORA-01779: cannot modify a column which maps to a non key-preserved table
    
    
    SQL> select * from t;
    
     COL_ORDER     AMOUNT        GRP
    ---------- ---------- ----------
             1         56
             2         63
             3         41
             4         52
             5         59
             6         74
             7         39
             8         42
             9         44
            10         51
            11         62
            12         77
    
    12 rows selected.
    
    SQL> merge into t
      2  using ( select col_order
      3          ,      sum(ranges) over (order by col_order) new_grp
      4          from ( select col_order
      5                 ,      case 
      6                          when lag(amount) over (order by col_order) < amount
      7                          then 0
      8                          else 1
      9                        end  ranges
     10                 from   t 
     11               )
     12        ) t2
     13  on (t.col_order = t2.col_order)
     14  when matched then update set grp = new_grp; 
    
    12 rows merged.
    
    SQL> select * from t; 
    
     COL_ORDER     AMOUNT        GRP
    ---------- ---------- ----------
             1         56          1
             2         63          1
             3         41          2
             4         52          2
             5         59          2
             6         74          2
             7         39          3
             8         42          3
             9         44          3
            10         51          3
            11         62          3
            12         77          3
    
    12 rows selected.
    
    SQL> 
  • ttt
    ttt Member Posts: 394 Bronze Badge
    edited Feb 20, 2010 5:21PM
    Hi !

    Also from curiosity :) i have done some testing too

    I have test this stuff with
    create or replace view VV as
      select <anything - also analytic functions> from (SUBQUERY) ..
    SUBQUERY is select statement from table with PK and .. everything is just OK with table, and for a view VV is table key preserved

    then
    If somewhere in SUBQUERY is an analytic function then view VV has no updatable columns from table in subquery..
    if there is no analytic functions in SUBQUERY then there are updatable columns in VV


    I don't think that i have seen something like this in documentation .. Interesting realy . But in Merge statement this is OK .. Hm ??

    Have a nice Sunday ..
This discussion has been closed.