mysqlperlgaps-and-islands

Perl: makes array of item IDs shortened

I have a large set of numbers, basically 1001 .. 150000 for a database using MySQL

There are a ton of gaps in the IDs on the database, so not all IDs exist. It can go from 100000 - 10500, then the next number will be 10675, and so forth.

I want to make the IDs shortened, such as 1001..3000, x, x, x, 55000..101000, etc.

I'm sure it's simple.

SELECT id FROM table_name WHERE data = x

give me above info.

I used

select group_concat(id) from items where id>1000

to get all ids in a comma-separated list. How do I shrink this to be more clean? Basically to add ".." to a series of sequential numbers.

I am using Perl, but I'm just not sure of the syntax to make it work.

Solution

  • here is a short query for you

    SELECT GROUP_CONCAT(
  CONCAT( IF(l=h, l, CONCAT(l,"..",h)))
) GAP
FROM ( 
  SELECT MIN(t1.id) l,MAX(t1.id) h, MAX(IF(t2.id IS NULL,@grp:=@grp+1,@grp)) AS grp
  FROM id_table t1
  LEFT JOIN id_table t2 ON t2.id = t1.id +1
  CROSS JOIN ( SELECT @grp := 0 ) AS INIT
  GROUP BY @grp
) as r;

    result

    100..103,110,120..121,200..203,400,500

    sample

    dbfiddle