在sqlite结果中包括无效值

Question

你好sqlite3专家，

我有一个名为"位置"的表，具有以下结构：

id | positionId | scanTime
 1 |          1 | 2019-05-11 14:18:58.802 +00:00
 2 |          2 | 2019-05-11 14:24:05.518 +00:00
 3 |          1 | 2019-05-11 14:34:01.777 +00:00
 4 |          1 | 2019-05-11 14:34:02.549 +00:00
 5 |          1 | 2019-05-11 14:34:06.560 +00:00
 6 |          1 | 2019-05-11 14:34:07.484 +00:00

我执行以下查询：

SELECT ranges.start, 
       ranges.end,
       count(positionLogs.id) as totalScans
 FROM 
 (
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-55 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-50 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-50 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-45 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-45 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-40 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-40 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-35 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-35 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-30 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-30 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-25 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-25 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-20 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-20 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-15 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-15 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-10 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-10 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-5 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-5 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-0 minutes') as end) as ranges
  LEFT join positionlogs on (positionlogs.scanTime >= ranges.start AND positionlogs.scanTime <=ranges.end)
   where positionlogs.positionId = 1
   group by start;

我要实现的目标是获得类似于以下结果的结果：

start            | end                | totalScans
2019-05-11 13:40 | 2019-05-11 13:45   | 0
2019-05-11 13:45 | 2019-05-11 13:50   | 0
2019-05-11 13:50 | 2019-05-11 13:55   | 0
2019-05-11 13:55 | 2019-05-11 14:00   | 0
2019-05-11 14:00 | 2019-05-11 14:05   | 0
2019-05-11 14:05 | 2019-05-11 14:10   | 0
2019-05-11 14:10 | 2019-05-11 14:15   | 0
2019-05-11 14:15 | 2019-05-11 14:20   | 1
2019-05-11 14:20 | 2019-05-11 14:25   | 1
2019-05-11 14:25 | 2019-05-11 14:30   | 4
2019-05-11 14:30 | 2019-05-11 14:35   | 0

，但我的查询不是我的查询：

start            | end                | totalScans
2019-05-11 14:15 | 2019-05-11 14:20   | 1
2019-05-11 14:20 | 2019-05-11 14:25   | 1
2019-05-11 14:25 | 2019-05-11 14:30   | 4

我尝试了几种不同的方式而没有成功。任何帮助/建议都非常感谢！

Answer 1

好吧，您离开了加入positionlogs，但是有一个WHERE子句，希望positionlogs.positionid为1。ranges的行没有发现postionlog的匹配行无法满足这一点，因此被过滤掉了。将表达式从WHERE移动到ON子句进行修复。

SELECT ranges.start, 
       ...
       ... ranges
       LEFT JOIN positionlogs
                 ON positionlogs.scanTime >= ranges.start
                    AND positionlogs.scanTime <=ranges.end
                    AND positionlogs.positionId = 1
       GROUP BY ranges.start;