如何在MySQL中统计并排序实体在日期和地点的共现次数

需要编写SQL脚本，统计不同实体对在同一日期（DATE）和地点（LOCATION）出现的次数。实际场景包含数百个不同实体、12个月的日期数据及20+个地点，目标是找出所有实体对在同日期同地点的共现频次并统计总次数。

示例输入数据 #

期望输出结果 #

（最终需按Count降序排序，以下为全量组合展示）

尝试过的错误SQL #

SELECT t1.Entity as Entity1, t2.Entity as Entity2, COUNT(*) as Count
FROM (
SELECT Entity, CONCAT(Date, Location) AS ConcatenatedValue, COUNT(*) 
FROM occurrences 
WHERE Year(Date) = 2022) t1,
(SELECT Entity, CONCAT(Date, Location) AS ConcatenatedValue, COUNT(*)
FROM occurrences
WHERE Year(Date) = 2022) t2
WHERE t1.ConcatenatedValue = t2.ConcatenatedValue
GROUP BY Entity1, Entity2
ORDER BY Count

正确解决方案 #

方法说明 #

核心思路是通过自连接将表与自身关联，匹配同一日期和地点的实体对，同时通过t1.Entity < t2.Entity避免重复统计（比如A-B和B-A视为同一对），最后按实体对分组统计共现的次数。

完整SQL脚本 #

SELECT
    t1.Entity AS Entity1,
    t2.Entity AS Entity2,
    COUNT(DISTINCT CONCAT(t1.Date, '-', t1.Location)) AS Count
FROM occurrences t1
JOIN occurrences t2
    ON t1.Date = t2.Date
    AND t1.Location = t2.Location
    AND t1.Entity < t2.Entity
-- 可选：添加日期过滤条件，比如统计2023年的数据
WHERE YEAR(t1.Date) = 2023
GROUP BY t1.Entity, t2.Entity
ORDER BY Count DESC;

关键细节解析 #

1. 自连接条件：通过t1.Date = t2.Date AND t1.Location = t2.Location确保只匹配同一日期同一地点的实体。
2. 避免重复对：t1.Entity < t2.Entity保证每个实体对只被统计一次，不会同时出现(A,B)和(B,A)。
3. 去重统计：使用COUNT(DISTINCT CONCAT(t1.Date, '-', t1.Location))确保每个日期-地点组合只被计数一次，避免同一组合内的实体多次关联导致重复统计。
4. 性能优化：如果数据量较大，建议在Date和Location字段上建立联合索引，提升自连接的查询效率。

内容的提问来源于stack exchange，提问作者srschaecher