postgresql - SQL Pattern Length in New Column -

i have (very large) table of similar format following:

+--------+-------+ | id     | value | +--------+-------+ |      1 | 5     | |      2 | 6     | |      3 | 6     | |      4 | 4     | |      5 | 3     | |      6 | 2     | |      7 | 4     | |      8 | 5     | +--------+-------+ 

what i'd able return pattern length of value column increasing or decreasing in third column (with pattern being negative decreasing , positive increasing), while ignoring ids there no change. pattern should reset 1 or -1 when pattern broken.

i've not explained @ all, table above, ideally result be:

+--------+-------+---------+ | id     | value | pattern | +--------+-------+---------+ |      1 | 5     | 0/null  | |      2 | 6     | 1       | |      3 | 6     | 1       | |      4 | 4     | -1      | |      5 | 3     | -2      | |      6 | 2     | -3      | |      7 | 4     | 1       | |      8 | 5     | 2       | +--------+-------+---------+ 

i did research , came across pattern matching, turns out either version of sql i'm using (it's version used by/on amazon redshift , according them 'based on' postgresql 8.0.2 http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html)) doesn't support it, or i'm being silly.

so, possible sql, , if how should go it? many thanks.

in sql server 2012, can lead() , lag() , cumulative sum.

something comes quite close this:

select t.*, sum(nextinc) on (order id) pattern (select t.*,              (case when lead(t.value) > t.value 1                    when lead(t.value) = t.value 0                    else -1 end) nextinc,              (case when lag(t.value) > t.value 1 else 0 end) previnc                        table t      ) t; 

however, pattern goes , down in increments of 1 instead of starting over. so, need find pattern breaks. following defines breaks in pattern , increments pattern for sequences of increasing/decreasing values:

select t.*,        sum(nextinc) on (partition grp order id) pattern (select t.*,              sum(case when (prev_value <= value , value <= next_value) or                            (prev_value >= value , value >= next_value)                       0 else 1                  end) on (order id) grp       (select t.*, lead(t.value) on (order id) next_value,                    lag(t.value) on (order id) prev_value,                    (case when lead(t.value) on (order id) > t.value 1                          when lead(t.value) on (order id) = t.value 0                          else -1 end) nextinc               table t            ) t       ) t