create table tes( ID smallint, SortKey smallint, Val smallint); insert into tes values (1,1, 1), (1,2, 2), (1,3, 4), (1,4, 8), (2,1, 16), (2,2, 32), (2,3, 64), (2,4, 128), (3,1, 1), (3,3, 1), (3,5, 2), (4,1,null), (4,2, 123), (4,3,null);
select ID,SortKey,Val, count(distinct Val) over(partition by ID) from tes; NG
select ID,SortKey,Val, count(distinct Val) over(partition by ID order by SortKey) from tes; NG
select ID,SortKey,Val, sum(Val) over(partition by ID) from tes; OK
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey rows 2 preceding) from tes; NG
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey rows between 2 preceding and current row) from tes; NG
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey rows 2 following) from tes; NG
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey range 2 preceding) from tes; NG
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey range between 2 preceding and current row) from tes; NG
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey range 2 following) from tes; NG
select ID,SortKey,Val, Lag (Val) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val, Lead(Val) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val, Lag (Val,2) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val, Lead(Val,2) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val,Lag(Val,2,999::smallint) over(order by SortKey) from tes; OK
select ID,SortKey,Val,Lead(Val,2,999::smallint) over(order by SortKey) from tes; OK
select ID,SortKey,Val,First_Value(Val) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val,Last_Value (Val) over(partition by ID order by SortKey) from tes; OK
select ID,SortKey,Val, First_Value(Val ignore nulls) over(partition by ID order by SortKey) from tes; NG
select ID,SortKey,Val, Last_Value (Val ignore nulls) over(partition by ID order by SortKey) from tes; NG
select ID,SortKey,Val, Last_Value (Val) over(partition by ID order by SortKey Rows between Unbounded Preceding and Unbounded Following) from tes; OK
select ID,sum(Val), max(sum(Val)) over() as maxSum from tes group by ID; OK
select ID,SortKey,Val from tes order by sum(Val) over(partition by ID),SortKey; OK
select ID,SortKey,Val from tes order by case max(ID) over() when 1 then Row_Number() over(order by ID) else Row_Number() over(order by ID desc) end; OK
select day1,Val,sum(Val) over(order by tim range interval '2 hours' preceding) as sumVal from (select timestamp '2009-05-21 12:00' as tim, 1 as Val union all select timestamp '2009-05-21 13:00' as tim, 2 as Val union all select timestamp '2009-05-21 14:00' as tim, 4 as Val union all select timestamp '2009-05-21 15:00' as tim, 8 as Val union all select timestamp '2009-05-21 16:00' as tim,16 as Val) d; NG
select Val, nth_value(Val,1) over(order by Val Rows between Unbounded Preceding and Unbounded Following) as nth1, nth_value(Val,2) over(order by Val Rows between Unbounded Preceding and Unbounded Following) as nth2, nth_value(Val,3) over(order by Val Rows between Unbounded Preceding and Unbounded Following) as nth3, nth_value(Val,4) over(order by Val Rows between Unbounded Preceding and Unbounded Following) as nth4 from (select 1 as Val union all select 1 as Val union all select 2 as Val union all select 2 as Val union all select 2 as Val) d; Val | nth1 | nth2 | nth3 | nth4 ----+------+------+------+------ 1 | 1 | 1 | 2 | 2 1 | 1 | 1 | 2 | 2 2 | 1 | 1 | 2 | 2 2 | 1 | 1 | 2 | 2 2 | 1 | 1 | 2 | 2
select ID,SortKey,Val,Row_Number() over() as rn from tes; OK
select ID,SortKey,Val,Row_Number() over(partition by ID) as rn from tes; OK
select ID,SortKey,Val,median(Val) over(partition by ID) as medi from tes; NG
select ID,SortKey,Val, max(Val) Keep(Dense_Rank Last order by ID) over() as m from tes; NG
update tes set Val = max(Val) over(partition by ID); NG
PostgreSQL 文書 UPDATE update tes set Val = b.rn from (select ID,SortKey, Row_Number() over(partition by ID order by SortKey) as rn from tes) b where tes.ID=b.ID and tes.SortKey=b.SortKey; OK
update tes set Val = (select rn from (select ID,SortKey, Row_Number() over(partition by ID order by SortKey) as rn from tes) b where tes.ID=b.ID and tes.SortKey=b.SortKey) where exists(select 1 from tes b where tes.ID=b.ID and tes.SortKey=b.SortKey); OK
with w(ID,sortKey,Val) as( select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 1,4,'d' union all select 1,5,'e' union all select 2,1,'f' union all select 2,2,'g' union all select 2,3,'h' union all select 3,1,'a' union all select 3,2,'a' union all select 3,2,'a') select ID, replace( replace( replace(xmlagg(xmlforest(Val)) over(partition by ID order by sortKey)::text ,'</val><val>',','), '</val>',''), '<val>' ,'') as strAgg from w; ID | strAgg ----+----------- 1 | a 1 | a,b 1 | a,b,c 1 | a,b,c,d 1 | a,b,c,d,e 2 | f 2 | f,g 2 | f,g,h 3 | a 3 | a,a,a 3 | a,a,a
with w(sortKey,Val) as( select 1,'a' union select 2,'b' union select 3,'c' union select 4,'d' union select 5,'e') select sortKey,Val, array_to_string(array_agg(Val) over(),',') as strAgg1, array_to_string(array_agg(Val) over(order by sortKey),',') as strAgg2 from w; sortKey | Val | strAgg1 | strAgg2 --------+-----+-----------+---------- 1 | a | e,a,b,c,d | a 2 | b | e,a,b,c,d | a,b 3 | c | e,a,b,c,d | a,b,c 4 | d | e,a,b,c,d | a,b,c,d 5 | e | e,a,b,c,d | a,b,c,d,e