I need to convert my prometheus metrics to pgsql using timescaleDB convertor, I'm trying to change my queries from promQL to SQL. I had lots of queries and almost did them all but I stuck in this one and don't now can I convert this promQL query to SQL:
sum(sum(increase(my_metric{}[1h])) by (label) > bool 0)
My table is something like this: (ordered by label)
time(timestamptz), value(double), label(integer)
=================================================
2021-02-17 21:50:01.690092+00 3.1 1
2021-02-17 21:45:01.390661+00 4.1 1
2021-02-17 21:50:01.690092+00 4.5 2
2021-02-17 21:45:01.390661+00 4.5 2
2021-02-17 21:50:01.690092+00 1.23 3
2021-02-17 21:45:01.390661+00 4.46 3
What I need is a counter that check value of each label during time, if the value is changed then it should add one to counter.
With promQL it's an easy task:
sum(sum(increase(my_metric{}[5m])) by (label) > bool 0)
or even:
sum(max(increase(my_metric{}[5m])) by (label) > bool 0)
For above data sample result should be 2 , it should add one for label 1 and 3, label 2 didn't change during that time so it should not add any to counter.
In Postgres you can use lag() and a cumulative count:
select t.*,
count(*) filter (where prev_value is distinct from value) over (order by time)
from (select t.*,
lag(value) over (order by time) as prev_value
from t
) t