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?
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;