如何按唯一ID分组统计同一小时内SiteAvailSecs字段的总和

问题背景 #

我现有如下数据表，其中level_altopod为站点ID，SiteAvailSecs为站点可用时长（单位：秒）：

level_altopod | datestr    | timestr  | SiteAvailSecs

可用性报告每15分钟（900秒）上报一次，以下是2021-08-30 12:00到13:00这一小时内的数据：

|           124 | 2021-08-30 | 12:45:00 |           900 |
|           121 | 2021-08-30 | 12:45:00 |           900 |
|           122 | 2021-08-30 | 12:45:00 |           900 |
|           120 | 2021-08-30 | 12:45:00 |           900 |
|           124 | 2021-08-30 | 12:30:00 |           900 |
|           120 | 2021-08-30 | 12:30:00 |           900 |
|           122 | 2021-08-30 | 12:30:00 |           900 |
|           121 | 2021-08-30 | 12:30:00 |           900 |
|           121 | 2021-08-30 | 12:15:00 |           900 |
|           120 | 2021-08-30 | 12:15:00 |           900 |
|           124 | 2021-08-30 | 12:15:00 |           900 |
|           122 | 2021-08-30 | 12:15:00 |           900 |
|           124 | 2021-08-30 | 12:00:00 |           900 |
|           122 | 2021-08-30 | 12:00:00 |           900 |
|           121 | 2021-08-30 | 12:00:00 |           900 |
|           120 | 2021-08-30 | 12:00:00 |           900 |

每个ID对应4条记录（时间点分别为12:00、12:15、12:30、12:45），需要查询并累加每个ID的4条记录的SiteAvailSecs值，期望输出结果如下：

|           ID  | Date       | Time     | Site Avail    |    
|           124 | 2021-08-30 | 12:00:00 |          3600 |
|           121 | 2021-08-30 | 12:00:00 |          3600 |
|           122 | 2021-08-30 | 12:00:00 |          3600 |
|           120 | 2021-08-30 | 12:00:00 |          3600 |

之前尝试执行的SQL语句如下，返回结果不符合预期：

SELECT level_altopod, datestr, timestr, SiteAvailSecs
FROM amg_hourly_bts_sensor
where datestr="20210830" and hour(timestr) ="12"
group by "level_altopod";

实际返回结果：

| level_altopod | datestr    | timestr  | SiteAvailSecs |
+---------------+------------+----------+---------------+
|           124 | 2021-08-30 | 12:45:00 |           900 |

错误原因 #

1. group by后面的level_altopod加了引号，变成了字符串常量而非字段名，相当于所有数据按同一个常量分组，最终只会返回1条聚合结果
2. 没有使用聚合函数对SiteAvailSecs做求和计算，直接取了分组后随机一条记录的原值
3. 非分组字段直接出现在select列表中不符合SQL规范（仅MySQL非严格模式下允许，但返回值不可控）

正确SQL写法 #

SELECT 
  level_altopod AS ID,
  datestr AS Date,
  MIN(timestr) AS Time, -- 取该小时最早的时间点12:00:00作为输出时间
  SUM(SiteAvailSecs) AS `Site Avail`
FROM amg_hourly_bts_sensor
WHERE datestr = '20210830' AND HOUR(timestr) = 12
GROUP BY level_altopod, datestr;

如果你的datestr字段存储格式是YYYY-MM-DD字符串类型，把WHERE条件里的datestr值改成'2021-08-30'即可。

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