Posgresql crosstab alternative

Question

I am looking for a crosstab alternative. I have data in Timescaledb (posgresql) hypertable in multiple rows:

For example I need to calculate average of category = 1 when category 2 > 4. What I am doing at them moment is to pivot using crosstab and then I calculate average of category 1. Is there a way of doing this without pivot (crosstab) ?

There are queries where I cannot use crosstab because is not working where I have only singe 'id' selected. It is aggregating category as one row.

I am looking for something that would work with single value as 'id' and was faster then crosstab. I have huge dataset.

Answer 1

You could just use conditional aggregation. In Postgres, this would look like:

select id,
       avg(value) filter (where category = 1)
from t
where category in (1, 2)
group by id
having avg(value) filter (where category = 2) > 4;