CODEWITHSUNDEEP
sqlpostgresqlcrosstabtimescaledbhypertable
Kylo
Kylo

Reputation: 119

Posgresql crosstab alternative

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.

Upvotes: 2

Views: 346

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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;

Upvotes: 3

Related Questions