2

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

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.

Kylo
  • 109
  • 8

1 Answers1

3

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for that, it looks great, and it works but there is small issue. what If I don't want to use id, just show average and filter on specific values for category 2 and 4 ? Is it possible to use multiple filter where in having clause ? – Kylo Aug 10 '21 at 12:52
  • @Kylo . . . You can remove `id` from the `select` and remove the `group by`. – Gordon Linoff Aug 10 '21 at 12:54