On Fri, Feb 18, 2022 at 10:42 AM Shaozhong SHI <shishaozhong@xxxxxxxxx> wrote:
> Given 2 or more such columns, is there any measure that can be calculated to tell which one alternates more than others?
Well, you could report non-flips as NULL and flips as magnitude, and
then wrap that query in another query to compute whatever statistical
properties you need... and you could have multiple columns so you're
computing those numbers for each input column... I was mainly trying
to point out the LAG() facility, which lets you compare a row with the
preceding row, according to some sort order, which I think you'd want
to build your query on top of. Hope that helps...
postgres=# with
flips as (select time,
value,
case
when sign(lag(value) over (order by
time)) != sign(value)
then abs(lag(value) over (order by
time) - value)
end as flip_magnitude
from time_series)
select count(flip_magnitude) as num_flips,
avg(flip_magnitude) as avg_magnitude
from flips;
count | avg
-------+---------------------
2 | 14.0000000000000000
(1 row)
