获取通过特定摄像头的汽车 [英] Get the cars that passed specific cameras

问题描述

MYSQL/MARIADB模式和示例数据:

CREATE DATABASE IF NOT EXISTS `puzzle` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

USE `puzzle`;

DROP TABLE IF EXISTS `event`;

CREATE TABLE `event` (
  `eventId` bigint(20) NOT NULL AUTO_INCREMENT,
  `sourceId` bigint(20) NOT NULL COMMENT 'think of source as camera',
  `carNumber` varchar(40) NOT NULL COMMENT 'ex: 5849',
  `createdOn` datetime DEFAULT NULL,
  PRIMARY KEY (`eventId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `event` (`eventId`, `sourceId`, `carNumber`, `createdOn`) VALUES
    (1, 44, '4456', '2016-09-20 20:24:05'),
    (2, 26, '26484', '2016-09-20 20:24:05'),
    (3, 5, '4456', '2016-09-20 20:24:06'),
    (4, 3, '72704', '2016-09-20 20:24:15'),
    (5, 3, '399606', '2016-09-20 20:26:15'),
    (6, 5, '4456', '2016-09-20 20:27:25'),
    (7, 44, '72704', '2016-09-20 20:29:25'),
    (8, 3, '4456', '2016-09-20 20:30:55'),
    (9, 44, '26484', '2016-09-20 20:34:55'),
    (10, 26, '4456', '2016-09-20 20:35:15'),
    (11, 3, '72704', '2016-09-20 20:35:15'),
    (12, 3, '399606', '2016-09-20 20:44:35'),
    (13, 26, '4456', '2016-09-20 20:49:45');

我想获取在20:24到20:45期间具有sourceId = 3 AND(26 OR 44)的CarNumber.由于实际表包含3亿多条记录，因此查询需要快速进行.

I want to get CarNumber(s) that have sourceId = 3 AND (26 OR 44) during 20:24 to 20:45. the query need to be fast since the real table contains over 300 million records.

到目前为止，以下是我可以查询的最大值(甚至无法产生有效的结果)

so far below is the maximum i could go with the query (its not even producing valid results)

select * from event e where 
e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00' 
and e.sourceId IN(3,26,44) group by e.carNumber;

所提供数据的正确结果:

the correct results for the provided data:

carNumber
4456
72704

我真的很困惑，被困住了.我试过EXISTS，Joins，子查询没有运气，所以我想知道SQL是否能够解决此问题，还是应该使用后端编码?

I am really puzzled and stuck. i tried EXISTS, Joins, sub-query without luck, so I wonder if SQL is able to solve this question or should I use backend coding?

正在使用的MySQL/MariaDB版本:

MySQL / MariaDB version in use:

mariadb-5.5.50

mariadb-5.5.50

mysql-5.5.51

mysql-5.5.51

推荐答案

如果需要快速处理，则假设您在event(createdOn, carNumber, SourceId)上具有索引，则以下可能可以正常工作:

If you need this to be fast, then the following might work, assuming you have an index on event(createdOn, carNumber, SourceId):

select e.carNumber 
from event e 
where e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
group by e.carNumber
having sum(e.sourceId = 3) > 0 and
       sum(e.sourceId IN (26, 44)) > 0;

我倾向于将其更改为:

select e.carNumber 
from event e 
where e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00' and
      e.sourceId in (3, 26, 44)
group by e.carNumber
having sum(e.sourceId = 3) > 0 and
       sum(e.sourceId IN (26, 44)) > 0;

然后为了性能，甚至是这样:

And then for performance, even this:

select carNumber
from ((select carNumber, sourceId
       from event e
       where e.sourceId = 3 and
             e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
      ) union all
      (select carNumber, sourceId
       from event e
       where e.sourceId = 26 and
             e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
      ) union all
      (select carNumber, sourceId
       from event e
       where e.sourceId = 44 and
             e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
      )
     ) e
group by e.carNumber
having sum(e.sourceId = 3) > 0 and
       sum(e.sourceId IN (26, 44)) > 0;

此版本可以利用event(sourceId, createdOn, carNumber)上的索引.每个子查询都应非常有效地使用此索引，将少量数据汇总在一起以进行最终聚合.

This version can take advantage of an index on event(sourceId, createdOn, carNumber). Each subquery should use this index very effectively, bringing a small'ish amount of data together for the final aggregation.

这篇关于获取通过特定摄像头的汽车的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！