How to increment an id based on a field having a certain value going row by row

Question

I'm importing data where groups of rows need to be given an id but there is nothing unique and common to them in the incoming data. What there is is a known indicator of the first row of a group and that the data is in order so we can step through row by row setting an id and then increment that id whenever this indicator is found. I've done this however it's incredibly slow, so is there a better way to do this in mysql or am i better off perhaps pre-processing the text data going line by line to add the id.

Example of data coming in, I need to increment an id whenever we see "NEW"

id,linetype,number,text
1,NEW,1234,sometext
2,CONTINUE,2412,anytext
3,CONTINUE,1,hello
4,NEW,2333,bla bla
5,CONTINUE,333,hello
6,NEW,1234,anything

So i'll end up with

id,linetype,number,text,group_id
1,NEW,1234,sometext,1
2,CONTINUE,2412,anytext,1
3,CONTINUE,1,hello,1
4,NEW,2333,bla bla,2
5,CONTINUE,333,hello,2
6,NEW,1234,anything,3

I've tried a stored procedure where i go row by row updating as i go, but it's super slow.

select count(*) from mytable into n;
set i=1;
while i<=n do
  select linetype into l_linetype from mytable where id = i;
  if l_linetype = "NEW" then
    set l_id = l_id + 1;
  end if;
  update mytable set group_id = l_id where id = i;
end while;

No errors, it's just something that i could go line by line reading and writing the text file and do in a second while in mysql it's taking 100 seconds, it'd be nice if there was a way within mysql to do this reasonably fast so separate pre-processing was not needed.

Answer 1

In absence of MySQL 8+ (non availability of Windowing functions), you can use a Correlated Subquery instead:

EDIT: As pointed out by @Paul in comments,

SELECT t1.*, 
       (SELECT COUNT(*) 
        FROM your_table t2
        WHERE t2.id <= t1.id 
          AND t2.linetype = 'NEW'
       ) group_id
FROM your_table t1

Above query can be more performant, if we define the following composite index (linetype, id). The order of columns is important, because we have a Range condition on id.

Previously:

SELECT t1.*, 
       (SELECT SUM(t2.linetype = 'NEW') 
        FROM your_table t2
        WHERE t2.id <= t1.id
       ) group_id
FROM your_table t1

Above query requires indexing on id.

---

Another approach using User-defined Variables (Session variables) would be:

SELECT 
  t1.*, 
  @g := IF(t1.linetype = 'NEW', @g + 1, @g) AS group_id
FROM your_table t1 
CROSS JOIN (SELECT @g := 0) vars 
ORDER BY t1.id

It is like a looping technique, where we use Session Variables whose previous value is accessible during next row's calculation during SELECT. So, we initialize the variable @g to 0, and then compute it row by row. If we can encounter a row with NEW linetype, we increment it, else use the previous row's value. You can also check https://stackoverflow.com/a/53465139/2469308 for more discussion and caveats to take care of while using this approach.

Answer 2

For MySql 8.0+ you can use SUM() window function:

select *,
  sum(linetype = 'NEW') over (order by id) group_id
from tablename  

See the demo.
For previous versions you can simulate this functionality with the use of a variable:

set @group_id := 0;
select *,
  @group_id := @group_id + (linetype = 'NEW') group_id
from tablename 
order by id

See the demo.

Results:

| id  | linetype | number | text     | group_id |
| --- | -------- | ------ | -------- | -------- |
| 1   | NEW      | 1234   | sometext | 1        |
| 2   | CONTINUE | 2412   | anytext  | 1        |
| 3   | CONTINUE | 1      | hello    | 1        |
| 4   | NEW      | 2333   | bla bla  | 2        |
| 5   | CONTINUE | 333    | hello    | 2        |
| 6   | NEW      | 1234   | anything | 3        |