我想检测此(示例)数据库中列值的变化
WITH events(id, row,event) AS (
VALUES
(1,1, 0 )
,(1,2, 0 )
,(1,3, 1 )
,(1,4, 0 )
,(1,5, 1 )
,(2,1, 0 )
,(2,2, 1 )
,(3,1, 0 )
,(3,2, 0 )
)
select * from events
我正在寻找的是新列"代码"的代码,该列在 1 之后切换到 1 Sde 事件列显示 1。 在相同的 id 中,代码保持 1。对于此示例,此新列将如下所示
WITH events2(id, row,event, code) AS (
VALUES
(1,1, 0, 0 )
,(1,2, 0, 0 )
,(1,3, 1, 0 )
,(1,4, 0, 1 ) -- notice the switch here
,(1,5, 1, 1 ) --
,(2,1, 0, 0 )
,(2,2, 1, 0 )
,(3,1, 0, 0 )
,(3,2, 0, 0 )
)
select * from events2
我有一种预感,答案将与这个问题的答案有关:PostgreSQL窗口功能:通过比较进行分区
不知何故,我自己无法弄清楚这一点..
彼得
合并标量子查询:
WITH events(id, zrow, zevent) AS (
VALUES
(1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
,(2,1, 0 ) ,(2,2, 1 )
,(3,1, 0 ) ,(3,2, 0 )
)
SELECT id, zrow, zevent
, COALESCE((SELECT 1 FROM events ex WHERE ex.id = ev.id AND ex.zrow < ev.zrow AND ex.zevent> 0),0) AS oevent
FROM events ev
;
或者,通过将布尔值 EXISTS() 类型转换为 INTEGER 来避免 COALESCE():
WITH events(id, zrow,event) AS (
VALUES
(1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
,(2,1, 0 ) ,(2,2, 1 )
,(3,1, 0 ) ,(3,2, 0 )
)
SELECT id, zrow, event
, EXISTS(SELECT 1 FROM events ex WHERE ex.id = ev.id AND ex.zrow < ev.zrow AND ex.event> 0)::integer AS oevent
FROM events ev
;
在同一组(帧)中查找先前记录的 MAX() 值:
WITH events(id, zrow,event) AS (
VALUES
(1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
,(2,1, 0 ) ,(2,2, 1 )
,(3,1, 0 ) ,(3,2, 0 )
)
, drag AS (
SELECT id, zrow, event, MAX(event)
OVER (PARTITION BY id
ORDER BY zrow
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS lagged
FROM events ev
)
SELECT id, zrow, event
, COALESCE(lagged,0) AS oevent
FROM drag dr
;
没有额外的 CTE 也是如此:
WITH events(id, zrow,event) AS (
VALUES
(1,1, 0 ) ,(1,2, 0 ) ,(1,3, 1 ) ,(1,4, 0 ) ,(1,5, 1 )
,(2,1, 0 ) ,(2,2, 1 )
,(3,1, 0 ) ,(3,2, 0 )
)
SELECT id, zrow, event, COALESCE(MAX(event) OVER (PARTITION BY id
ORDER BY zrow
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
),0) AS lagged
FROM events ev
;
执行自联接的另一种方法是使用递归查询。