0

I'm migrating my PLs from MySQL 5.7 to MySQL 8.

When I was starting with MySQL 5.7, I found a recipe to find gaps and repetitions in a number sequence.

In my table, it must not have gaps. It's right 1,2,3,4,5,6... but not 1,2,3,5,6... (4 is missing).

In my table, it must not have repetitions. It's wrong 1,2,3,3,4,5... (3 is repeated).

The query I found was like this, and it works perfectly for the target:

SET @a :=0;
SET @b :=1;
SELECT COUNT(DISTINCT LAG)
INTO GAPS
FROM (
        SELECT r.value, r.value - r2.value AS LAG
        FROM
        (SELECT if(@a, @a:=@a+1, @a:=1) as rownum, my_seq_number as value FROM my_table order by my_seq_number asc) AS r
        LEFT JOIN
        (SELECT if(@b, @b:=@b+1, @b:=1) as rownum, my_seq_number as value FROM my_table order by my_seq_number asc) AS r2
        ON r.rownum = r2.rownum
) T
WHERE T.LAG IS NOT NULL AND T.LAG <> 1 ;

@a, @b are user-defined variables, and it doesn't work fine (or work at all) in MySQL 8.

I'm wondering about making a count and check in Java, but it's slower than the query (I have thousands of rows).

I'm wondering about counting the number of rows (6), the min(1) and the max(6), and (max-min+1 = number of rows). But in the sequence 1,2,3,3,4,6, that formula is right but the sequence is wrong.

Do you have any other "recipe" or idea to check this in MySQL 8?

Any suggestion is welcome. Thank you.

yaki_nuka
  • 724
  • 4
  • 26
  • 44
  • In MySQL 8 you must use window function and CTE, not user-defined variables. Describe the task itself and porovide sample data + desired output for it according to [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055). – Akina Jul 13 '23 at 07:48

1 Answers1

1

Schematically:

WITH cte AS (
    SELECT column - LAG(column) OVER (ORDER BY column, unique_id) AS delta
    FROM table
)
SELECT SUM(delta > 1) AS gaps_amount,
       SUM(delta = 0) AS duplicates_amount
FROM cte;
Akina
  • 39,301
  • 5
  • 14
  • 25
  • That's right, @akina. I have accepted the answer. That schema helped me a lot. I didn't know the LAG function. That makes what I need. Thanks. – yaki_nuka Jul 26 '23 at 10:56