#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Help with group labeling


    I have a table containing two columns:
    ID flag
    1 0
    2 0
    3 1
    4 1
    5 0
    6 1
    7 0
    8 0
    9 1
    10 1
    11 1
    .....

    I would like to label each group of 1's in ascending order by adding a new column "group_id" as follows:


    ID flag group_id
    1 0 0
    2 0 0
    3 1 1
    4 1 1
    5 0 0
    6 1 2
    7 0 0
    8 0 0
    9 1 3
    10 1 3
    11 1 3
    .....

    I tried window functions but none of them work directly. Any suggestions?
    Thanks in advance!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    You are trying to rely on the order of rows to define groups -- which is explicitly forbidden by relational systems. You need to reform your schema to account for what groups are, and input your data accordingly. There is no window function in existence that bases its output on the order of rows, because the order of a result is not considered a fact by itself and therefore can't be taken into account by a function.

    If you already have a bad schema but it has data in it already, you can write a procedural function that migrates your data to a better schema. The reason it can work (but is extremely fragile) is because it is not relational (declarative), it is procedural (step-by-step).

    Here is one way. Save this as a script and run it from psql:
    sql Code:
    CREATE TABLE stupid
      (id    INTEGER NOT NULL,
       flag  INTEGER NOT NULL);
     
    INSERT INTO stupid
       (id, flag)
      VALUES
       ( 1, 0),
       ( 2, 0),
       ( 3, 1),
       ( 4, 1),
       ( 5, 0),
       ( 6, 1),
       ( 7, 0),
       ( 8, 0),
       ( 9, 1),
       (10, 1),
       (11, 1);
     
    CREATE TABLE smart
      (id  SERIAL PRIMARY KEY);
     
    CREATE TABLE groups
      (id  SERIAL PRIMARY KEY);
     
    CREATE TABLE members
      (member  INTEGER REFERENCES smart ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
       gid     INTEGER REFERENCES groups ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
       PRIMARY KEY (member, gid));
     
    CREATE FUNCTION fix_stupid() RETURNS VOID AS
      $$
      DECLARE
        x            stupid%ROWTYPE;
        last_flag    INTEGER := 0;
        current_gid  INTEGER;
      BEGIN
        FOR x IN (SELECT id, flag FROM stupid ORDER BY id) LOOP
          EXECUTE $q$INSERT INTO smart VALUES ($1)$q$ USING x.id;
          IF x.flag > 0 THEN 
            IF last_flag < 1 THEN
              INSERT INTO groups VALUES (DEFAULT) RETURNING id INTO current_gid;
            END IF;
            EXECUTE $q$INSERT INTO members (member, gid) VALUES ($1, $2)$q$
              USING x.id, current_gid;
          END IF;
          last_flag := x.flag;
        END LOOP;
        SELECT MAX(id) INTO new_pk FROM smart;
        RAISE NOTICE 'Setting smart_id_seq to %', new_pk;
        PERFORM SETVAL('smart_id_seq', new_pk);
      END;
      $$ LANGUAGE plpgsql;
     
    SELECT fix_stupid();
     
    SELECT s.id, m.gid
      FROM smart AS s LEFT OUTER JOIN members AS m ON (s.id = m.member)
      ORDER BY id;
    From within psql the result is:
    Code:
    ceverett=# \i stupid.sql 
    CREATE TABLE
    INSERT 0 11
    psql:stupid.sql:21: NOTICE:  CREATE TABLE will create implicit sequence "smart_id_seq" for serial column "smart.id"
    psql:stupid.sql:21: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "smart_pkey" for table "smart"
    CREATE TABLE
    psql:stupid.sql:24: NOTICE:  CREATE TABLE will create implicit sequence "groups_id_seq" for serial column "groups.id"
    psql:stupid.sql:24: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "groups_pkey" for table "groups"
    CREATE TABLE
    psql:stupid.sql:29: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "members_pkey" for table "members"
    CREATE TABLE
    CREATE FUNCTION
    psql:stupid.sql:56: NOTICE:  Setting smart_id_seq to 11
     fix_stupid 
    ------------
     
    (1 row)
    
     id | gid 
    ----+-----
      1 |    
      2 |    
      3 |   1
      4 |   1
      5 |    
      6 |   2
      7 |    
      8 |    
      9 |   3
     10 |   3
     11 |   3
    (11 rows)
    
    ceverett=#
    Junk like that is always eventually required when you initially decide to create a braindead schema. Keep that in mind when working on real projects. Schema changes are a primary killer, because data is central to everything that you do in programming.

IMN logo majestic logo threadwatch logo seochat tools logo