MS SQL Server中Partition by结合Group by统计车祸数据异常求助

为什么你的代码会出现异常结果？ #

1. 第一次代码（仅窗口函数）：count(...) over (partition by sex_of_driver, accident_severity) 会给每一条匹配的关联记录都带上对应分组的事故计数，所以会出现大量重复行——每一条事故-车辆匹配记录都会显示一次该分组的总数。
2. 第二次代码（窗口函数+GROUP BY）：GROUP BY 已经将结果聚合为 sex_of_driver + accident_severity 的唯一组合，每个分组仅保留一行数据。此时窗口函数的分区逻辑和GROUP BY完全一致，相当于统计当前单行所在分组的行数，结果自然都是1。

正确实现方案 #

1. 基础统计：不同性别-事故严重程度的事故数量

这和你期望的目标代码（去掉CASE）逻辑一致，直接用 GROUP BY 配合聚合函数即可：

select
    vehicle.sex_of_driver,
    accident.accident_severity,
    count(*) as num_accidents
from
    SQL.dbo.accident as accident
inner join SQL.dbo.vehicle as vehicle on
    accident.accident_index = vehicle.accident_index
where
    sex_of_driver not in (3, -1) -- 简化排除条件
group by
    vehicle.sex_of_driver,
    accident.accident_severity
order by
    accident.accident_severity, vehicle.sex_of_driver

2. 扩展需求：同时计算占比

如果需要计算每个性别下各事故严重程度的占比（或总事故中的占比），可以在GROUP BY的基础上结合窗口函数实现：

select
    vehicle.sex_of_driver,
    accident.accident_severity,
    count(*) as num_accidents,
    -- 计算当前分组在对应性别总事故中的占比，保留两位小数
    cast(count(*) as decimal(10,2)) 
    / sum(count(*)) over (partition by vehicle.sex_of_driver) as severity_ratio
from
    SQL.dbo.accident as accident
inner join SQL.dbo.vehicle as vehicle on
    accident.accident_index = vehicle.accident_index
where
    sex_of_driver not in (3, -1)
group by
    vehicle.sex_of_driver,
    accident.accident_severity
order by
    accident.accident_severity, vehicle.sex_of_driver

3. 另一种实现：用窗口函数+DISTINCT去重（不推荐，性能较差）

如果你坚持想用窗口函数，也可以通过DISTINCT去掉重复的分组行，但这种方式性能不如直接GROUP BY：

select distinct
    sex_of_driver,
    accident_severity,
    count(accident_severity) over (partition by sex_of_driver, accident_severity) as num_accidents
from
    SQL.dbo.accident as accident
inner join SQL.dbo.vehicle as vehicle on
    accident.accident_index = vehicle.accident_index
where
    sex_of_driver not in (3, -1)

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