Reformatting multi-feature timeseries data with SQL

I have a table that has the following formatting:

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,

I would like to group by day then average a set of values by a ‘feature’ column and join the results into a single table.

This would look like:

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

I’m able to join the average values by day using:

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;

But, I’m not sure how I would do this by feature using an aggregate query and produce the final join. How can I pull this off?

Answer

You can use conditional aggregation:

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;

Advertisement

Answer