Início / dba / Perguntas / 161580
Accepted
stUrb
Asked: 2017-01-20 02:05:51 +0800 CST

Incluindo linhas da tabela base com junções à esquerda quando essas junções estão 'vazias'

  • 772

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;
mysql

3 respostas

  1. Best Answer

    Como regra geral -
    ao ter ... LEFT JOIN t ON ..., as condições sobre tdevem estar na ONcláusula e não na WHEREcláusula.

    SELECT 
   t.*, count(b.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)
AND  r.deleted_at is null

-- 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
AND b.asset_type_id = 6 -- asset type we need

GROUP BY t.time_start, t.time_end;

    É possível que você queira uma junção um pouco diferente (o restante da consulta SELECT ... FROM ... te GROUP BYpermanece o mesmo que acima):

    -- reservations of the assets
-- and filter the assets on their type
LEFT JOIN reservations r  
    JOIN assets b 
    ON r.asset_id = b.id
    AND b.asset_type_id = 6 -- asset type we need
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null
    • 4

  2. Você está descartando as linhas porque tem uma wherecondição na assetstabela:

    AND b.asset_type_id = 6

    Como você deixou a junção reservationspara chegar a assetsquando não há linhas, todos os valores assetssão nulos e essa condição está removendo todas as linhas.

    Não estou 100% no seu esquema, pois não foi fornecido, mas o seguinte deve ter o resultado que você procura:

    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
AND b.asset_type_id = 6 -- asset type we need

WHERE r.deleted_at is null
GROUP BY t.time_start, t.time_end;
    • 1

  3. Esta é outra opção:

    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  
JOIN assets b 
ON      r.asset_id = b.id
    AND b.asset_type_id = 6 
    AND r.deleted_at is null
ON      start_utc < concat('2017-04-02 ', t.time_end)
    AND end_utc   > concat('2017-04-02 ', t.time_start)

GROUP BY t.time_start, t.time_end;
    • 1

relate perguntas