如何在BigQuery中一次性查询多个时段的平均步数？

你之前通过WHERE指定单个时段查询平均值的方式效率太低，要一次性得到所有时段的结果，只需要用GROUP BY子句按Hour字段分组，再对每组计算平均步数即可。

基础查询语句 #

SELECT 
  Hour,
  AVG(Step_Total) AS Average_Steps
FROM `analyticscapstone-378406.fitbit_data.hourly_steps`
GROUP BY Hour;

这个语句会把所有相同Hour的记录归为一组，自动计算每组的平均步数，返回每个时段对应的平均值。

优化：按时间顺序排序 #

直接执行上面的语句，结果会按字符串规则排序（比如10:00 AM会排在1:00 AM前面），不符合实际时间逻辑。推荐两种方式让结果按从12:00 AM到11:00 PM的顺序排列：

方式1：用CASE语句自定义排序 #

SELECT 
  Hour,
  AVG(Step_Total) AS Average_Steps
FROM `analyticscapstone-378406.fitbit_data.hourly_steps`
GROUP BY Hour
ORDER BY 
  CASE Hour
    WHEN '12:00 AM' THEN 0
    WHEN '1:00 AM' THEN 1
    WHEN '2:00 AM' THEN 2
    WHEN '3:00 AM' THEN 3
    WHEN '4:00 AM' THEN 4
    WHEN '5:00 AM' THEN 5
    WHEN '6:00 AM' THEN 6
    WHEN '7:00 AM' THEN 7
    WHEN '8:00 AM' THEN 8
    WHEN '9:00 AM' THEN 9
    WHEN '10:00 AM' THEN 10
    WHEN '11:00 AM' THEN 11
    WHEN '12:00 PM' THEN 12
    WHEN '1:00 PM' THEN 13
    WHEN '2:00 PM' THEN 14
    WHEN '3:00 PM' THEN 15
    WHEN '4:00 PM' THEN 16
    WHEN '5:00 PM' THEN 17
    WHEN '6:00 PM' THEN 18
    WHEN '7:00 PM' THEN 19
    WHEN '8:00 PM' THEN 20
    WHEN '9:00 PM' THEN 21
    WHEN '10:00 PM' THEN 22
    WHEN '11:00 PM' THEN 23
  END;

方式2：用PARSE_TIME转换时间排序（更简洁） #

BigQuery的PARSE_TIME函数可以将字符串格式的时间转换为时间类型，直接按时间排序：

SELECT 
  Hour,
  AVG(Step_Total) AS Average_Steps
FROM `analyticscapstone-378406.fitbit_data.hourly_steps`
GROUP BY Hour
ORDER BY PARSE_TIME('%I:%M %p', Hour);

这里%I:%M %p是匹配12:00 AM这类格式的时间模板，转换后排序会自动按正确的时间顺序排列。

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