postgresql – 窗口函数LAG可以引用正在计算值的列吗?
我需要根据当前记录的其他一些列和前一条记录的X值(使用一些分区和顺序)计算某些列X的值.基本上我需要在表单中实现查询
SELECT <some fields>,<some expression using LAG(X) OVER(PARTITION BY ... ORDER BY ...) AS X FROM <table> 这是不可能的,因为只有现有的列可以在窗口函数中使用,所以我正在寻找如何克服这一点. 这是一个例子.我有一张活动表.每个事件都有type和time_stamp. create table event (id serial,type integer,time_stamp integer); 我不想找到“重复”事件.副本我的意思是以下.让我们通过time_stamp升序来为给定类型的所有事件排序.然后 >第一个事件不重复 对于这个数据 insert into event (type,time_stamp) values (1,1),(1,2),(2,3),10),15),21),13),40); 和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 我可以根据前一个非重复事件的当前time_stamp和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. 所以问题:我可以重写这个查询以达到我需要的效果吗?
递归方法的替代方法是自定义聚合.掌握编写自己的聚合技术后,创建转换和最终函数既简单又合乎逻辑.
状态转换功能: create or replace function is_duplicate(st int[],time_stamp int,timeframe int) returns int[] language plpgsql as $$ begin if st is null or st[1] + timeframe <= time_stamp then st[1] := time_stamp; end if; st[2] := time_stamp; return st; end $$; 最终功能: create or replace function is_duplicate_final(st int[]) returns boolean language sql as $$ select st[1] <> st[2]; $$; 骨料: create aggregate is_duplicate_agg(time_stamp int,timeframe int) ( sfunc = is_duplicate,stype = int[],finalfunc = is_duplicate_final ); 查询: select *,is_duplicate_agg(time_stamp,10) over w from event window w as (partition by type order by time_stamp asc) order by type,time_stamp; id | type | time_stamp | is_duplicate_agg ----+------+------------+------------------ 1 | 1 | 1 | f 2 | 1 | 2 | t 4 | 1 | 3 | t 5 | 1 | 10 | t 7 | 1 | 15 | f 8 | 1 | 21 | t 10 | 1 | 40 | f 3 | 2 | 2 | f 6 | 2 | 10 | t 9 | 2 | 13 | f (10 rows) 请阅读文档:37.10. User-defined Aggregates和CREATE AGGREGATE. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |