526互联

【LeetCode 2854. 滚动平均步数】MySQL过去n日滑动窗口

发布时间 2024-01-12 09:27:30作者: yhm138

题目地址

https://leetcode.cn/problems/rolling-average-steps/description/

代码

WITH t1 AS
(
    SELECT 
        user_id,
        steps_date,
        CASE
            WHEN COUNT(steps_count) OVER (
                PARTITION BY user_id 
                ORDER BY date(steps_date)
                RANGE BETWEEN INTERVAL @n_days DAY PRECEDING AND CURRENT ROW
            ) >= @n_days + 1 THEN -- 确保至少有n天的数据
                ROUND(AVG(steps_count) OVER (
                    PARTITION BY user_id 
                    ORDER BY date(steps_date)
                    RANGE BETWEEN INTERVAL @n_days DAY PRECEDING AND CURRENT ROW
                ), 2)
            ELSE NULL -- 如果不足n天，就不计算平均值
        END AS rolling_average
    FROM 
        Steps
        , (SELECT @n_days := 2 ) vars   -- 我不知道怎么改，用的时候把这一行注释掉，把所有@n_days替换为实际数值
)

SELECT * from t1 
WHERE rolling_average IS NOT NULL;