mysql - 难以为事件构建 SQL 查询

Question

我在尝试获取与在不同日期发生的多个事件关联的数据计数时遇到问题。

假设我正在跟踪一群飞机观察员，他们中的每个人都可能在某一天在某个特定机场观察到来自世界各地的多架飞机。我想生成一个列表，每天每个观察员一行，其中包含观察员 ID、日期、他(总是“他”，对吗？)当天发现的飞机数量、个别航空公司的数量飞机属于，以及航空公司属于多少个国家。所以，我想要这样的结果:

 +-----------+------------+---------+----------+-----------+
 |           |            |  Planes |          |   Airline |
 | SpotterID | Date       | spotted | Airlines | Countries |
 +-----------+------------+---------+----------+-----------+
 |      1234 | 2017-04-15 |      28 |       11 |         4 |
 +-----------+------------+---------+----------+-----------+
 |      1234 | 2017-04-16 |      65 |       19 |         7 |
 +-----------+------------+---------+----------+-----------+
 |      5678 | 2017-04-22 |      39 |       14 |         6 |
 +-----------+------------+---------+----------+-----------+
 |      6677 | 2017-04-28 |      74 |       29 |         9 |
 +-----------+------------+---------+----------+-----------+

所以，(MySQL 5.7.17)我的测试表如下所示(我可能忘记了几个索引)。

飞机发现事件表定义为:

CREATE TABLE `SpottingEvents` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SpotterID` int(11) NOT NULL,
 `SpotDateTime` datetime NOT NULL,
 `PlaneID` int(11) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_SpotterID_PlaneID_DateTime` (`SpotterID`,`PlaneID`,`SpotDateTime`),
 KEY `indx_SpotterID_DateTime` (`SpotterID`,`SpotDateTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

平面表定义为:

CREATE TABLE `Planes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `PlaneID` int(11) NOT NULL,
 `PlaneTypeID` int(11) NOT NULL,
 `AirlineID` int(11) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_PlaneID_AirlineID` (`PlaneID`,`AirlineID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

airlines 表定义为:

CREATE TABLE `Airlines` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `AirlineID` int(11) NOT NULL,
 `AirlineName` varchar(100) NOT NULL,
 `CountryID` int(11) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_AirlineID` (`AirlineID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

国家表定义为:

CREATE TABLE `Countries` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `CountryID` int(11) NOT NULL,
 `CountryName` varchar(100) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_CountryID` (`CountryID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我的问题是最后两列，即 Airlines 和 Countries 计数。我已经尝试了几种方法来做到这一点，包括以下几种:

select distinct sev.SpotDateTime, sev.SpotterID, count(*) as planes_count,
(select count(*) from ( select distinct cnt.CountryID 
  from Countries as cnt
  inner join Airlines as aln on al.CountryID = cnt.CountryID
  inner join Planes as pl on pl.AirlineID = aln.AirlineID 
  where pl.PlaneID = sev.PlaneID ) as t1) as countries_count  
from SpottingEvents as sev
# where sev.UserID = 1234
group by SpotDateTime
order by SpotDateTime

这会导致错误 Unknown column 'sev.planeID' in 'where clause'但由于我不是专家，所以我只是没有得到预期的结果。那么，如何才能达到预期的效果呢？

Answer 1

您正在寻找 COUNT(DISTINCT)。加入所需的表格，然后计数。

select
  se.spotterid,
  date(se.spotdatetime) as spot_date,
  count(*) as planes,
  count(distinct p.airlineid) as airlines,
  count(distinct a.countryid) as countries
from spottingevents se
join planes p on p.planeid = se.planeid
join airlines a on a.airlineid = p.airlineid
group by se.spotterid, date(se.spotdatetime)
order by date(se.spotdatetime), se.spotterid;