1

I'm trying to create a query that will return data in this format:

id time value
1 1 2.5
1 2 3.5
1 3 6.4
2 1 8.3
2 2 8.5

I'm using Timescaledb and wish to use one of their downsampling functions for each unique id

select
        id,
        asap_smooth(time, value, 80)
from
        data
group by
        id

Where the asap_smooth aggregate function returns a custom datatype that can be unnested without the id into the following format:

SELECT * FROM unnest(
    (SELECT asap_smooth(time, value, 80)
     FROM data));
time value
1 2.5
2 3.5
3 6.4

Is there anyway to get each row tagged with the id it belongs to? I've tried this query however I can't seem to unpack the record type into their own columns:

select
    id,
    unnest ((select asap_smooth(time, value, 80))) t
from
    data
group by
    id
id t
1 {1,2.5}
1 {2,3.5}
1 {3,6.4}
2 {1,8.3}
2 {2,8.5}
1

1 Answer 1

1

The type Timevector is unlike other standard data types in Postgres. Similar to an array of <ROW type>, but using a vector instead of the array, which is otherwise only used in the system catalogs.
Simply decomposing the resulting row type should do it:

SELECT id, (unnest(asap_smooth(time, value, 80))).*
FROM   data
GROUP  BY id;

But I would rather use this more explicit form:

SELECT sub.id, asap_row.*
FROM  (
   SELECT id, asap_smooth(time, value, 80) AS asap
   FROM   data
   GROUP  BY id
   ) sub
LEFT   JOIN LATERAL unnest(asap) AS asap_row ON true;

Because the second form preserves rows from the subquery sub where asap_smooth() returns NULL or an empty set. The first form eliminates such rows. See:

And because of this:

Plus, I am more confident the second form actually works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.