僅選擇通過 where-test 的相鄰行

我想對我的數據應用一個選擇過程，該過程不包括我的子句應該接受的行，WHERE但在失敗的行之後跟隨的行WHERE。

例子。我有一個 gps 列表，如果它們超過速度限制，我location想把它們放在一個實體中。tour

SELECT _id, speed FROM location;

結果（sqlite3語法）：

1|0
2|0
3|3
4|3
5|3
6|0
7|0
8|0
9|3
10|3
11|3
12|0

所以，如果我現在SELECT，WHERE speed > 2我會得到3,4,5and 9,10,11。這兩者顯然應該是分開的旅行。我能否以一種只導致結果的方式製定我的 SQL，3,4,5因為以下實體將導致WHERE speed > 2測試失敗？

首先我想，我可以簡單地返回所有這些實體，但現在我認為會有兩個問題。SELECT首先，當它被呼叫時，可能會有很多這樣的。其次，可能會刪除行。因此，11可能會有更高的數字而不是行，因為_id它仍然應該屬於同一tour行，因為和這一行之間沒有行，10並且都通過WHERE speed > 2. 沒有行在WHERE10 和下一行之間失敗是完全未知的，應用程序正在等待這個結果SELECT。

我故意不要求數據庫特定的答案。如果您知道數據庫 X 或 Y 的簡單解決方案，則將其添加為解決方案沒有問題。真正的問題是，如何使用標準 SQL 來解決它。我認為每個著名的數據庫都應該有某種方法來解決標準可解決任務集之外的任何任務。

我不知道這是否適用於 sqlite3，但你沒有用它來標記問題，所以這是一個 Oracle 解決方案。

SELECT ID, Speed FROM
(
   SELECT ID, Speed, MIN(Speed) OVER (ORDER BY ID) InSequence  
   FROM
   (
       SELECT ID, Speed, SUM(Speed) OVER (ORDER BY ID) SpeedSum
       FROM location 
   )
   WHERE SpeedSum > 0 
) 
WHERE InSequence > 0
ORDER BY ID;

SUM(Speed)分析刪除了領先的零速行，並且分析MIN(Speed)允許我們從剩餘的第一個零速行開始消除行。樣本數據可以按如下方式創建：

drop table location;

create table location as (select level id, 
  case when level between 3 and 5 then 3 
       when level between 9 and 11 then 3
       else 0 end speed from dual connect by level <=12);

select * from location;

通過以下所有變體，它似乎表現正確，但我可能錯過了一種模式：

UPDATE location SET Speed=1 WHERE ID=2;
UPDATE location SET Speed=2 WHERE ID=3;
UPDATE location SET Speed=1 WHERE ID=6;
DELETE FROM location WHERE ID IN (7,9);


UPDATE location SET Speed=0 WHERE ID=2;
UPDATE location SET Speed=5 WHERE ID=1;

此解決方案只需要一次全表掃描，而不是兩次Noam 解決方案需要，但它只找到第一組值，而 Noam 找到所有組。它還假設門檻值為零，但可以針對任何門檻值進行修改。

這個解決方案很糟糕，因為它太複雜了。如果您對問題有更簡單的解決方案，請繼續展示它！！！

好的，我找到了類似的解決方案。對我幫助很大的是我意識到我之後無法確定哪個 id 確實是之前的後續，但是我可以WHERE通過知道遊覽的 start_id 來辨識第一個失敗的很容易（第一個返回由問題中SELECT的）。從那裡很容易回溯。

為了更好的可讀性，我分了 5 個步驟放入臨時視圖（它變得太複雜了）。我tour之前叫的現在叫track現在，因為這是 KML 中使用的術語。

涉及的步驟：

1. 找到最後一個已經被跟踪的foreign_id（在跟踪表中，對本次討論不重要）
2. 找到第一個_id通過WHERE(start_id)的位置
3. 在失敗_id後找到第一個（first_fail）start_id``WHERE
4. 找到通過(end_id)_id之前的最後一個first_fail``WHERE
5. 放在start_id一起end_id以更簡單地查詢結果

下面是程式碼（記住每一步都按照與上述相同的順序查看）：

DROP VIEW IF EXISTS last_tracked;
DROP VIEW IF EXISTS start_id;
DROP VIEW IF EXISTS first_fail;
DROP VIEW IF EXISTS end_id;
DROP VIEW IF EXISTS next_track;
CREATE VIEW last_tracked AS SELECT end_location FROM track order by end_location DESC LIMIT 1;
CREATE VIEW start_id AS SELECT _id FROM location WHERE sped >= 2 AND _id > (SELECT * FROM last_tracked) ORDER BY _id LIMIT 1;
CREATE VIEW first_fail AS SELECT _id FROM location WHERE sped < 2 AND _id >= (SELECT * FROM start_id) ORDER BY _id LIMIT 1;
CREATE VIEW end_id AS SELECT _id FROM location WHERE _id >= (SELECT * FROM start_id) AND _id < (SELECT * FROM first_fail) ORDER BY _id DESC LIMIT 1;
CREATE VIEW next_track AS SELECT (SELECT * FROM start_id), (SELECT * FROM end_id);
SELECT * FROM next_track;

引用自：https://dba.stackexchange.com/questions/5741