如何在CnosDB中按天计算平均温度？SQL编写方法咨询

核心逻辑是将时间戳字段转换为仅包含年月日的日期格式，再按该日期分组，计算每组的温度平均值。不同数据库的日期处理函数略有差异，以下是常用数据库的实现方式：

MySQL/MariaDB #

使用DATE()函数提取时间戳的日期部分：

SELECT DATE(time) AS date, AVG(temperature) AS avg_temperature
FROM air
GROUP BY DATE(time);

PostgreSQL #

可以用DATE_TRUNC()截断到天，或直接将时间戳转为日期类型：

-- 方式1：用DATE_TRUNC
SELECT DATE_TRUNC('day', time)::DATE AS date, AVG(temperature) AS avg_temperature
FROM air
GROUP BY DATE_TRUNC('day', time);

-- 方式2：直接转换为日期
SELECT CAST(time AS DATE) AS date, AVG(temperature) AS avg_temperature
FROM air
GROUP BY CAST(time AS DATE);

SQL Server #

使用CONVERT()或CAST()将时间戳转为日期：

-- 方式1：CONVERT
SELECT CONVERT(date, time) AS date, AVG(temperature) AS avg_temperature
FROM air
GROUP BY CONVERT(date, time);

-- 方式2：CAST
SELECT CAST(time AS date) AS date, AVG(temperature) AS avg_temperature
FROM air
GROUP BY CAST(time AS date);

Oracle #

使用TRUNC()函数将时间戳截断到天：

SELECT TRUNC(time, 'DD') AS date, AVG(temperature) AS avg_temperature
FROM air
GROUP BY TRUNC(time, 'DD');

注意事项 #

• 确保time字段为datetime/timestamp类型，temperature为数值类型（如INT/FLOAT），否则AVG()函数无法正常计算。
• 如果temperature存在NULL值，AVG()会自动忽略这些值；若需将NULL视为特定值（如0），可使用AVG(COALESCE(temperature, 0))。

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