Tracking Growth of a Metric over Time In TimescaleDB-CodePudding

I'm currently running timescaleDB. I have a table that looks similar to the following

        one_day        | name | metric_value 
------------------------ ---------------------
2022-05-30 00:00:00 00 | foo  |    400
2022-05-30 00:00:00 00 | bar  |    200
2022-06-01 00:00:00 00 | foo  |    800
2022-06-01 00:00:00 00 | bar  |    1000

I'd like a query that returns the % growth and raw growth of metric, so something like the following.

 name | % growth | growth 
-------------------------
 foo  |  200%    | 400
 bar  |  500%    | 800

I'm fairly new to timescaleDB and not sure what the most efficient way to do this is. I've tried using LAG, but the main problem I'm facing with that is OVER (GROUP BY time, url) doesn't respect that I ONLY want to consider the same name in the group by and can't seem to get around it. The query works fine for a single name.

Thanks!

CodePudding user response：

Use LAG to get the previous value for the same name using the PARTITION option:

lag(metric_value,1,0) over (partition by name order by one_day)

This says, when ordered by 'one_day', within each 'name', give me the previous (the second parameter to LAG says 1 row) value of 'metric_value'; if there is no previous row, give me '0'.