使用SQL重新格式化多要素时序数据

Question

我有一个表格，它的格式如下：

id | uid | created_at | feature | value

1, 1, 2019-10-1 20:26:32, 'weight', 155.0,
2, 1, 2019-10-1 23:26:32, 'weight', 150.0,
3, 1, 2019-10-2 10:00:00, 'sleep', 8.0,
4, 1, 2019-10-3 10:00:00, 'calories', 2000.0,
5, 1, 2019-10-4 10:00:00, 'exercise', 30.0,

我想按天分组，然后按“feature”列平均一组值，并将结果连接到单个表中。

这看起来像这样：

date | weight | sleep | calories | exercise

2019-10-1, 152.5, NULL, NULL, NULL
2019-10-2, NULL, 8.0, NULL, NULL
2019-10-3, NULL, NULL, 2000.0, NULL
2019-10-4, NULL, NULL, NULL, 30.0

我可以使用以下命令加入每天的平均值：

SELECT cast(e.created_at AS date) AS dt, avg(e.value) AS avg_value
FROM entries e
GROUP BY cast(e.created_at AS date)
ORDER BY cast(e.created_at AS date) ASC;

但是，我不确定如何使用聚合查询按特征执行此操作，并生成最终的连接。我怎么才能成功呢？

Answer 1

您可以使用条件聚合：

select e.created_at::date,
       avg(e.value) filter (where feature = 'weight') as avg_weight,
       avg(e.value) filter (where feature = 'sleep') as avg_sleep,
       avg(e.value) filter (where feature = 'calories') as avg_calories
from entries e
group by e.created_at::date
order by e.created_at::date;