如何基于各用户最新有效评分记录计算所有地点的平均评分

解决思路 #

你现在的写法不符合需求的核心原因是：你的逻辑是取每个地点的最新一条记录的评分，但需求是先取每个用户在对应地点的最新有效评分，再对同一地点的所有用户有效评分求平均值。
正确的实现逻辑分两步：

1. 对每个(用户ID, 地点ID)组合，过滤掉所有rating为空的记录后，按timestamp倒序排序取第一条，得到该用户对该地点的最新有效评分
2. 按地点维度对上述得到的用户评分求平均值，就是最终的地点平均评分

推荐实现（支持窗口函数的SQL环境，如MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH user_loc_latest_rating AS (
    SELECT 
        user_id,
        loc_id,
        loc_name,
        rating,
        -- 按用户+地点分组，时间倒序排序，有评分的记录里第一条就是最新有效
        ROW_NUMBER() OVER (PARTITION BY user_id, loc_id ORDER BY `Timestamp` DESC) AS rn
    FROM Table_T
    WHERE rating IS NOT NULL -- 直接过滤无评分的记录，剩下的都是有效候选
)
SELECT 
    loc_id,
    loc_name,
    AVG(CAST(rating AS DECIMAL)) AS avgRating
FROM user_loc_latest_rating
WHERE rn = 1 -- 只取每个用户每个地点的最新有效评分
GROUP BY loc_id, loc_name;

老版本MySQL（不支持CTE和窗口函数）的兼容写法 #

SELECT 
    t.loc_id,
    t.loc_name,
    AVG(CAST(t.rating AS DECIMAL)) AS avgRating
FROM Table_T t
INNER JOIN (
    -- 先查询每个用户每个地点的最大有效评分时间
    SELECT 
        user_id,
        loc_id,
        MAX(`Timestamp`) AS latest_valid_ts
    FROM Table_T
    WHERE rating IS NOT NULL
    GROUP BY user_id, loc_id
) tm ON t.user_id = tm.user_id 
    AND t.loc_id = tm.loc_id 
    AND t.`Timestamp` = tm.latest_valid_ts
GROUP BY t.loc_id, t.loc_name;

逻辑说明 #

• 我们优先过滤了所有无评分的记录，天然避免了“最新签到无评分需要回退查找更早有效记录”的需求，剩下的记录里每个用户每个地点的最大时间对应的就是符合要求的有效评分
• 不需要使用CASE函数，过滤+取最大值的逻辑已经能覆盖你的业务规则

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