新列中的 SQL 模式长度

Question

我有一个(非常非常大的)表格，其格式类似于以下内容:

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

我想做的是在第三列中返回值列增加或减少的模式长度(模式为负表示减少，正表示增加)，同时忽略没有变化的 ID .当模式被破坏时，模式应重置为 1 或 -1。

我根本没有解释清楚，所以根据上面的表格，理想情况下的结果是:

+--------+-------+---------+
| 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       |
+--------+-------+---------+

我做了一些研究并遇到了模式匹配，但事实证明我正在使用的 SQL 版本(它是 Amazon Redshift 使用/在 Amazon Redshift 上使用的版本，根据他们的说法，它“基于”PostgreSQL 8.0.2 http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html )) 不支持，或者我很傻。

那么，这是否可以通过 SQL 实现，如果可以，我应该怎么做呢？非常感谢。

Answer 1

在 SQL Server 2012 中，您可以使用 lead() 和 lag() 以及累积和来执行此操作。

非常接近的是:

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

但是，模式会以 1 为增量上升和下降，而不是重新开始。所以，我们需要找到模式中断。以下定义了模式中的中断，然后为递增/递减值序列递增模式:

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