Menu
▾
▴
[Firebird-checkins] CVS: fbtcs/GTCS/tests FB_SQL_WINDOW_FUNC_04.output, 1.1, 1.2 FB_SQL_WINDOW_FUNC_04.script, 1.1, 1.2
|
From: Adriano d. S. F. <asf...@us...> - 2010-03-07 20:22:04
|
Update of /cvsroot/firebird/fbtcs/GTCS/tests In directory sfp-cvsdas-3.v30.ch3.sourceforge.com:/tmp/cvs-serv29613/GTCS/tests Modified Files: FB_SQL_WINDOW_FUNC_04.output FB_SQL_WINDOW_FUNC_04.script Log Message: Update tests Index: FB_SQL_WINDOW_FUNC_04.output =================================================================== RCS file: /cvsroot/firebird/fbtcs/GTCS/tests/FB_SQL_WINDOW_FUNC_04.output,v retrieving revision 1.1 retrieving revision 1.2 diff -b -U3 -r1.1 -r1.2 --- FB_SQL_WINDOW_FUNC_04.output 21 Feb 2010 15:04:14 -0000 1.1 +++ FB_SQL_WINDOW_FUNC_04.output 7 Mar 2010 20:21:53 -0000 1.2 @@ -7,28 +7,31 @@ lag(val) over (order by val nulls first, id), lead(val) over (order by val nulls first, id), lag(val, 2) over (order by val nulls first, id), - lead(val, 3) over (order by val nulls first, id) + lead(val, 3) over (order by val nulls first, id), + lag(val, 1, -val) over (order by val nulls first, id), + lag(val, 2, -val) over (order by val nulls first, id), + lead(val, 3, -1.00) over (order by val nulls first, id) from entries e order by id; - ID PERSON DAT VAL LAG LEAD LAG LEAD -============ ============ =========== ===================== ===================== ===================== ===================== ===================== - 1 1 2010-01-03 2.30 <null> 3.40 <null> 4.30 - 2 2 2010-01-04 4.30 3.40 6.30 3.40 6.40 - 3 3 2010-01-05 6.30 4.30 6.40 3.40 8.30 - 4 4 2010-01-06 8.30 6.40 9.40 6.40 10.30 - 5 5 2010-01-07 10.30 9.40 12.40 9.40 15.40 - 6 1 2010-02-02 3.40 2.30 3.40 <null> 6.30 - 7 2 2010-02-03 6.40 6.30 6.40 4.30 9.40 - 8 3 2010-02-04 9.40 8.30 9.40 6.40 12.40 - 9 4 2010-02-05 12.40 10.30 12.40 9.40 15.40 - 10 5 2010-02-06 15.40 12.40 15.40 12.40 <null> - 11 1 2010-03-02 3.40 3.40 4.30 2.30 6.40 - 12 2 2010-03-03 6.40 6.40 8.30 6.30 9.40 - 13 3 2010-03-04 9.40 9.40 10.30 8.30 12.40 - 14 4 2010-03-05 12.40 12.40 15.40 10.30 <null> - 15 5 2010-03-06 15.40 15.40 <null> 12.40 <null> - 16 1 <null> <null> <null> 2.30 <null> 3.40 + ID PERSON DAT VAL LAG LEAD LAG LEAD LAG LAG LEAD +============ ============ =========== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== + 1 1 2010-01-03 2.30 <null> 3.40 <null> 4.30 <null> -2.30 4.30 + 2 2 2010-01-04 4.30 3.40 6.30 3.40 6.40 3.40 3.40 6.40 + 3 3 2010-01-05 6.30 4.30 6.40 3.40 8.30 4.30 3.40 8.30 + 4 4 2010-01-06 8.30 6.40 9.40 6.40 10.30 6.40 6.40 10.30 + 5 5 2010-01-07 10.30 9.40 12.40 9.40 15.40 9.40 9.40 15.40 + 6 1 2010-02-02 3.40 2.30 3.40 <null> 6.30 2.30 <null> 6.30 + 7 2 2010-02-03 6.40 6.30 6.40 4.30 9.40 6.30 4.30 9.40 + 8 3 2010-02-04 9.40 8.30 9.40 6.40 12.40 8.30 6.40 12.40 + 9 4 2010-02-05 12.40 10.30 12.40 9.40 15.40 10.30 9.40 15.40 + 10 5 2010-02-06 15.40 12.40 15.40 12.40 <null> 12.40 12.40 -1.00 + 11 1 2010-03-02 3.40 3.40 4.30 2.30 6.40 3.40 2.30 6.40 + 12 2 2010-03-03 6.40 6.40 8.30 6.30 9.40 6.40 6.30 9.40 + 13 3 2010-03-04 9.40 9.40 10.30 8.30 12.40 9.40 8.30 12.40 + 14 4 2010-03-05 12.40 12.40 15.40 10.30 <null> 12.40 10.30 -1.00 + 15 5 2010-03-06 15.40 15.40 <null> 12.40 <null> 15.40 12.40 -1.00 + 16 1 <null> <null> <null> 2.30 <null> 3.40 <null> <null> 3.40 select @@ -37,18 +40,20 @@ lag(sum(val)) over (order by person), lead(sum(val)) over (order by person), lag(sum(val), 2) over (order by person), - lead(sum(val), 3) over (order by person) + lead(sum(val), 3) over (order by person), + lag(sum(val), 2, -sum(val)) over (order by person), + lead(sum(val), 3, -1.00) over (order by person) from entries group by person order by person; - PERSON SUM LAG LEAD LAG LEAD -============ ===================== ===================== ===================== ===================== ===================== - 1 9.10 <null> 17.10 <null> 33.10 - 2 17.10 9.10 25.10 <null> 41.10 - 3 25.10 17.10 33.10 9.10 <null> - 4 33.10 25.10 41.10 17.10 <null> - 5 41.10 33.10 <null> 25.10 <null> + PERSON SUM LAG LEAD LAG LEAD LAG LEAD +============ ===================== ===================== ===================== ===================== ===================== ===================== ===================== + 1 9.10 <null> 17.10 <null> 33.10 -9.10 33.10 + 2 17.10 9.10 25.10 <null> 41.10 -17.10 41.10 + 3 25.10 17.10 33.10 9.10 <null> 9.10 -1.00 + 4 33.10 25.10 41.10 17.10 <null> 17.10 -1.00 + 5 41.10 33.10 <null> 25.10 <null> 25.10 -1.00 --- Index: FB_SQL_WINDOW_FUNC_04.script =================================================================== RCS file: /cvsroot/firebird/fbtcs/GTCS/tests/FB_SQL_WINDOW_FUNC_04.script,v retrieving revision 1.1 retrieving revision 1.2 diff -b -U3 -r1.1 -r1.2 --- FB_SQL_WINDOW_FUNC_04.script 21 Feb 2010 15:04:14 -0000 1.1 +++ FB_SQL_WINDOW_FUNC_04.script 7 Mar 2010 20:21:55 -0000 1.2 @@ -8,7 +8,10 @@ lag(val) over (order by val nulls first, id), lead(val) over (order by val nulls first, id), lag(val, 2) over (order by val nulls first, id), - lead(val, 3) over (order by val nulls first, id) + lead(val, 3) over (order by val nulls first, id), + lag(val, 1, -val) over (order by val nulls first, id), + lag(val, 2, -val) over (order by val nulls first, id), + lead(val, 3, -1.00) over (order by val nulls first, id) from entries e order by id; @@ -18,7 +21,9 @@ lag(sum(val)) over (order by person), lead(sum(val)) over (order by person), lag(sum(val), 2) over (order by person), - lead(sum(val), 3) over (order by person) + lead(sum(val), 3) over (order by person), + lag(sum(val), 2, -sum(val)) over (order by person), + lead(sum(val), 3, -1.00) over (order by person) from entries group by person order by person; |