如何按连续4年分组统计movies表中电影的平均时长？

需求梳理 #

• 排除Year为0的无效记录
• 从2001年开始，每4个连续年份划分为一组（如2001-2004、2005-2008……）
• 统计每组的最小年份、最大年份，以及转换为小时:分钟格式的电影平均时长

实现代码 #

SELECT
    MIN(Year) AS group_start_year,
    MAX(Year) AS group_end_year,
    CONCAT(
        FLOOR(AVG(movie_running_time) / 60),
        ':',
        LPAD(MOD(AVG(movie_running_time), 60), 2, '0')
    ) AS average_running_time
FROM movies
WHERE Year != 0 AND Year >= 2001
GROUP BY (Year - 2001) DIV 4
ORDER BY group_start_year;

代码细节说明 #

1. 分组逻辑：(Year - 2001) DIV 4 会将2001-2004的年份计算为0，2005-2008计算为1，以此类推，精准实现每4年一组的分组规则。
2. 年份过滤：WHERE Year != 0 AND Year >= 2001 直接剔除无效的0值年份，同时确保统计范围从2001年开始。
3. 时长格式化：
   • AVG(movie_running_time) 计算该组电影的平均时长（单位：分钟）
   • FLOOR(AVG(movie_running_time) / 60) 提取小时部分
   • MOD(AVG(movie_running_time), 60) 提取分钟部分，LPAD(..., 2, '0') 确保分钟数显示为两位数（例如5分钟会显示为05）
   • CONCAT 将小时和分钟拼接为小时:分钟的标准格式

数据库兼容调整 #

如果你的数据库不支持DIV运算符，可按以下方式替换分组语句：

• PostgreSQL：FLOOR((Year - 2001) / 4)::INT
• SQL Server：(Year - 2001) / 4（整数除法）

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