mysql - SQL:配对并计算样本

Question

我有下表(示例):

ID |LOCATION|DAY           
1  | 1      |20190301   
1  | 2      |20190301  
1  | 3      |20190301  
1  | 1      |20190302   
1  | 4      |20190302  
1  | 4      |20190305     
1  | 5      |20190302   
2  | 4      |20190301       
2  | 1      |20190301   
2  | 3      |20190303   
2  | 2      |20190305  

其中 ID 是车号，Location 是位置 id，时间是 YYYYMMDD。我想编写一个 SQL 查询来计算每个月每个 carID 的“成对位置”的数量(YYYYMM):汽车在位置 i 和 j 存在的次数。也就是说，最终的结果应该是这样的

ID|LOCATION 1|LOCATION 2|MONTH |count1|count 2  
1 | 1        |2         |201903| 2    | 1  
1 | 1        |3         |201903| 2    | 1  
1 | 1        |4         |201903| 2    | 2  
1 | 1        |5         |201903| 2    | 1   
1 | 2        |3         |201903| 1    | 1  
1 | 2        |4         |201903| 1    | 2  

其中 count1 是位置 1 的计数，count2 是位置 2 的计数，我们为每对 location1 和 location2 构建它。

为了构建对，我尝试了:

Select n1.location, n2.location
From
(
  Select location
  from table
) n1,
(
  Select location
  from table
) n2
Where n1.location < n2.location
Order by n1.location, n2.location

但我想计算每个位置的数量 (count1, count2) 而不是对数。

我可以在 SQL 的子查询中执行此操作吗？任何意见，将不胜感激。

Answer 1

这是一个奇怪的请求。您正在寻找两个位置的独立计数，但在一行中对齐(这很奇怪，因为有很多重复数据)。

您可以通过汇总before加入来做到这一点:

with l as (
      select l.id, l.location, date_format(l.time, '%Y%m') as yyyymm,
             count(*) as cnt
      from carlocations l
      group by l.id, l.location, date_format(l.time, '%Y%m') 
     )
select l1.id, l1.location as location1, l2.location2, l1.yyyymm, l1.cnt as cnt2, l2.cnt as cnt2
from l l1 join
     l l2
     on l1.id = l2.id and l1.yyyymm = l2.yyyymm and 
        l1.location < l2.location;

MySQL 8+ 支持 with。在早期版本中，您需要在 from 子句中重复子查询。

编辑:

没有 CTE，这看起来像:

select l1.id, l1.location as location1, l2.location2, l1.yyyymm, l1.cnt as cnt2, l2.cnt as cnt2
from (select l.id, l.location, date_format(l.time, '%Y%m') as yyyymm,
             count(*) as cnt
      from carlocations l
      group by l.id, l.location, date_format(l.time, '%Y%m') 
     ) l1 join
     (select l.id, l.location, date_format(l.time, '%Y%m') as yyyymm,
             count(*) as cnt
      from carlocations l
      group by l.id, l.location, date_format(l.time, '%Y%m') 
     ) l2
     on l1.id = l2.id and l1.yyyymm = l2.yyyymm and 
        l1.location < l2.location;