SQLite - Merge multiple rows

By xngo on March 5, 2019

Overview

Merging rows in a table will reduce the size of your database and consequently improve the search performance. For my case, I was able to reduce my biggest table from 1.7M rows to 650K rows. That is more than a half.

Here, I am showing the case of my Android app, iTransit. The application simply shows time schedules of buses.

iTransit - Schedule screen

Suppose that you have a table with the following times data:

stop_id     departure_time
-------- --------------
51661       06:00:00      
51661       06:23:00      
51661       07:07:00      
51661       07:45:00      
51661       08:00:00      
51661       08:36:00

You want to merge each row by the hour and leave out the number of seconds. Your end goal would be:

stop_id     hour        minutes   
-------- -------- ----------
51661       06          00 23     
51661       07          07 45     
51661       08          00 36

Merging rows

First, create the data with the following SQL statements:

DROP TABLE IF EXISTS stop_times;
CREATE TABLE stop_times(stop_id INTEGER, departure_time TEXT);
 
INSERT INTO stop_times(stop_id, departure_time) VALUES(51661, '06:00:00');
INSERT INTO stop_times(stop_id, departure_time) VALUES(51661, '06:23:00');
INSERT INTO stop_times(stop_id, departure_time) VALUES(51661, '07:07:00');
INSERT INTO stop_times(stop_id, departure_time) VALUES(51661, '07:45:00');
INSERT INTO stop_times(stop_id, departure_time) VALUES(51661, '08:00:00');
INSERT INTO stop_times(stop_id, departure_time) VALUES(51661, '08:36:00');

Secondly, breakdown departure_time column into hour and minute columns:

SELECT stop_id, departure_time, 
    substr(departure_time, 1, 2) AS HOUR, substr(departure_time, 4, 2) AS MINUTE
FROM stop_times;
stop_id     departure_time  hour        minute   
-------- ------------ -------- ----------
51661       06:00:00        06          00        
51661       06:54:00        06          54        
51661       07:00:00        07          00        
51661       07:54:00        07          54        
51661       08:00:00        08          00        
51661       08:56:00        08          56

Thirdly, merge the hour rows using GROUP BY and concatenate the minute using GROUP_CONCAT() function.

SELECT stop_id, substr(departure_time, 1, 2) AS HOUR, 
        GROUP_CONCAT(substr(departure_time, 4, 2),' ') AS MINUTE
FROM stop_times
GROUP BY stop_id, HOUR;
stop_id     hour        minute    
-------- -------- ----------
51095       06          00 23     
51095       07          07 45     
51095       08          00 36

Finally, save the results into a new table new_stop_times.

DROP TABLE IF EXISTS new_stop_times;
CREATE TABLE new_stop_times(stop_id INTEGER, HOUR TEXT, minutes TEXT);
 
INSERT INTO new_stop_times(stop_id, HOUR, minutes)
    SELECT stop_id, substr(departure_time, 1, 2) AS HOUR, 
            GROUP_CONCAT(substr(departure_time, 4, 2),' ') AS MINUTE
    FROM stop_times
    GROUP BY stop_id, HOUR;
SQLite

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.