SMA
SMA

Reputation: 377

Converting PromQL to SQL

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.

Upvotes: 1

Views: 1557

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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

Upvotes: 1

Related Questions