如何识别其值从正变为零或负数的ID？

I’d like to identify IDs whose values transition from positive value to zero or negative value.

我想识别其值从正值转换为零或负值的ID。

So, if we have a table as follows:

所以，如果我们有一个表如下：

ID  VALUES ORDER
1   20     2
1   10     1
2   0      2
2   100    1
3   -10    2
3   5      1
4   0      2
4   0      1
5   -3     2
5   25     1
6   30     2
6  -50     1
7  -10     2
7   0      1
8  -100    3
8  50      2
8  100     1
9  -10     4
9  0       3
9  10      2
9  20      1

The results should be as follows, where ID 2’s values transitioned from 100 to 0, ID 3’s values transitioned from 5 to -10, ID 5’s values transitioned from 25 to -3. We are not interested in ID 6 since its values transitioned from negative to positive values -- we only care about IDs whose values went from positive to 0 or negative (and not vice versa). We can also have IDs that transition from 0 to negative but not 0 to 0:

结果应如下所示，其中ID 2的值从100转换为0，ID 3的值从5转换为-10，ID 5的值从25转换为-3。我们对ID 6不感兴趣，因为它的值从负值转换为正值 - 我们只关心其值从正值到0或负值（反之亦然）的ID。我们也可以将ID从0转换为负但不是0到0：

ID  VALUES  ORDER
2   0       2
2   100     1
3   -10     2
3   5       1
5   -3      2
5   25      1
7  -10      2
7   0       1
8  -100     3
8   50      2
9  -10      4
9   0       3
9   10      2

How would I achieve such a result?

我怎样才能达到这样的效果？

4 个解决方案

#1

You could find the previous and next value for each ID using analytic lag/lead functions:

您可以使用分析滞后/超前函数找到每个ID的上一个和下一个值：

select id, value, seq,
  lag(value) over (partition by id order by seq) as prev_val,
  lead(value) over (partition by id order by seq) as next_val
from your_table
order by id, seq desc;

        ID      VALUE        SEQ   PREV_VAL   NEXT_VAL
---------- ---------- ---------- ---------- ----------
         1         20          2         10           
         1         10          1                    20
         2          0          2        100           
         2        100          1                     0
         3        -10          2          5           
         3          5          1                   -10
         4          0          2          0           
         4          0          1                     0
         5         -3          2         25           
         5         25          1                    -3
         6         30          2        -50           
         6        -50          1                    30
         7        -10          2          0           
         7          0          1                   -10
         8       -100          3         50           
         8         50          2        100       -100
         8        100          1                    50
         9        -10          3          0           
         9          0          2         10        -10
         9         20          1                    10
         9         10          1         20          0

(I changed the column names, as both order and values are reserved). You can then use that as a subquery and filter the combinations you want to see:

（我更改了列名，因为订单和值都是保留的）。然后，您可以将其用作子查询并过滤要查看的组合：

select id, value, seq
from (
  select id, value, seq,
    lag(value) over (partition by id order by seq) as prev_val,
    lead(value) over (partition by id order by seq) as next_val
  from your_table
)
where (prev_val >=0 and value <= 0 and not (prev_val = 0 and value = 0))
or (value >= 0 and next_val <= 0 and not (value = 0 and next_val = 0))
order by id, seq desc;

        ID      VALUE        SEQ
---------- ---------- ----------
         2          0          2
         2        100          1
         3        -10          2
         3          5          1
         5         -3          2
         5         25          1
         7        -10          2
         7          0          1
         8       -100          3
         8         50          2
         9        -10          3
         9          0          2
         9         10          1

As Gordon mentioned, if you only want the IDs - not the values involved - you only need the lead or lag value, not both.

正如戈登提到的，如果你只想要ID - 而不是所涉及的值 - 你只需要超前或滞后值，而不是两者。

#2

I think you can just do this using lead():

我想你可以使用lead（）来做到这一点：

select distinct id
from (select t.*,
             lead(value) over (partition by id order by seq) as next_val,
      from t
     ) t
where value > 0 and next_val <= 0;

Note that I used Alex's naming conventions.

请注意，我使用了Alex的命名约定。

#3

This should give you the same result but in rows

这应该给你相同的结果，但行

select t1.ID, t1.VALUES, t2.VALUES from TABLE t1
join TABLE t2 on t1.VALUE < t2.VALUE and t1.ID = t2.ID and t1.ORDER > t2.ORDER
where t1.ORDER = t2.ORDER +1 and not (t1.VALUE >0 and t2.VALUE >0)

Table would look like this if this is useful for you :

如果这对您有用，表格将如下所示：

ID    Value1    Value2
2     0         100
3     -10       5
5     -3        25
7     -10       0
8     -100      50
9     -10       0
9     0         10

#4

If you are using Oracle 12c, this is a great use case for MATCH_RECOGNIZE. In that case, it would be done like so:

如果您使用的是Oracle 12c，这是MATCH_RECOGNIZE的一个很好的用例。在这种情况下，它将这样做：

SELECT id, value, ord FROM d
MATCH_RECOGNIZE ( partition by id 
                  order by ord
                  all rows per match
                  after match skip past last row
                  pattern (posorzero neg | pos negorzero )
                  define
                    neg as neg.value < 0,
                    negorzero as negorzero.value <= 0,
                    pos as pos.value > 0, 
                    posorzero as posorzero.value>= 0
                    )
order by id, ord desc;

ID  VALUE   ORD
--  -----   ---
2       0     2
2     100     1
3     -10     2
3       5     1
5      -3     2
5      25     1
7     -10     2
7       0     1
8    -100     3
8      50     2
9       0     3
9      10     2

The only thing is, this doesn't include the following row from your desired output:

唯一的问题是，这不包括所需输出中的以下行：

9  -10      4

In all other cases, your desired output seems to be "include only the rows from each id that contribute to the cross-over". This one row that you've included in your desired output would not meet that definition, since these rows:

在所有其他情况下，您所需的输出似乎“仅包括每个id中有助于交叉的行”。您在所需输出中包含的这一行将不符合该定义，因为这些行：

9   0       3
9   10      2

... by themselves represent a cross-over as you've defined it.

......当你定义它时，它们本身就代表了一个交叉。

If you have a consistent and better definition of what the requested output should include, please let me know and I'll update my answer.

如果您对请求的输出应包含的内容有一致且更好的定义，请告诉我，我会更新我的答案。