窗口函数中行级计算实现？LeetSQL机器进程耗时统计问题

表结构 #

Activity表结构如下：

主键：(machine_id, process_id, activity_type)

需求 #

计算每台机器完成一个进程的平均耗时，耗时为同一进程的end时间戳减去start时间戳，结果保留3位小数。

现有自连接解决方案 #

已通过自连接实现需求，代码如下：

select a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3) as processing_time 
from Activity a1
join Activity a2 
on a1.machine_id=a2.machine_id and a1.process_id=a2.process_id
and a1.activity_type='start' and a2.activity_type='end'
group by a1.machine_id

尝试窗口函数时遇到的问题 #

尝试用窗口函数实现，初始代码如下：

SELECT a1.machine_id, AVG(a1.timestamp) OVER (PARTITION BY machine_id) AS processing_time
FROM Activity AS a1

添加GROUP BY a1.machine_id后报错，错误信息：

[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'Activity.timestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW)

能否用窗口函数实现？ #

可以。核心思路是先通过窗口函数为每个进程匹配对应的start和end时间戳，再计算平均耗时。

实现代码 #

方案一：仅针对start行匹配对应end时间，再聚合计算平均

SELECT 
    machine_id,
    ROUND(AVG(end_timestamp - start_timestamp), 3) AS processing_time
FROM (
    SELECT 
        machine_id,
        process_id,
        timestamp AS start_timestamp,
        -- 窗口函数获取同一机器+进程的end时间戳
        MAX(CASE WHEN activity_type = 'end' THEN timestamp END) 
            OVER (PARTITION BY machine_id, process_id) AS end_timestamp
    FROM Activity
    WHERE activity_type = 'start'
) AS process_times
GROUP BY machine_id;

方案二：先计算每个进程的耗时，再去重聚合

SELECT 
    machine_id,
    ROUND(AVG(timestamp_diff), 3) AS processing_time
FROM (
    SELECT 
        machine_id,
        process_id,
        -- 窗口函数计算同一进程的end与start时间差
        MAX(CASE WHEN activity_type = 'end' THEN timestamp END) 
            OVER (PARTITION BY machine_id, process_id) 
        - MIN(CASE WHEN activity_type = 'start' THEN timestamp END) 
            OVER (PARTITION BY machine_id, process_id) AS timestamp_diff
    FROM Activity
) AS process_diffs
-- 每个进程只保留一条记录用于计算
WHERE activity_type = 'start'
GROUP BY machine_id;

报错原因解释 #

你添加GROUP BY a1.machine_id后报错的核心原因是窗口函数与GROUP BY的逻辑冲突：

• 当使用GROUP BY时，SQL要求SELECT列表中的列要么是GROUP BY的分组键，要么被聚合函数包裹（如SUM、AVG）。
• 你代码中的AVG(a1.timestamp) OVER (PARTITION BY machine_id)是窗口函数，它会为每一行生成一个基于窗口的计算值，而非对整个分组做聚合。此时原始的timestamp列既不在GROUP BY中，也没有被聚合函数处理，同时窗口函数的结果不属于合法的SELECT列类型，因此触发语法错误。

简单来说：GROUP BY是将数据分组后做整组聚合，窗口函数是为每行计算独立的窗口值，两者不能直接混用在同一个SELECT语句的逻辑中。

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