diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 4c5af4b..89d28b2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -12266,6 +12266,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lag(value any [, offset integer [, default any ]]) + [respect nulls]|[ignore nulls] @@ -12280,7 +12281,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; default are evaluated with respect to the current row. If omitted, offset defaults to 1 and - default to null + default to null. If + IGNORE NULLS is specified and a previous evalution in the + current window has returned a non-null value then that value will be + returned instead. @@ -12293,6 +12297,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lead(value any [, offset integer [, default any ]]) + [respect nulls]|[ignore nulls] @@ -12307,7 +12312,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; default are evaluated with respect to the current row. If omitted, offset defaults to 1 and - default to null + IGNORE NULLS is specified and a previous evalution in the + current window has returned a non-null value then that value will be + returned instead. @@ -12401,11 +12408,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; The SQL standard defines a RESPECT NULLS or - IGNORE NULLS option for lead, lag, - first_value, last_value, and - nth_value. This is not implemented in - PostgreSQL: the behavior is always the - same as the standard's default, namely RESPECT NULLS. + IGNORE NULLS option for first_value, + last_value, and nth_value. This is not + implemented in PostgreSQL: the behavior is + always the same as the standard's default, namely RESPECT NULLS. Likewise, the standard's FROM FIRST or FROM LAST option for nth_value is not implemented: only the default FROM FIRST behavior is supported. (You can achieve diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index d9f0e79..e1a1020 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -2000,6 +2000,16 @@ WinGetCurrentPosition(WindowObject winobj) Assert(WindowObjectIsValid(winobj)); return winobj->winstate->currentpos; } +/* + * WinGetFrameOptions + * Returns the frame option flags + */ +int +WinGetFrameOptions(WindowObject winobj) +{ + Assert(WindowObjectIsValid(winobj)); + return winobj->winstate->frameOptions; +} /* * WinGetPartitionRowCount diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5094226..aae35d8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -288,6 +288,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type TriggerEvents TriggerOneEvent %type TriggerFuncArg %type TriggerWhen +%type opt_ignore_nulls %type event_trigger_when_list event_trigger_value_list %type event_trigger_when_item @@ -401,7 +402,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type columnDef columnOptions %type def_elem reloption_elem old_aggr_elem %type def_arg columnElem where_clause where_or_current_clause - a_expr b_expr c_expr func_expr AexprConst indirection_el + a_expr b_expr c_expr AexprConst indirection_el columnref in_expr having_clause func_table array_expr ExclusionWhereClause %type ExclusionConstraintList ExclusionConstraintElem @@ -481,6 +482,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type document_or_content %type xml_whitespace_option +%type func_application func_expr_common_subexpr +%type func_expr func_expr_windowless %type common_table_expr %type with_clause opt_with_clause %type cte_list @@ -543,7 +546,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); HANDLER HAVING HEADER_P HOLD HOUR_P - IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P + IDENTITY_P IF_P IGNORE ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION @@ -573,7 +576,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK + RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -6132,7 +6135,7 @@ index_elem: ColId opt_collate opt_class opt_asc_desc opt_nulls_order $$->ordering = $4; $$->nulls_ordering = $5; } - | func_expr opt_collate opt_class opt_asc_desc opt_nulls_order + | func_expr_windowless opt_collate opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = NULL; @@ -9894,11 +9897,9 @@ relation_expr_opt_alias: relation_expr %prec UMINUS } ; - -func_table: func_expr { $$ = $1; } +func_table: func_expr_windowless { $$ = $1; } ; - where_clause: WHERE a_expr { $$ = $2; } | /*EMPTY*/ { $$ = NULL; } @@ -11079,15 +11080,7 @@ c_expr: columnref { $$ = $1; } } ; -/* - * func_expr is split out from c_expr just so that we have a classification - * for "everything that is a function call or looks like one". This isn't - * very important, but it saves us having to document which variants are - * legal in the backwards-compatible functional-index syntax for CREATE INDEX. - * (Note that many of the special SQL functions wouldn't actually make any - * sense as functional index entries, but we ignore that consideration here.) - */ -func_expr: func_name '(' ')' over_clause +func_application: func_name '(' ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11096,11 +11089,11 @@ func_expr: func_name '(' ')' over_clause n->agg_star = FALSE; n->agg_distinct = FALSE; n->func_variadic = FALSE; - n->over = $4; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' func_arg_list ')' over_clause + | func_name '(' func_arg_list ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11109,11 +11102,11 @@ func_expr: func_name '(' ')' over_clause n->agg_star = FALSE; n->agg_distinct = FALSE; n->func_variadic = FALSE; - n->over = $5; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' VARIADIC func_arg_expr ')' over_clause + | func_name '(' VARIADIC func_arg_expr ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11122,11 +11115,11 @@ func_expr: func_name '(' ')' over_clause n->agg_star = FALSE; n->agg_distinct = FALSE; n->func_variadic = TRUE; - n->over = $6; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' func_arg_list ',' VARIADIC func_arg_expr ')' over_clause + | func_name '(' func_arg_list ',' VARIADIC func_arg_expr ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11135,11 +11128,11 @@ func_expr: func_name '(' ')' over_clause n->agg_star = FALSE; n->agg_distinct = FALSE; n->func_variadic = TRUE; - n->over = $8; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' func_arg_list sort_clause ')' over_clause + | func_name '(' func_arg_list sort_clause ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11148,11 +11141,11 @@ func_expr: func_name '(' ')' over_clause n->agg_star = FALSE; n->agg_distinct = FALSE; n->func_variadic = FALSE; - n->over = $6; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' ALL func_arg_list opt_sort_clause ')' over_clause + | func_name '(' ALL func_arg_list opt_sort_clause ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11165,11 +11158,11 @@ func_expr: func_name '(' ')' over_clause * for that in FuncCall at the moment. */ n->func_variadic = FALSE; - n->over = $7; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' DISTINCT func_arg_list opt_sort_clause ')' over_clause + | func_name '(' DISTINCT func_arg_list opt_sort_clause ')' { FuncCall *n = makeNode(FuncCall); n->funcname = $1; @@ -11178,11 +11171,11 @@ func_expr: func_name '(' ')' over_clause n->agg_star = FALSE; n->agg_distinct = TRUE; n->func_variadic = FALSE; - n->over = $7; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | func_name '(' '*' ')' over_clause + | func_name '(' '*' ')' { /* * We consider AGGREGATE(*) to invoke a parameterless @@ -11201,11 +11194,48 @@ func_expr: func_name '(' ')' over_clause n->agg_star = TRUE; n->agg_distinct = FALSE; n->func_variadic = FALSE; - n->over = $5; + n->over = NULL; n->location = @1; $$ = (Node *)n; } - | COLLATION FOR '(' a_expr ')' + ; + + +/* + * func_expr and its cousin func_expr_windowless is split out from c_expr just + * so that we have classifications for "everything that is a function call or + * looks like one". This isn't very important, but it saves us having to document + * which variants are legal in the backwards-compatible functional-index syntax + * for CREATE INDEX. + * (Note that many of the special SQL functions wouldn't actually make any + * sense as functional index entries, but we ignore that consideration here.) + */ +func_expr: func_application over_clause + { + FuncCall *n = (FuncCall*)$1; + n->over = $2; + $$ = (Node*)n; + } + | func_expr_common_subexpr + { $$ = $1; } + ; + +/* + * As func_expr but does not accept WINDOW functions directly (they + * can still be contained in arguments for functions etc.) + * Use this when window expressions are not allowed, so to decomplicate + * the grammar. (e.g. in CREATE INDEX) + */ +func_expr_windowless: + func_application { $$ = $1; } + | func_expr_common_subexpr { $$ = $1; } + ; + +/* + * Special expression + */ +func_expr_common_subexpr: + COLLATION FOR '(' a_expr ')' { FuncCall *n = makeNode(FuncCall); n->funcname = SystemFuncName("pg_collation_for"); @@ -11752,16 +11782,25 @@ window_definition: } ; -over_clause: OVER window_specification - { $$ = $2; } - | OVER ColId +opt_ignore_nulls: + IGNORE NULLS_P { $$ = FRAMEOPTION_IGNORE_NULLS; } + | RESPECT NULLS_P { $$ = 0; } + | /* EMPTY */ { $$ = 0; } + ; + +over_clause: opt_ignore_nulls OVER window_specification + { + $3->frameOptions |= $1; + $$ = $3; + } + | opt_ignore_nulls OVER ColId { WindowDef *n = makeNode(WindowDef); - n->name = $2; + n->name = $3; n->refname = NULL; n->partitionClause = NIL; n->orderClause = NIL; - n->frameOptions = FRAMEOPTION_DEFAULTS; + n->frameOptions = FRAMEOPTION_DEFAULTS | $1; n->startOffset = NULL; n->endOffset = NULL; n->location = @2; @@ -12740,6 +12779,7 @@ unreserved_keyword: | HOUR_P | IDENTITY_P | IF_P + | IGNORE | IMMEDIATE | IMMUTABLE | IMPLICIT_P @@ -12827,6 +12867,7 @@ unreserved_keyword: | REPLACE | REPLICA | RESET + | RESPECT | RESTART | RESTRICT | RETURNS diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index b7c42d3..8985149 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -25,6 +25,15 @@ typedef struct rank_context } rank_context; /* + * structure for IGNORE NULLS / RESPECT NULLS semantics + */ +typedef struct leadlag_context +{ + int64 last; /* last non-null result, initially 0 */ + bool seen_one; /* true iff we can output the row in "last" now */ +} leadlag_context; + +/* * ntile process information */ typedef struct @@ -292,6 +301,15 @@ leadlag_common(FunctionCallInfo fcinfo, Datum result; bool isnull; bool isout; + bool ignore_nulls; + leadlag_context* context; + + /* + * We want to set the markpos (the earliest tuple we can access) as + * aggressively as possible to save memory, but we can't move the mark + * beyond the last non-null tuple! + */ + ignore_nulls = (WinGetFrameOptions(winobj) & FRAMEOPTION_IGNORE_NULLS) != 0; if (withoffset) { @@ -305,11 +323,15 @@ leadlag_common(FunctionCallInfo fcinfo, offset = 1; const_offset = true; } + if(!forward) + { + offset = -offset; + } result = WinGetFuncArgInPartition(winobj, 0, - (forward ? offset : -offset), + offset, WINDOW_SEEK_CURRENT, - const_offset, + const_offset && !ignore_nulls, &isnull, &isout); if (isout) @@ -322,6 +344,39 @@ leadlag_common(FunctionCallInfo fcinfo, result = WinGetFuncArgCurrent(winobj, 2, &isnull); } + /* + * If the row's out of the partition we don't want to propagate the + * last non-null value if we're RESPECTing NULLS - we'll just leave + * the default value (if there was one). + */ + if (ignore_nulls && !isout) + { + /* + * We'll keep the last non-null value we've seen in our per-partition chunk + * of memory, so it gets cleaned up for us. + */ + context = (leadlag_context *) + WinGetPartitionLocalMemory(winobj, sizeof(leadlag_context)); + if (isnull) + { + if (context->seen_one) + { + /* restore the datum at the stashed index */ + result = WinGetFuncArgInPartition(winobj, 0, + context->last, + WINDOW_SEEK_HEAD, + const_offset, /* drag mark up after us */ + &isnull, &isout); + } + } + else + { + /* work out which tuple we just loaded */ + context->last = WinGetCurrentPosition(winobj) + offset; + context->seen_one = true; + } + } + if (isnull) PG_RETURN_NULL(); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 6723647..71b44d5 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -435,6 +435,7 @@ typedef struct WindowDef #define FRAMEOPTION_END_VALUE_PRECEDING 0x00800 /* end is V. P. */ #define FRAMEOPTION_START_VALUE_FOLLOWING 0x01000 /* start is V. F. */ #define FRAMEOPTION_END_VALUE_FOLLOWING 0x02000 /* end is V. F. */ +#define FRAMEOPTION_IGNORE_NULLS 0x04000 #define FRAMEOPTION_START_VALUE \ (FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING) diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 68a13b7..2acf073 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -179,6 +179,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD) PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD) PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD) PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD) +PG_KEYWORD("ignore", IGNORE, UNRESERVED_KEYWORD) PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD) PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD) @@ -312,6 +313,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD) PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD) PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD) +PG_KEYWORD("respect", RESPECT, UNRESERVED_KEYWORD) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD) diff --git a/src/include/windowapi.h b/src/include/windowapi.h index 5bbf1fa..81f5ba0 100644 --- a/src/include/windowapi.h +++ b/src/include/windowapi.h @@ -46,6 +46,8 @@ extern void *WinGetPartitionLocalMemory(WindowObject winobj, Size sz); extern int64 WinGetCurrentPosition(WindowObject winobj); extern int64 WinGetPartitionRowCount(WindowObject winobj); +extern int WinGetFrameOptions(WindowObject winobj); + extern void WinSetMarkPosition(WindowObject winobj, int64 markpos); extern bool WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2); diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 752c7b4..c6f72f8 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -5,19 +5,21 @@ CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, salary int, - enroll_date date + enroll_date date, + term_date date, + respect text ); INSERT INTO empsalary VALUES -('develop', 10, 5200, '2007-08-01'), -('sales', 1, 5000, '2006-10-01'), -('personnel', 5, 3500, '2007-12-10'), -('sales', 4, 4800, '2007-08-08'), -('personnel', 2, 3900, '2006-12-23'), -('develop', 7, 4200, '2008-01-01'), -('develop', 9, 4500, '2008-01-01'), -('sales', 3, 4800, '2007-08-01'), -('develop', 8, 6000, '2006-10-01'), -('develop', 11, 5200, '2007-08-15'); +('develop', 10, 5200, '2007-08-01', null, null), +('sales', 1, 5000, '2006-10-01', null, 'frog'), +('personnel', 5, 3500, '2007-12-10', null, null), +('sales', 4, 4800, '2007-08-08', '2010-09-22', 'chicken'), +('personnel', 2, 3900, '2006-12-23', null, null), +('develop', 7, 4200, '2008-01-01', null, null), +('develop', 9, 4500, '2008-01-01', null, 'gorilla'), +('sales', 3, 4800, '2007-08-01', '2009-03-05', null), +('develop', 8, 6000, '2006-10-01', '2009-11-17', 'tiger'), +('develop', 11, 5200, '2007-08-15', null, null); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; depname | empno | salary | sum -----------+-------+--------+------- @@ -989,9 +991,9 @@ ERROR: window functions are not allowed in GROUP BY LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO... ^ SELECT * FROM rank() OVER (ORDER BY random()); -ERROR: window functions are not allowed in functions in FROM +ERROR: syntax error at or near "OVER" LINE 1: SELECT * FROM rank() OVER (ORDER BY random()); - ^ + ^ DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; ERROR: window functions are not allowed in WHERE LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())... @@ -1020,5 +1022,135 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; ERROR: argument of ntile must be greater than zero SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; ERROR: argument of nth_value must be greater than zero +-- test null behaviour: (1) lags +SELECT term_date, lag(term_date) OVER (ORDER BY empno) FROM empsalary; + term_date | lag +------------+------------ + | + | + 03-05-2009 | + 09-22-2010 | 03-05-2009 + | 09-22-2010 + | + 11-17-2009 | + | 11-17-2009 + | + | +(10 rows) + +SELECT term_date, lag(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + term_date | lag +------------+------------ + | + | + 03-05-2009 | + 09-22-2010 | 03-05-2009 + | 09-22-2010 + | + 11-17-2009 | + | 11-17-2009 + | + | +(10 rows) + +-- a numeric (date) column +SELECT term_date, lag(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + term_date | lag +------------+------------ + | + | + 03-05-2009 | + 09-22-2010 | 03-05-2009 + | 09-22-2010 + | 09-22-2010 + 11-17-2009 | 09-22-2010 + | 11-17-2009 + | 11-17-2009 + | 11-17-2009 +(10 rows) + +-- a text column +SELECT respect, lag(respect) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + respect | lag +---------+--------- + frog | + | frog + | frog + chicken | frog + | chicken + | chicken + tiger | chicken + gorilla | tiger + | gorilla + | gorilla +(10 rows) + +-- (2) leads +SELECT term_date, lead(term_date) OVER (ORDER BY empno) FROM empsalary; + term_date | lead +------------+------------ + | + | 03-05-2009 + 03-05-2009 | 09-22-2010 + 09-22-2010 | + | + | 11-17-2009 + 11-17-2009 | + | + | + | +(10 rows) + +SELECT term_date, lead(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + term_date | lead +------------+------------ + | + | 03-05-2009 + 03-05-2009 | 09-22-2010 + 09-22-2010 | + | + | 11-17-2009 + 11-17-2009 | + | + | + | +(10 rows) + +SELECT term_date, lead(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + term_date | lead +------------+------------ + | + | 03-05-2009 + 03-05-2009 | 09-22-2010 + 09-22-2010 | 09-22-2010 + | 09-22-2010 + | 11-17-2009 + 11-17-2009 | 11-17-2009 + | 11-17-2009 + | 11-17-2009 + | +(10 rows) + -- cleanup DROP TABLE empsalary; +-- some more test cases +CREATE TABLE test_table ( + id serial, + val integer); +INSERT INTO test_table (val) SELECT * FROM unnest(ARRAY[1,2,3,4,NULL, NULL, NULL, 5, 6, 7]); +SELECT val, lead(val, 2) IGNORE NULLS OVER (ORDER BY id) FROM test_table; + val | lead +-----+------ + 1 | 3 + 2 | 4 + 3 | 4 + 4 | 4 + | 4 + | 5 + | 6 + 5 | 7 + 6 | + 7 | +(10 rows) + +DROP TABLE test_table; diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 769be0f..6dba56e 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -6,20 +6,22 @@ CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, salary int, - enroll_date date + enroll_date date, + term_date date, + respect text ); INSERT INTO empsalary VALUES -('develop', 10, 5200, '2007-08-01'), -('sales', 1, 5000, '2006-10-01'), -('personnel', 5, 3500, '2007-12-10'), -('sales', 4, 4800, '2007-08-08'), -('personnel', 2, 3900, '2006-12-23'), -('develop', 7, 4200, '2008-01-01'), -('develop', 9, 4500, '2008-01-01'), -('sales', 3, 4800, '2007-08-01'), -('develop', 8, 6000, '2006-10-01'), -('develop', 11, 5200, '2007-08-15'); +('develop', 10, 5200, '2007-08-01', null, null), +('sales', 1, 5000, '2006-10-01', null, 'frog'), +('personnel', 5, 3500, '2007-12-10', null, null), +('sales', 4, 4800, '2007-08-08', '2010-09-22', 'chicken'), +('personnel', 2, 3900, '2006-12-23', null, null), +('develop', 7, 4200, '2008-01-01', null, null), +('develop', 9, 4500, '2008-01-01', null, 'gorilla'), +('sales', 3, 4800, '2007-08-01', '2009-03-05', null), +('develop', 8, 6000, '2006-10-01', '2009-11-17', 'tiger'), +('develop', 11, 5200, '2007-08-15', null, null); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; @@ -264,5 +266,35 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; +-- test null behaviour: (1) lags + +SELECT term_date, lag(term_date) OVER (ORDER BY empno) FROM empsalary; + +SELECT term_date, lag(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + +-- a numeric (date) column +SELECT term_date, lag(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + +-- a text column +SELECT respect, lag(respect) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + +-- (2) leads + +SELECT term_date, lead(term_date) OVER (ORDER BY empno) FROM empsalary; + +SELECT term_date, lead(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + +SELECT term_date, lead(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + -- cleanup DROP TABLE empsalary; + +-- some more test cases +CREATE TABLE test_table ( + id serial, + val integer); +INSERT INTO test_table (val) SELECT * FROM unnest(ARRAY[1,2,3,4,NULL, NULL, NULL, 5, 6, 7]); + +SELECT val, lead(val, 2) IGNORE NULLS OVER (ORDER BY id) FROM test_table; + +DROP TABLE test_table;