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

On Fri, Feb 18, 2022 at 9:11 AM Shaozhong SHI <shishaozhong@gmail.com> wrote:
> How to calculate frequency of positive and negative numbers and define and calculate frequency of alteration of
polarity?
>
> Surely, we can use frequency of alteration of polarity and level of change (e.g., size of positive and negative
numbers)to measure degree and frequency of alteration.
 
>
> Any ideas in doing so in postgres tables' columns full of positive and negative numbers?

Window functions might be useful to detect polarity changes:

postgres=# create table time_series (time int, value int);
CREATE TABLE
postgres=# insert into time_series values (1, -5), (2, -5), (3, 10), (4, -3);
INSERT 0 4
postgres=# select time,
                  value,
                  sign(lag(value) over (order by time)) != sign(value)
as flipped
             from time_series;
 time | value | flipped
------+-------+---------
    1 |    -5 |
    2 |    -5 | f
    3 |    10 | t
    4 |    -3 | t
(4 rows)

On Fri, Feb 18, 2022 at 9:11 AM Shaozhong SHI <shishaozhong@gmail.com> wrote:
> How to calculate frequency of positive and negative numbers and define and calculate frequency of alteration of polarity?
>
> Surely, we can use frequency of alteration of polarity and level of change (e.g., size of positive and negative numbers) to measure degree and frequency of alteration.
>
> Any ideas in doing so in postgres tables' columns full of positive and negative numbers?

Window functions might be useful to detect polarity changes:

postgres=# create table time_series (time int, value int);
CREATE TABLE
postgres=# insert into time_series values (1, -5), (2, -5), (3, 10), (4, -3);
INSERT 0 4
postgres=# select time,
                  value,
                  sign(lag(value) over (order by time)) != sign(value)
as flipped
             from time_series;
 time | value | flipped
------+-------+---------
    1 |    -5 |
    2 |    -5 | f
    3 |    10 | t
    4 |    -3 | t
(4 rows)

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)