Reorganize and Aggregate Data by Count and Timerange

Overview

In this article, we will explore how to reorganize and aggregate data by time range using SQL. We will use a MySQL database with a table containing job information, including start and end times for each job. The goal is to create a new table that shows the count of active jobs within specific time ranges.

SQL Fiddle Demo

To demonstrate this concept, we will use an SQL Fiddle demo. Please note that you should use date/timestamp column instead of just strings, as it may behave incorrectly if proper format is not used.

Required Query

SELECT t3.range_start, t3.range_end, COUNT(*) AS count_of_act_job
FROM your_table t1
CROSS JOIN (
    SELECT time_start AS range_start, time_end AS range_end FROM (
        SELECT t2.*, @next AS time_end, @next := time_start
        FROM (SELECT time_start FROM your_table UNION SELECT time_end AS time_start FROM your_table) t2,
             (SELECT @next := NULL) var_init
        ORDER BY time_start DESC
    ) sq
    WHERE time_start <> time_end
) t3
WHERE  t3.range_start >= t1.time_start AND t3.range_end <= t1.time_end
GROUP BY t3.range_start, t3.range_end
ORDER BY range_start;

Output:

+-------------+-----------+------------------+
| range_start | range_end | count_of_act_job |
+-------------+-----------+------------------+
| 00:00       | 02:00     |                1 |
| 02:00       | 04:00     |                2 |
| 04:00       | 05:00     |                1 |
| 06:00       | 07:00     |                1 |
+-------------+-----------+------------------+

Explanation

The SQL query works as follows:

Step 1: SELECT time_start FROM your_table UNION SELECT time_end AS time_start FROM your_table;

This step creates a list of all possible start and end times for each job.

Output:

+------------+
| time_start |
+------------+
| 00:00      |
| 02:00      |
| 06:00      |
| 04:00      |
| 05:00      |
| 07:00      |
+------------+

Step 2: SELECT time_start AS range_start, time_end AS range_end FROM (...) WHERE time_start <> time_end;

This step creates a list of all possible combinations of start and end times for each job.

Output:

+-------------+-----------+
| range_start | range_end |
+-------------+-----------+
| 06:00       | 07:00     |
| 05:00       | 06:00     |
| 04:00       | 05:00     |
| 02:00       | 04:00     |
| 00:00       | 02:00     |
+-------------+-----------+

Step 3: SELECT t3.range_start, t3.range_end FROM your_table t1 CROSS JOIN (...) WHERE range_start >= time_start AND range_end <= time_end;

This step creates a new table that contains all possible combinations of start and end times for each job, filtered by the original table’s time range.

Output:

+-------------+-----------+
| range_start | range_end |
+-------------+-----------+
| 06:00       | 07:00     |
| 04:00       | 05:00     |
| 02:00       | 04:00     |
| 00:00       | 02:00     |
+-------------+-----------+

Step 4: GROUP BY range_start, range_end AND COUNT(*) AS count_of_act_job;

This step groups the resulting table by each combination of start and end times and counts the number of active jobs within each time range.

Output:

+-------------+-----------+------------------+
| range_start | range_end | count_of_act_job |
+-------------+-----------+------------------+
| 00:00       | 02:00     |                1 |
| 02:00       | 04:00     |                2 |
| 04:00       | 05:00     |                1 |
| 06:00       | 07:00     |                1 |
+-------------+-----------+------------------+

Conclusion

In this article, we demonstrated how to reorganize and aggregate data by time range using SQL. By breaking down the problem into smaller steps and using a combination of CROSS JOIN, WHERE, and GROUP BY clauses, we were able to create a new table that shows the count of active jobs within specific time ranges.

Last modified on 2024-07-09