I want to number the group with a new number when the value of a column in SQL is 1.

If a column has a value of 1, I would like to number the group with a new number.
Do you know if it can be implemented using SQL?

For example, if there is data like A, I would like to create data like B.

Using window functions such as lag and first_value doesn't work, so
Then I think pl/sql, but I can't find a feasible implementation.

[Data A]

column 1 column 2
A1
A2
A3
A1
A2
A3

[Data B]

column 1column 2column 3
A11
A21
A31
A12
A22
A32

sql

2022-09-30 16:34

2 Answers

If the order is guaranteed, such as the primary key or the date and time of the update, it can be done by sub-questioning.

The SQL below is a sample code that is arranged in Oracle in the order of update date and time (updat column) and counts up the number every time the piyo column becomes 1.

SQL

select src.*,
      (select nvl(sum(1), 0)
       from hogetmp
       -- where tmp.rowid<=src.rowid -- forced numbering in ROWID
       where tmp.updat<=src.updat -- numbering in order of update date and time
       and tmp.piyo=1)group_value
from hoge src
order by update;

configuration

create table hoge(
  fugachar(1),
  piyo number (1),
  update date
);

insert into hoge values ('A', 1, sysdate);
insert into hoge values ('A', 2, sysdate+1);
insert into hoge values ('A', 3, sysdate+2);
insert into hoge values ('A', 1, sysdate+3);
insert into hoge values ('A', 2, sysdate+4);
insert into hoge values('A',3,sysdate+5);

SQL Fiddle

If you do not specify a row order, SQL does not guarantee the row order to retrieve.
Oracle and SQLite can use roid as unique keys issued by DB engines, and postgresql can use ctid. However, it is recommended that you create the primary key instead of relying on these unique keys because the values will change due to data migration.

2022-09-30 16:34

Thinking

If a column has a value of 1, then 1 and otherwise 0 is column X, and the sum of columns X from the first row to that row is the new number.

SQL Example

 with TMPTBL(NO,C1,C2,X) as(
    select
            NO, C1, C2, case C2 when 1 THEEN 1 else 0 end as X 
    from TARTGET_TABLE
)
select
    NO, C1, C2, sum(X) over (order by NOW between unbounded precedence and current row) as new number
from TMPTBL
order by NO;

The table in question is TARTGET_TABLE, and the column names are C1 and C2.Some columns are C2.
Added column NO, which is the key to sorting.
unbounded preceding is the first line
The current row is the current row.

Example Execution

with TARTGET_TABLE(NO,C1,C2)as(
    select 1, 'A', 1 union all
    select 2, 'A', 2 union all
    select 3, 'A', 3 union all
    select 4, 'A', 1 union all
    select 5, 'A', 2 union all
    select 6, 'A', 3 union all
    select 7, 'A', 4 union all
    select 8, 'A', 1
)
, TMPTBL(NO, C1, C2, X) as(
    select
            NO, C1, C2, case C2 when 1 THEEN 1 else 0 end as X 
    from TARTGET_TABLE
)
select
    NO, C1, C2, sum(X) over (order by NOW between unbounded precedence and current row) as new number
from TMPTBL
order by NO;

+----+----+----+-----------------+
| NO | C1 | C2 | New Number |
+----+----+----+-----------------+
|  1 | A | 1 | 1 |
|  2 | A | 2 | 1 |
|  3 | A | 3 | 1 |
|  4 | A | 1 | 2 |
|  5 | A | 2 | 2 |
|  6 | A | 3 | 2 |
|  7 | A | 4 | 2 |
|  8 | A | 1 | 3 |
+----+----+----+-----------------+

The DBMS I tried was 10.5.9 in MariaDB.

2022-09-30 16:34

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656