如何在不使用子查询的情况下计算时间占比分布

问题背景 #

现有一张#stats表，存储车辆、年份、行驶速度、对应行驶时长（hoursSpent）、年度总时长（hoursInPeriod）信息，需计算不同速度下的时间占比。注意：hoursSpent是各速度段的行驶时长，hoursInPeriod是车辆年度总时长，前者总和不等于后者。

数据表结构及示例数据 #

create table #stats (
    car varchar(30), yr INT, speed NUMERIC(5,1), hoursSpent INT, hoursInPeriod INT
    PRIMARY KEY(car, yr, speed)
)

INSERT INTO #stats (
    car, yr, speed, hoursSpent, hoursInPeriod
)
VALUES  ('Volvo', 2019, 50, 20, 300)
,   ('Volvo', 2019, 65, 13, 300)
,   ('Volvo', 2019, 70, 30, 300)

,   ('Volvo', 2020, 50, 10, 250)
,   ('Volvo', 2020, 65, 25, 250)
,   ('Volvo', 2020, 70, 40, 250)

,   ('Volvo', 2021, 50, 5, 100)
,   ('Volvo', 2021, 70, 10, 100)

,   ('Tesla', 2019, 50, 5, 100)
,   ('Tesla', 2019, 65, 20, 100)
,   ('Tesla', 2019, 70, 10, 100)

,   ('Tesla', 2020, 50, 10, 100)
,   ('Tesla', 2020, 65, 20, 100)
,   ('Tesla', 2020, 70, 13, 100)

,   ('Tesla', 2021, 50, 30, 100)
,   ('Tesla', 2021, 65, 20, 100)
,   ('Tesla', 2021, 70, 50, 100)

常规可行查询（受限无法使用） #

常规嵌套子查询可得到正确结果，但受API限制无法执行：

SELECT  speed
, SUM(hoursSpent) * 1.0 / (SELECT SUM(hoursInPeriod) FROM (select distinct car, yr, hoursInPeriod FROM #stats s) x)
FROM    #stats ss
GROUP BY speed

预期结果 #

API限制说明 #

当前访问数据的API仅支持以下语法：

• SELECT列（支持复杂表达式、函数、窗口函数，但禁止嵌套SELECT FROM语句）
• 表名
• WHERE条件
• GROUP BY
• ORDER BY

禁止使用：派生表、连接、关联子查询。

错误尝试 #

此前尝试的查询因COUNT分布不均无法得到正确结果：

SELECT speed
    , SUM(hoursSpent) / (SELECT SUM(hoursInPeriod) * 1.0 / COUNT(*))
FROM #stats ss
GROUP BY speed

符合API限制的解决方案 #

利用窗口函数SUM() OVER()配合DISTINCT特性，先计算唯一car+yr组合的hoursInPeriod总和，再计算各速度的占比：

全局速度占比（不分组） #

SELECT 
    speed,
    SUM(hoursSpent) * 1.0 / SUM(DISTINCT hoursInPeriod) OVER () AS distribution
FROM #stats
GROUP BY speed

支持按车辆/年份分组 #

按车辆分组

SELECT 
    car,
    speed,
    SUM(hoursSpent) * 1.0 / SUM(DISTINCT hoursInPeriod) OVER (PARTITION BY car) AS distribution
FROM #stats
GROUP BY car, speed

按年份分组

SELECT 
    yr,
    speed,
    SUM(hoursSpent) * 1.0 / SUM(DISTINCT hoursInPeriod) OVER (PARTITION BY yr) AS distribution
FROM #stats
GROUP BY yr, speed

按车辆+年份分组

SELECT 
    car,
    yr,
    speed,
    SUM(hoursSpent) * 1.0 / SUM(DISTINCT hoursInPeriod) OVER (PARTITION BY car, yr) AS distribution
FROM #stats
GROUP BY car, yr, speed

原理说明 #

• SUM(DISTINCT hoursInPeriod) OVER ()：在整个数据集范围内，对每个唯一的hoursInPeriod（对应唯一car+yr组合）求和，等价于常规查询中嵌套子查询的总时长计算结果。
• 添加PARTITION BY子句后，窗口函数会在指定分组内计算唯一hoursInPeriod的总和，实现分组统计需求。

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