SQLite:获取所有条目的每月、每周或每日平均值

Question

我有一个SQLite记录数据库，格式是这样的：

date        location  temperature
1568463916  room 1    20.0
1568463916  room 2    25.0
1568463916  room 3    30.0
...
1568460316  room 1    15.5
1568460316  room 2    20.5
1568460316  room 3    21.3

每小时插入三个新记录，每个房间一个。

对于每月平均值，需要此输出：

month  avg_temperature  location
01     21.333           room 1
01     24.5             room 2
01     19.0             room 3
...
12     20.4             room 1
12     31.31            room 2
12     13.37            room 3

可以重复使用同一查询来获取每周平均值(第 00-07 天(和每日平均值(第 00-23 小时(。

为了获得每月平均值，我假设我将选择：

• now和now - 1 year之间date的所有记录
• 每条记录的月份，strftime(date, "unixepoch")为month
• 每location，每month就得到avg(temperature)
• 结果是每个房间每个月的平均温度rooms*12行

但是，当我使用GROUP BY语句时，我只得到每个月的最后一行。构造此类查询的正确方法是什么？

这是我尝试过的查询：

SELECT strftime("%m", date, "unixepoch") month,
avg(temperature) avg_temperature,
location
FROM table
WHERE date > date("now", "unixepoch", "-1 year")
AND   date < date("now", "unixepoch")
GROUP BY location, month
ORDER BY month

Answer 1

这应该可以做你想要的：

select date(datetime(date, 'unixepoch'), 'start of month') as month,
location,
avg(temperature)
from t
group by date(datetime(date, 'unixepoch'), 'start of month') as month,
location
order by month, location;