RANK data by value in the column

I’d like to divide the data into separate groups (chunks) based on the value in the column. If the value increase above certain threshold, the value in the “group” should increase by 1.

This would be easy to achieve in MySQL, by doing CASE WHEN @val > 30 THEN @row_no + 1 ELSE @row_no END however I am using Amazon Redshift where this is not allowed.

Sample fiddle: http://sqlfiddle.com/#!15/00b3aa/6

Suggested output:

ID	Value	Group
1	11	1
2	11	1
3	22	1
4	11	1
5	35	2
6	11	2
7	11	2
8	11	2
9	66	3
10	11	3

Answer

A cumulative sum should do what you want:

SELECT *, sum((val>=30)::INTEGER) OVER (ORDER BY id BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM mydata ORDER BY id;
 id | val | sum
----+-----+-----
  1 |  11 |   0
  2 |  11 |   0
  3 |  22 |   0
  4 |  11 |   0
  5 |  35 |   1
  6 |  11 |   1
  7 |  11 |   1
  8 |  11 |   1
  9 |  66 |   2
 10 |  11 |   2