MySQL：如何从7天数据中取Top5计算日均步数平均值？

嘿，我来帮你搞定这个每周取Top5计算日均的需求！咱们的核心目标很清晰：给每个用户（按encodedid区分）按周分组，每周挑出步数最多的5天算日均，活跃分钟数也遵循同样逻辑，还要覆盖16周的数据。下面是具体的实现方案：

1. 先搞定每周Top5日均步数 #

我们可以用窗口函数给每个用户每周内的每日步数排个序，挑出前5天再算平均。这里用ROW_NUMBER()来做排名，它会给每周内的天数按步数从高到低编号，这样我们就能轻松筛选出Top5：

WITH weekly_steps_ranked AS (
    SELECT
        encodedid,
        DATE_TRUNC('week', activity_date) AS week_start, -- 把日期截断到每周起始，方便分组
        steps,
        -- 按用户+周分组，给步数降序排名
        ROW_NUMBER() OVER (
            PARTITION BY encodedid, DATE_TRUNC('week', activity_date)
            ORDER BY steps DESC
        ) AS step_rank
    FROM activities
    -- 要是只需要最近16周的数据，加上这个过滤条件就行
    -- WHERE activity_date >= CURRENT_DATE - INTERVAL '16 weeks'
)
SELECT
    encodedid,
    week_start,
    AVG(steps) AS avg_top5_steps
FROM weekly_steps_ranked
WHERE step_rank <= 5 -- 只保留每周步数前5的天数
GROUP BY encodedid, week_start
ORDER BY encodedid, week_start;

2. 再处理每周Top5日均活跃分钟数 #

活跃分钟数应该是轻度、中度（你提到的fairly_act_min）再加重度活跃分钟数（一般是very_act_min）的总和对吧？先算出每日总活跃分钟，再用同样的排名逻辑挑Top5天算平均：

WITH weekly_active_ranked AS (
    SELECT
        encodedid,
        DATE_TRUNC('week', activity_date) AS week_start,
        -- 计算每日总活跃分钟，用COALESCE处理可能的NULL值（比如某天没有重度活跃数据）
        (lightly_act_min + fairly_act_min + COALESCE(very_act_min, 0)) AS total_active_min,
        -- 按总活跃分钟降序排名
        ROW_NUMBER() OVER (
            PARTITION BY encodedid, DATE_TRUNC('week', activity_date)
            ORDER BY (lightly_act_min + fairly_act_min + COALESCE(very_act_min, 0)) DESC
        ) AS active_rank
    FROM activities
    -- 同样可以加16周的过滤条件
    -- WHERE activity_date >= CURRENT_DATE - INTERVAL '16 weeks'
)
SELECT
    encodedid,
    week_start,
    AVG(total_active_min) AS avg_top5_active_min
FROM weekly_active_ranked
WHERE active_rank <= 5
GROUP BY encodedid, week_start
ORDER BY encodedid, week_start;

3. 把两个结果合并到一张表（可选） #

要是想把步数和活跃分钟数的结果放在一起看，咱们可以把两个查询的结果关联起来：

WITH weekly_steps_ranked AS (
    SELECT
        encodedid,
        DATE_TRUNC('week', activity_date) AS week_start,
        steps,
        ROW_NUMBER() OVER (
            PARTITION BY encodedid, DATE_TRUNC('week', activity_date)
            ORDER BY steps DESC
        ) AS step_rank
    FROM activities
    WHERE activity_date >= CURRENT_DATE - INTERVAL '16 weeks'
),
weekly_active_ranked AS (
    SELECT
        encodedid,
        DATE_TRUNC('week', activity_date) AS week_start,
        (lightly_act_min + fairly_act_min + COALESCE(very_act_min, 0)) AS total_active_min,
        ROW_NUMBER() OVER (
            PARTITION BY encodedid, DATE_TRUNC('week', activity_date)
            ORDER BY (lightly_act_min + fairly_act_min + COALESCE(very_act_min, 0)) DESC
        ) AS active_rank
    FROM activities
    WHERE activity_date >= CURRENT_DATE - INTERVAL '16 weeks'
),
top5_steps_avg AS (
    SELECT
        encodedid,
        week_start,
        AVG(steps) AS avg_top5_steps
    FROM weekly_steps_ranked
    WHERE step_rank <=5
    GROUP BY encodedid, week_start
),
top5_active_avg AS (
    SELECT
        encodedid,
        week_start,
        AVG(total_active_min) AS avg_top5_active_min
    FROM weekly_active_ranked
    WHERE active_rank <=5
    GROUP BY encodedid, week_start
)
SELECT
    t.encodedid,
    t.week_start,
    t.avg_top5_steps,
    a.avg_top5_active_min
FROM top5_steps_avg t
JOIN top5_active_avg a
    ON t.encodedid = a.encodedid AND t.week_start = a.week_start
ORDER BY t.encodedid, t.week_start;

几个要注意的小细节 #

• 不同数据库的DATE_TRUNC行为可能有点不一样，比如有些数据库周起始是周日，有些是周一，你可以根据自己用的数据库调整这个函数的参数。
• 如果某一周用户的有效天数不足5天（比如只记录了3天），上面的查询会自动用现有天数算平均。要是你想排除这类周，可以在最后分组的SELECT里加个HAVING COUNT(*) >=5。
• 我用的是ROW_NUMBER()，它会在步数相同时给不同行分配不同的排名。要是你想让并列的步数都被计入（比如一周有6天步数相同且都是最高，想把这6天都算进去），可以换成RANK()或者DENSE_RANK()，不过ROW_NUMBER()更贴合“取最多的5天”的需求。

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