如何结合多SQL查询计算场地相对用户平均距离的表现值？

核心思路 #

把用户个人最近5次平均距离的计算结果和原始数据关联，先算出每个用户在对应场地的实际距离与个人平均的差值，再按场地、日期分组求这些差值的平均值，就能得到场地的相对表现。

基础版本SQL查询 #

SELECT 
    d.venue,
    d.date,
    AVG(d.distance - ua.avg_distance) AS Performance
FROM 
    "database" d
JOIN (
    -- 计算每个用户最近5次的平均距离
    SELECT 
        name, 
        AVG(distance) AS avg_distance
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS seqnum
        FROM "database"
    ) s
    WHERE seqnum <= 5
    GROUP BY name
) ua ON d.name = ua.name
GROUP BY d.venue, d.date;

匹配示例逻辑的调整版本 #

如果需要像示例中那样，计算用户排除当前场地记录外的最近5次平均距离（比如Paul的平均不含Lakeview的数据），可以修改子查询，用窗口函数计算每条记录对应的用户历史平均（不含当前记录）：

SELECT 
    venue,
    date,
    AVG(distance - user_avg) AS Performance
FROM (
    SELECT 
        *,
        -- 取当前记录之前的最近5条记录计算平均
        AVG(distance) OVER (
            PARTITION BY name 
            ORDER BY date DESC 
            ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING
        ) AS user_avg
    FROM "database"
) sub
WHERE user_avg IS NOT NULL -- 排除无历史记录的用户（如示例中的Tom）
GROUP BY venue, date;

这个版本会忽略Tom的记录（他没有历史数据），最终Lakeview的结果为(100-100 + 140-160)/2 = -10，和示例预期完全一致。

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