On Thu, 17 Feb 2022 at 21:20, Thomas Munro <thomas.mu...@gmail.com> wrote:
> On Fri, Feb 18, 2022 at 9:11 AM Shaozhong SHI <shishaozh...@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) > Given 2 or more such columns, is there any measure that can be calculated to tell which one alternates more than others? Regards, David
