我需要根据当前记录的其他一些列和前一条记录的 X 值(使用某些分区和顺序)计算某些列 X 的值。基本上我需要以形式实现查询
SELECT <some fields>,
<some expression using LAG(X) OVER(PARTITION BY ... ORDER BY ...) AS X
FROM <table>
这是不可能的,因为只有现有的列可以在窗口函数中使用,所以我正在寻找如何克服这个问题的方法。
这是一个例子。我有一张有活动的桌子。每个活动都有type
and time_stamp
.
create table event (id serial, type integer, time_stamp integer);
我不想找到“重复”事件(以跳过它们)。我所说的重复是指以下内容。让我们对给定的所有事件进行排序type
by time_stamp
上升。然后
- 第一个事件不是重复的
- 跟随非重复且在其后某个时间范围内的所有事件(即它们的
time_stamp
不大于然后time_stamp
先前的非重复加上一些常量TIMEFRAME
) 是重复的
- 下一个事件
time_stamp
比之前的非重复项大超过TIMEFRAME
不重复
- 等等
对于这个数据
insert into event (type, time_stamp)
values
(1, 1), (1, 2), (2, 2), (1,3), (1, 10), (2,10),
(1,15), (1, 21), (2,13),
(1, 40);
and TIMEFRAME=10
结果应该是
time_stamp | type | duplicate
-----------------------------
1 | 1 | false
2 | 1 | true
3 | 1 | true
10 | 1 | true
15 | 1 | false
21 | 1 | true
40 | 1 | false
2 | 2 | false
10 | 2 | true
13 | 2 | false
我可以计算出duplicate
基于当前的字段time_stamp
and time_stamp
之前的非重复事件如下:
WITH evt AS (
SELECT
time_stamp,
CASE WHEN
time_stamp - LAG(current_non_dupl_time_stamp) OVER w >= TIMEFRAME
THEN
time_stamp
ELSE
LAG(current_non_dupl_time_stamp) OVER w
END AS current_non_dupl_time_stamp
FROM event
WINDOW w AS (PARTITION BY type ORDER BY time_stamp ASC)
)
SELECT time_stamp, time_stamp != current_non_dupl_time_stamp AS duplicate
但这不起作用,因为计算出的字段不能被引用LAG
:
ERROR: column "current_non_dupl_time_stamp" does not exist.
那么问题来了:我可以重写这个查询来达到我需要的效果吗?