I am trying to write a sql query to calculate RSI(Relative strength index) in Postgresql & implement in grafana. Can anyone help with such query?
I have just 3 columns in table -
time_stamp bigint,
metric_name text,
value.max int
the RSI is about value.max for plotting in grafana as a timeseries query
RSI Reference: Relative Strength Index (RSI) [ChartSchool]
the query for RSI I used is as below:
SELECT
$__unixEpochGroup(timestamp/1000, '1m') AS "time",
value.max - lag(value.max) OVER (ORDER BY $__unixEpochGroup(timestamp/1000, '1m')) AS "diff",
case when diff > 0 then diff else 0 end as "Gain",
case when diff < 0 then diff else 0 end as "Loss",
avg(Gain) OVER (ORDER BY $__unixEpochGroup(timestamp/1000, '1m') ROWS 20 PRECEDING) as "avg_gain",
avg(Loss) OVER (ORDER BY $__unixEpochGroup(timestamp/1000, '1m') ROWS 20 PRECEDING) AS "avg_loss"
FROM spectrum_schema.my_table
WHERE
$__unixEpochFilter(time) and
metric_name = 'CPUUtilization'
ORDER BY 1
this gives ERROR** 
**: **
window function calls may not be nested
**
the normal query for timeseries data plotting is working as below:
SELECT
$__unixEpochGroup(timestamp/1000, '1m') AS "time",
value.max AS "value.max"
FROM spectrum_schema.my_table
WHERE
$__unixEpochFilter(time) and
metric_name = 'NetworkThroughput' and
ORDER BY 1
Can this query be tuned as well?