我正在嘗試根據 aa 列中的值標記一些行，但我還需要根據當前行值為上一行和下一行放置相同的標志。
所以下面是我的桌子
-- create a table
CREATE TABLE table1 (
id INTEGER PRIMARY KEY,
time INTEGER,
event varchar NOT NULL
);
-- insert some values
INSERT INTO table1 VALUES (1, '1', 'r');
INSERT INTO table1 VALUES (2, '2', 'r');
INSERT INTO table1 VALUES (3, '3', 's');
INSERT INTO table1 VALUES (4, '4', 'r');
INSERT INTO table1 VALUES (5, '5', 'r');
INSERT INTO table1 VALUES (6, '6', 'r');
INSERT INTO table1 VALUES (7, '7', 's');
INSERT INTO table1 VALUES (8, '8', 'r');
INSERT INTO table1 VALUES (9, '9', 'r');
INSERT INTO table1 VALUES (10, '10', 's');
我想添加一個flag包含0forevent='s'以及它的上一行和下一行的列。但由于系統限制，不能使用leador或 temp 表。lag
所以我的最終輸出看起來像這樣
----------- -------- ------
| timestamp | events | flag |
----------- -------- ------
| 1 | r | 1 |
| 2 | r | 0 |
| 3 | s | 0 |
| 4 | r | 0 |
| 5 | r | 1 |
| 6 | r | 1 |
| 7 | r | 0 |
| 8 | s | 0 |
| 9 | r | 0 |
| 10 | r | 0 |
| 11 | s | 0 |
----------- -------- ------
到目前為止我嘗試過的是
SELECT a.time, a.event, 0 as flag
FROM table1 AS a
JOIN table1 AS b
ON b.event = 's' AND abs(a.id - b.id) <= 1
我得到了我需要標記為0但錯過的所有行1
TimeStamp 是有序時間，但為了便于求解，將其轉換為整數。
uj5u.com熱心網友回復：
嘗試以下操作：
With CTE As
(
Select id, time, event,
Case
When event='r' then -10 else id
End as f
From table1
)
Select id, time, event,
Case
when id in
(select f from cte where f<>-10
union
select f 1 from cte where f<>-10
union
select f-1 from cte where f<>-10) then 0 else 1
End As flag
From CTE
其中-10inWhen event='r' then -10 else id是id列中不存在的任何整數值，即使它已加 1。
查看db<>fiddle的演示。
更新以覆寫id列中的空白：
With CTE As
(
Select M.id, M.time, M.event,
Case
When M.event='r' then -10 else id
End as f,
Case
when M.event='s' then
(select top 1 T.id from table1 T where T.id > M.id order by T.id)
else -10
End As Lead_val,
Case
when M.event='s' then
(select top 1 T.id from table1 T where T.id < M.id order by T.id desc)
else -10
End As Lag_val
From table1 M
)
Select T.id, T.time, T.event,
Case
when T.id in (
select f from cte
union
select Lead_val from cte
union
select Lag_val from cte
)
then 0 else 1
End as flag
From table1 T
查看db<>fiddle的演示。
uj5u.com熱心網友回復：
另一種選擇。為了解決間隙的可能性，我使用 row_number 生成無間隙序列，然后使用自連接來避免 LEAD 和 LAG。
with cte as (select *, ROW_NUMBER() over (order by time) as rno from table1
)
select main.*,
case when main.event = 's' then 0
when main.event <> 's' and after.event = 's' then 0
when main.event <> 's' and prior.event = 's' then 0
else 1 end as [flag],
prior.rno as [r-1], prior.id as [prior id], prior.event as [prior event],
after.rno as [r 1], after.id as [after id], after.event as [after event]
from cte as main
left join cte as prior on main.rno = prior.rno 1
left join cte as after on main.rno = after.rno - 1
order by main.rno;
小提琴演示 - 包含一些帶有間隙的額外行來說明。目前尚不清楚哪種邏輯最適合選擇前/后行，因此我使用了“時間”列。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/504681.html