I have a set of user records consisting of the start and end dates and the some groups that they belong to, as the following:

I am using MariaDB, and I need to iterate through all the records and compare each record to all the others to check if they belong to the same group or not (A, B, C) and if so, I merge these records into one record and take the first date in started and the last date in end as highlighted in bold.

The end result should look something like this:

I implemented a way where I create a temp table and store all the records I checked, but this takes a lot of time.

Here is my implemention:

CREATE PROCEDURE loopOver()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE id BIGINT UNSIGNED;

    DECLARE cur CURSOR FOR SELECT user_id
                          FROM table1 as f
                          WHERE NOT EXISTS( -- temp table that contain already check records
                                  SELECT 1
                                  FROM temp_table as t
                                  WHERE f.id = t.id);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    FETCH cur INTO id;  -- Get first row
    WHILE NOT done
        DO
           -- This where i create tmp table
            call creating_tmp_table(id); 
            FETCH cur INTO id;  -- Get next row
        END WHILE;
    CLOSE cur;
END //

Is there a faster way to do this operation?

Thanks in advance.

CodePudding user response：

You can select MIN(started), MAX(ends) grouped by A, B, C as the following:

SELECT MIN(started) AS started, MAX(ends) AS ends,
       A, B, C
FROM table_name
GROUP BY A, B, C
ORDER BY A, B, C

See a demo.

If you have null values in the 'ends' columun and you want to select them as '9999-12-31', you may use conditional aggregation as the following:

SELECT MIN(started) AS started,
       MAX(CASE WHEN ends IS NULL THEN '9999-12-31' ELSE ends END) AS ends,
       A, B, C
FROM table_name
GROUP BY A, B, C
ORDER BY A, B, C

See a demo.