Re: frequency of positive and negative numbers, sizes of numbers and frequency of alteration of polarity

On Fri, Feb 18, 2022 at 10:42 AM Shaozhong SHI <shishaozhong@gmail.com> 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)