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

On Thursday, 17 February 2022, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Fri, Feb 18, 2022 at 9:11 AM Shaozhong SHI <shishaozhong(at)gmail(dot)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)
>

Hot to get measures for frequency and magnitude of alternating?
Regards, David