我需要洗掉資料集中的一些行，其中的行speed等于 0 并持續 N 次（假設 N 為 2）。表的結構demo如下所示：
| ID | 車 | 速度 | 時間 |
|---|---|---|---|
| 1 | 富 | 0 | 1 |
| 2 | 富 | 0 | 2 |
| 3 | 富 | 0 | 3 |
| 4 | 富 | 1 | 4 |
| 5 | 富 | 1 | 5 |
| 6 | 富 | 0 | 6 |
| 7 | 酒吧 | 0 | 1 |
| 8 | 酒吧 | 0 | 2 |
| 9 | 酒吧 | 5 | 3 |
| 10 | 酒吧 | 5 | 4 |
| 11 | 酒吧 | 5 | 5 |
| 12 | 酒吧 | 5 | 6 |
然后我希望通過使用生成如下表window_function：
| ID | 車 | 速度 | 時間 | 持久 |
|---|---|---|---|---|
| 1 | 富 | 0 | 1 | 3 |
| 2 | 富 | 0 | 2 | 3 |
| 3 | 富 | 0 | 3 | 3 |
| 4 | 富 | 1 | 4 | 2 |
| 5 | 富 | 1 | 5 | 2 |
| 6 | 富 | 0 | 6 | 1 |
| 7 | 酒吧 | 0 | 1 | 2 |
| 8 | 酒吧 | 0 | 2 | 2 |
| 9 | 酒吧 | 5 | 3 | 4 |
| 10 | 酒吧 | 5 | 4 | 4 |
| 11 | 酒吧 | 5 | 5 | 4 |
| 12 | 酒吧 | 5 | 6 | 4 |
然后我可以通過使用輕松排除這些行 WHERE NOT (speed = 0 AND lasting > 2)
把我試過的代碼放在這里，但它沒有回傳我期望的值，我想這些FROM (SELECT ... FROM (SELECT ...可能不是解決問題的最佳實踐：
SELECT g3.*, count(id) OVER (PARTITION BY car, cumsum ORDER BY id) as num
FROM (SELECT g2.*, sum(grp2) OVER (PARTITION BY car ORDER BY id) AS cumsum
FROM (SELECT g1.*, (CASE ne0 WHEN 0 THEN 0 ELSE 1 END) AS grp2
FROM (SELECT g.*, speed - lag(speed, 1, 0) OVER (PARTITION BY car) AS ne0
FROM (SELECT *, row_number() OVER (PARTITION BY car) AS grp FROM demo) g ) g1 ) g2 ) g3
ORDER BY id;
uj5u.com熱心網友回復：
您可以使用窗函式LAG()檢查speed每行的前一個值，并使用SUM()窗函式為連續值創建組。
然后使用COUNT()視窗函式，您可以計算每個組中的行數，以便您可以過濾出speed超過 2 行的組中的0行：
SELECT id, car, speed, time
FROM (
SELECT *, COUNT(*) OVER (PARTITION BY car, grp) counter
FROM (
SELECT *, SUM(flag::int) OVER (PARTITION BY car ORDER BY time) grp
FROM (
SELECT *, speed <> LAG(speed, 1, speed - 1) OVER (PARTITION BY car ORDER BY time) flag
FROM demo
) t
) t
) t
WHERE speed <> 0 OR counter <= 2
ORDER BY id;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343252.html
標籤：PostgreSQL 数数 where子句 窗函数