System and method for computing running and moving sequence functions in a database system

A method of executing a database query, comprising:

• determining whether the query includes any of a predefined set of running and moving sequence functions, and when that determination is positive, performing steps of:
• converting each running and moving sequence function in the query into a predefined normalized form suitable for compilation, the normalized form comprising an ordered set of one or more executable statements, at least one of the set of executable statements including an Offset sequence function, Offset(argument, index), wherein the argument of the Offset sequence function is a specified function of information associated with a row of a table, the row having a position that is index rows before a current row referenced by a cursor for the table;
• accessing rows of a table, and while accessing the rows of the table storing for each accessed row of the table one or more auxiliary fields in a buffer that is separate from the table, each of the auxiliary fields containing information that may be accessed during execution of the Offset sequence function while a cursor for the table is pointing to a subsequent row;
• and
• while accessing the rows of the table, executing the set of executable statements with respect to a current row of the table, including evaluating each Offset sequence function included in the set of executable statements;
• the step of evaluating each Offset sequence function for a current row of the table including reading information for a previously accessed row of the table by reading one or more of the auxiliary fields stored for the previously access row in the separate buffer.

A method of compiling a database query, comprising:

• determining whether the query includes any of a predefined set of running and moving sequence functions, and when that determination is positive, performing steps of:
• converting the query into a normalized form, including converting each running and moving sequence function in the query into a predefined normalized form suitable for compilation, the normalized form comprising an ordered set of one or more executable statements, at least one of the set of executable statements including an Offset sequence function, Offset(argument, index), wherein the argument of the Offset sequence function is a specified function of information associated with a row of a table, the row having a position that is index rows before a current row referenced by a cursor for the table;
• compiling the ordered set of executable statements in the normalized query, including compiling each Offset sequence function in the normalized query, including:
• parsing the argument of the Offset sequence function to determine a set of auxiliary fields to be stored for each row of a table to be accessed, each auxiliary field of each row containing information that may be accessed during execution of the Offset sequence function while the cursor for that table is pointing to a subsequent row;
• and
• converting the Offset sequence function into a compiled set of instructions, including instructions for storing and reading the auxiliary fields to and from a buffer that is separate from the table.