1 2 Previous Next 15 Replies Latest reply: Feb 27, 2014 2:05 PM by Tobias Arnhold RSS

    List of 1 and 0 to groups

    Tobias Arnhold

      Ho do I get a list like that

      "ID"    "GROUP"

      1    1

      2    1

      3    1

      4    1

      5    0

      9    1

      7    1

      8    0

      6    0

       

       

      To something like this:

      "ID"    "GROUP"   "GROUP_ID"

      1    1    1

      2    1    1

      3    1    1

      4    1    1

      5    0    2

      9    1    3

      7    1    3

      8    0    4

      6    0    4

       

      REM INSERTING into MY_TABLE
      SET DEFINE OFF;
      Insert into MY_TABLE ("ID",GROUP) values ('1','1');
      Insert into MY_TABLE ("ID",GROUP) values ('2','1');
      Insert into MY_TABLE ("ID",GROUP) values ('3','1');
      Insert into MY_TABLE ("ID",GROUP) values ('4','1');
      Insert into MY_TABLE ("ID",GROUP) values ('5','0');
      Insert into MY_TABLE ("ID",GROUP) values ('9','1');
      Insert into MY_TABLE ("ID",GROUP) values ('7','1');
      Insert into MY_TABLE ("ID",GROUP) values ('8','0');
      Insert into MY_TABLE ("ID",GROUP) values ('6','0');
      
      

       

      Thanks ahead

       

      Tobias

        • 2. Re: List of 1 and 0 to groups
          Biju Das

          ROW_NUMBER

           

          Regards

          Biju

          • 3. Re: List of 1 and 0 to groups
            Partha Sarathy S

            Try this. Since GROUP is a keyword, i have used GROUP_ID for the column and GRP_ID for the output column.

             

            SELECT ID,

                   GROUP_ID,

                   SUM(CASE WHEN GROUP_ID!=LG THEN 1 ELSE 0 END)OVER(ORDER BY RN)+1 GRP_ID

            FROM (

            SELECT ID,

                   GROUP_ID,

                   RN,

                   LAG(GROUP_ID)OVER(ORDER BY RN) LG

            FROM (

            SELECT ID,

                   GROUP_ID,

                   ROW_NUMBER()OVER(ORDER BY NULL) RN

            FROM MY_TABLE)

            );

             

            OUTPUT:

            1 1 1

            2 1 1

            3 1 1

            4 1 1

            5 0 2

            9 1 3

            7 1 3

            8 0 4

            6 0 4

             

            • 4. Re: List of 1 and 0 to groups
              Hoek

              Data is not stored in any particular order, yet we'll need something to order by.

              Is the below result also feasible?

               

              "ID""GROUP"   "GROUP_ID"
              111
              211
              311
              411
              502
              602
              713
              804
              915
              • 5. Re: List of 1 and 0 to groups
                Solomon Yakobson

                This has no solution. Rows in relational table have no order unless ORDER BY or hierarchical ordering applied. There no way to say row id=5, group=0 is followed by row id=9, group=1.

                 

                SY.

                • 6. Re: List of 1 and 0 to groups
                  Tobias Arnhold

                  Hi Hoek,

                   

                  I'm working with the result of a select statement.

                  "Is the below result also feasible?" > Yes. Maybe looks strange but it is correct.

                   

                  Thanks so far.

                  • 7. Re: List of 1 and 0 to groups
                    Solomon Yakobson

                    ORDER BY NULL is non-deterministic. You got correct results just because rows were inserted in suitable sequence and optimizer is using FULL SCAN. Assume tomorrow PK on id will be added (or id will be simply modified as not null) and index on id,group_id will be created. Now look what your query will return:

                     

                    SQL> alter table my_table modify id not null;

                    Table altered.

                    SQL> create index my_table_idx1
                      2  on my_table(id,group_id)
                      3  /

                    Index created.

                    SQL> SELECT ID,
                      2         GROUP_ID,
                      3         SUM(CASE WHEN GROUP_ID!=LG THEN 1 ELSE 0 END)OVER(ORDER BY RN)+1 GRP_ID
                      4  FROM (
                      5  SELECT ID,
                      6         GROUP_ID,
                      7         RN,
                      8         LAG(GROUP_ID)OVER(ORDER BY RN) LG
                      9  FROM (
                    10  SELECT ID,
                    11         GROUP_ID,
                    12         ROW_NUMBER()OVER(ORDER BY NULL) RN
                    13  FROM MY_TABLE)
                    14  );

                            ID   GROUP_ID     GRP_ID
                    ---------- ---------- ----------
                             1          1          1
                             2          1          1
                             3          1          1
                             4          1          1
                             5          0          2
                             6          0          2
                             7          1          3
                             8          0          4
                             9          1          5

                    9 rows selected.

                    SQL>

                     

                    SY.

                    • 8. Re: List of 1 and 0 to groups
                      Hoek

                      SY already gave the solution then

                      • 9. Re: List of 1 and 0 to groups
                        Solomon Yakobson

                        Not that I care, but you marked incorrect answer as correct. Read my latest reply to Expert

                         

                        • 10. Re: List of 1 and 0 to groups
                          Frank Kulash

                          Hi, Tobias,

                           

                          So, you want a series of consecutive rows to get the same group_id.  Words like "first", "next" and "consecutive" only make sense when the rows are ordered, and, as Hoek and others have pointed out, there is no built-in ordering in a relational table.

                          Assuming you have something in the table that determines the order, like the seq_num olumn below:

                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('1', '1', 10);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('2', '1', 11);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('3', '1', 12);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('4', '1', 14);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('5', '0', 15);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('9', '1', 16);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('7', '1', 20);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('8', '0', 25);
                          Insert into MY_TABLE (ID, GRP, SEQ_NUM) values ('6', '0', 26);

                          then here's one way to do what you want:


                          WITH     got_skip_id    AS
                          (
                              SELECT  id, grp     -- GROUP is not a very good column name
                              ,       seq_num
                              ,       ROW_NUMBER () OVER ( ORDER BY      seq_num)
                                    - ROW_NUMBER () OVER ( PARTITION BY  grp
                                                           ORDER BY      seq_num
                                                         )   AS skip_id
                              FROM    my_table
                          )
                          SELECT    id, grp
                          ,         DENSE_RANK () OVER ( ORDER BY (
                                                                      SELECT  MIN (seq_num)
                                                                      FROM    got_skip_id
                                                                      WHERE   skip_id  = m.skip_id
                                                                      AND     grp      = m.grp
                                                                  )
                                                       )  AS grp_id  -- to be consistent with grp
                          FROM      got_skip_id  m
                          ORDER BY  seq_num
                          ;

                          This is an example of a "fixed difference" problem; rows with the same group_id will have the same value of a difference (that is, a subtraction result).

                          • 11. Re: List of 1 and 0 to groups
                            Marwim

                            Ok, my first (link only) answer missed the fact that there is no ordering in the data.

                            Based on a sequence column my solution would be

                             

                            DROP TABLE my_table;

                            CREATE TABLE my_table (id NUMBER,id_group NUMBER,seq NUMBER);

                            INSERT INTO my_table (id,id_group,seq) VALUES (1,1,10);

                            INSERT INTO my_table (id,id_group,seq) VALUES (2,1,20);

                            INSERT INTO my_table (id,id_group,seq) VALUES (3,1,30);

                            INSERT INTO my_table (id,id_group,seq) VALUES (4,1,40);

                            INSERT INTO my_table (id,id_group,seq) VALUES (5,0,50);

                            INSERT INTO my_table (id,id_group,seq) VALUES (9,1,60);

                            INSERT INTO my_table (id,id_group,seq) VALUES (7,1,70);

                            INSERT INTO my_table (id,id_group,seq) VALUES (8,0,80);

                            INSERT INTO my_table (id,id_group,seq) VALUES (6,0,90);

                            COMMIT;

                             

                            WITH case_row_number AS (

                                SELECT  id

                                       ,id_group

                                       ,seq

                                       ,CASE

                                          WHEN id_group - LAG(id_group,1,0) OVER (ORDER BY seq) <> 0 THEN

                                            ROW_NUMBER() OVER (ORDER BY seq)

                                          END rn

                                FROM    my_table

                                )

                            ,grouped AS(

                                SELECT  id

                                       ,id_group

                                       ,seq

                                       ,MAX(rn) OVER (ORDER BY seq) grp

                                FROM    case_row_number

                                )

                            SELECT  id

                                   ,id_group

                                   ,seq

                                   ,DENSE_RANK() OVER (ORDER BY grp) grp

                            FROM    grouped

                            ORDER BY seq;

                             

                            It's called max-on-case-row-number technique by Rob van Wijk (my second link)

                             

                            Marcus

                             

                            Message was edited by: Marwim

                            • 12. Re: List of 1 and 0 to groups
                              Solomon Yakobson

                              Or, using recursive subquery factoring:

                               

                              with r(id,id_group,seq,grp_id) as (

                                                                  select  id,

                                                                          id_group,

                                                                          seq,

                                                                          1 grp_id

                                                                    from  my_table

                                                                    where seq = 10

                                                                 union all

                                                                  select  m.id,

                                                                          m.id_group,

                                                                          m.seq,

                                                                          case m.id_group

                                                                            when r.id_group then r.grp_id

                                                                            else r.grp_id + 1

                                                                          end grp_id

                                                                    from  r,

                                                                          my_table m

                                                                    where m.seq = r.seq + 10

                                                                )

                              select  *

                                from  r

                                order by seq

                              /


                                      ID   ID_GROUP        SEQ     GRP_ID
                              ---------- ---------- ---------- ----------
                                       1          1         10          1
                                       2          1         20          1
                                       3          1         30          1
                                       4          1         40          1
                                       5          0         50          2
                                       9          1         60          3
                                       7          1         70          3
                                       8          0         80          4
                                       6          0         90          4

                              9 rows selected.

                              SQL>

                               

                              SY.

                              • 13. Re: List of 1 and 0 to groups
                                Hoek

                                Alternatively, you could kick in the MODEL clause:

                                 

                                SQL> select id

                                  2  ,      grp

                                  3  ,      grp_id

                                  4  from   my_table

                                  5  model

                                  6  dimension by (row_number() over (order by id) id)

                                  7  measures ( grp

                                  8           , 0 grp_id

                                  9           )

                                10  rules ( grp_id[id=1] = grp_id[cv()]+1

                                11        , grp_id[id>1] = case

                                12                           when grp[cv()] = grp[cv()-1] then grp_id[cv()-1]

                                13                           else grp_id[cv()-1]+1

                                14                          end

                                15         );

                                 

                                        ID G     GRP_ID

                                ---------- - ----------

                                         1 1          1

                                         2 1          1

                                         3 1          1

                                         4 1          1

                                         5 0          2

                                         6 0          2

                                         7 1          3

                                         8 0          4

                                         9 1          5

                                 

                                9 rows selected.

                                • 14. Re: List of 1 and 0 to groups
                                  user10857924

                                  or may be like this...

                                   

                                  {code}

                                   

                                  with my_table as
                                  (
                                  select '1' ID, '1' GRP, 10 SEQ_NUM from dual union all
                                  select '2', '1', 11 from dual union all
                                  select '3', '1', 12 from dual union all
                                  select '4', '1', 14 from dual union all
                                  select '5', '0', 15 from dual union all
                                  select '9', '1', 16 from dual union all
                                  select '7', '1', 20 from dual union all
                                  select '8', '0', 25 from dual union all
                                  select '6', '0', 26 from dual

                                  )
                                  select ID, GRP, SEQ_NUM, sum(flg)over(order by SEQ_NUM) as new_grp
                                  from
                                  (
                                  select t.*, case when grp!=lag(grp,1,'#')over(order by SEQ_NUM) then 1 ELSE 0 END as flg
                                  from my_table t
                                  )x

                                   

                                  {code}

                                  1 2 Previous Next