Eu tenho uma consulta que conta o número de reservas em uma tabela em vários intervalos de 15 minutos recuperados de outra tabela:
SELECT
t.*, count(r.id) as nof_reservations
FROM (
SELECT time_start, time_end FROM `interval`
WHERE time_start < '18:00:00'
AND time_end > '10:00:00'
) t -- holds a row per 15m interval
-- reservations of the assets
LEFT JOIN reservations r
ON start_utc < concat('2017-04-02 ', t.time_end)
AND end_utc > concat('2017-04-02 ', t.time_start)
-- we need to filter the assets on their type, which is
-- stored in the assets table
LEFT JOIN assets b
ON r.asset_id = b.id
WHERE r.deleted_at is null
AND b.asset_type_id = 6 -- asset type we need
GROUP BY t.time_start, t.time_end;
Isso produz a tabela desejada. Mas quando não há reservas ( nof_reservations=0) toda a linha desaparece:
| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 11:00:00 | 11:15:00 | 2 |
| 11:15:00 | 11:30:00 | 2 |
| 11:30:00 | 11:45:00 | 2 |
...
+------------+----------+------------------+
Como estou recuperando cada intervalo <1800 >1000, pensei em obter linhas como
| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 10:00:00 | 10:15:00 | 0 |
| 10:15:00 | 10:30:00 | 0 |
| 10:30:00 | 10:45:00 | 0 |
| 10:45:00 | 11:00:00 | 0 |
| 11:00:00 | 11:15:00 | 2 |
| 11:15:00 | 11:30:00 | 2 |
| 11:30:00 | 11:45:00 | 2 |
...
+------------+----------+------------------+
O que estou perdendo na consulta mencionada anteriormente?
Esquemas:
CREATE TABLE `reservations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` timestamp NULL DEFAULT NULL,
`asset_id` int(11) NOT NULL,
`start_utc` datetime NOT NULL,
`end_utc` datetime NOT NULL
PRIMARY KEY (`id`),
KEY `idx_van_utc` (`start_utc`),
KEY `id_tot_utc` (`end_utc`),
KEY `idx_boot_id` (`asset_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `assets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`asset_type_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `interval` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`time_start` time NOT NULL,
`time_end` time NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
