如何修改SQL查询，基于6小时时间范围计算滚动平均值？

原来的查询是基于前后各1行的行数窗口计算滚动平均，要改成当前记录CHARTTIME前后3小时的时间窗口，核心是把ROWS BETWEEN替换为基于时间范围的窗口定义。以下是修改后的完整查询：

sql_query <- "SELECT SUBJECT_ID, HADM_ID, chartevents.ITEMID, VALUE, CHARTTIME, LABEL, 
  AVG(SAFE_CAST(VALUE AS INT)) OVER(
    PARTITION BY SUBJECT_ID
    ORDER BY CHARTTIME
    RANGE BETWEEN INTERVAL 3 HOUR PRECEDING AND INTERVAL 3 HOUR FOLLOWING
  ) AS MV_AVG
FROM chartevents
INNER JOIN d_items ON chartevents.ITEMID = d_items.ITEMID
WHERE chartevents.ITEMID IN (211,220045)
ORDER BY SUBJECT_ID, CHARTTIME 
"

关键修改点说明 #

• 把ROWS BETWEEN 1 PRECEDING and 1 FOLLOWING替换为RANGE BETWEEN INTERVAL 3 HOUR PRECEDING AND INTERVAL 3 HOUR FOLLOWING：
  • RANGE BETWEEN用于基于值范围而非行数的窗口计算，这里针对CHARTTIME这个时间字段，用INTERVAL 3 HOUR定义前后3小时的时间区间。
  • 这样窗口会包含所有CHARTTIME在当前记录时间±3小时内的行，而非固定的前后各1行，完全匹配你的业务需求。

注意事项 #

• 确保CHARTTIME是时间/日期时间类型，如果是字符串格式，需要先通过PARSE_TIMESTAMP或CAST转换为时间类型，否则INTERVAL无法生效。
• SAFE_CAST(VALUE AS INT)会把无法转换为整数的VALUE转为NULL，这些NULL值不会参与AVG计算；如果需要处理这类情况，可以用COALESCE给NULL设置默认值。
• PARTITION BY SUBJECT_ID确保每个患者的滚动平均独立计算，这个逻辑和原查询一致，无需调整。

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