嵌套SELECT实现各辖区事故最多街道查询 原语句超时问题修正

• 内层聚合逻辑不完整：使用了count(street)聚合函数但没有搭配GROUP BY district, street子句，在严格语法模式的数据库中会直接报错；即使部分数据库兼容该写法，也只会返回全表聚合的单条结果，完全达不到按街道、辖区维度拆分统计的目的。
• 字段名前后不匹配：内层聚合将统计结果别名为street_cnt，外层过滤条件却使用street_count字段，字段名不一致根本无法完成值匹配。
• 业务逻辑完全偏离需求：需求是每个独立辖区内查找对应事故最多的街道，但原SQL的DISTINCT ON (street)逻辑是按街道维度去重，没有以辖区作为核心分组维度，最终结果只会输出全库事故数最高的单条街道，完全不匹配按辖区拆分的统计要求。
• 性能层面存在硬伤：整个查询没有设置有效的分组边界，子查询是无过滤的全表扫描聚合，嵌套的最大值子查询会触发全表重复扫描，针对千万级规模的城市事故数据表，必然出现查询超时的问题。

结合原SQL使用的PostgreSQL数据库语法特性，调整后的实现逻辑不需要做全表重复关联，仅通过一次聚合扫描即可完成统计，两种常用写法可选：

写法1：适配原有的DISTINCT ON语法 #

调整核心是把DISTINCT ON的分区维度从street改为district，配合排序规则取每个辖区下事故数最高的街道：

SELECT DISTINCT ON (district)
  district,
  street,
  accident_cnt
FROM (
  SELECT
    district,
    street,
    COUNT(*) AS accident_cnt
  FROM accident_table
  GROUP BY district, street
) district_street_stats
ORDER BY district, accident_cnt DESC

写法2：通用窗口函数写法 #

不依赖数据库独有的DISTINCT ON语法，可在MySQL 8.0、PostgreSQL、SQL Server等所有支持窗口函数的数据库中通用：

SELECT
  district,
  street,
  accident_cnt
FROM (
  SELECT
    district,
    street,
    COUNT(*) AS accident_cnt,
    ROW_NUMBER() OVER (
      PARTITION BY district 
      ORDER BY COUNT(*) DESC
    ) AS accident_rank
  FROM accident_table
  GROUP BY district, street
) base_stats
WHERE accident_rank = 1

配套性能优化建议 #

• 为accident_table创建联合索引(district, street)，该索引属于覆盖索引，聚合统计过程不需要回表读取全表数据，扫描效率会提升数个量级
• 若业务需要保留同辖区内并列最高事故数的街道，可将ROW_NUMBER()替换为RANK()函数，避免截断并列的统计结果
• 若表中存在street或district为NULL的无效记录，可在聚合前增加WHERE district IS NOT NULL AND street IS NOT NULL的过滤条件，减少无效数据的扫描开销

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