oracle 如何在列中查找负数，后跟正数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26679710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find negative number in a column, which is followed by a positive number
提问by Yte
I have a historical table XYwith these contents:
我有一个XY包含这些内容的历史表：
ID Person_ID Balance_on_account ts
---- ----------- -------------------- ----------
01 05 +10 10.10.14
02 05 -10 20.10.14
03 05 -50 30.10.14
04 05 +50 30.10.14
05 05 -10 30.10.14
06 06 11 11.10.14
07 06 -40 15.10.14
08 06 +5 16.10.14
09 06 -10 30.10.14
and I need to create an SQL query which will give me those Person_ID's and timestamps where are
我需要创建一个 SQL 查询，它会给我那些Person_ID和时间戳在哪里
- a) the
Balance_on_accountis negative - that's the easy one, - b) and at the same time is the record of negative
Balance_on_accountfollowed by a positive number.
- a)
Balance_on_account是否定的 - 那很容易， - b) 并且同时是负数
Balance_on_account后跟正数的记录。
Like for Person_ID = 05I would have the row with ID = 05, and for Person_ID = 06the row with ID = 09.
就像Person_ID = 05我将行与ID = 05，而Person_ID = 06行与ID = 09.
回答by 1010
I never used it, but you could try analytic LEAD function
我从来没有用过，但你可以试试分析 LEAD 函数
SELECT *
FROM (
SELECT ID, Person_ID, Balance_on_account, ts
LEAD (Balance_on_account, 1)
OVER (PARTITION BY Person_ID ORDER BY ID) next_balance
FROM XY)
WHERE Balance_on_account < 0 and next_balance >= 0
ORDER BY ID
LEAD lets you access the following rows in a query without joining with itself. PARTITION BY groups rows by Person_ID so it doesn't mix different person's balances and ORDER BY defines the order within each group. The filtering cannot be done in the inner query because it'd filter out the rows with positive balance.
LEAD 允许您访问查询中的以下行，而无需与自身连接。PARTITION BY 按 Person_ID 对行进行分组，因此它不会混合不同人的余额，并且 ORDER BY 定义每个组内的顺序。过滤不能在内部查询中完成，因为它会过滤掉余额为正的行。
next_balance will be null for the last row.
最后一行的 next_balance 将为空。
source analytic functionsand LEAD
回答by mheptinstall
The following query should give you the expected results provided the database platform you are using supports Common Table Expressionsand Window Functionse.g. SQL Server 2008 and up.
如果您使用的数据库平台支持Common Table Expressions，Window Functions例如 SQL Server 2008 及更高版本，以下查询应该会为您提供预期的结果。
WITH TsOrder AS
(
SELECT
Id
, Person_Id
, Balance_on_account
, ts
, ROW_NUMBER() OVER(PARTITION BY Person_Id
ORDER BY ts, Id) AS ts_Order
FROM
[TableName]
)
SELECT
*
FROM
TsOrder
LEFT JOIN TsOrder AS NextTs
ON TsOrder.Person_id = NextTs.Person_Id
AND TsOrder.ts_order = NextTs.ts_order - 1
WHERE
TsOrder.Balance_on_account < 0
AND NextTs.Balance_on_account > 0
