content_image/797249.jpg
<img src='content_image/797249.jpg'>

WINDOW w AS (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING); 

Each function uses the rows in the current frame, which, per the window definition shown, extends from the first partition row to the current row. For the NTH_VALUE()  calls, the current frame does not always include the requested row; in such cases, the return value is NULL . 

In the absence of a frame clause, the default frame depends on whether an ORDER BY  clause is present: 

•With ORDER BY : The default frame includes rows from the partition start through the current row, including all peers of the current row (rows equal to the current row according to the ORDER BY  clause). The default is equivalent to this frame specification: 

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 

•Without ORDER BY : The default frame includes all partition rows (because, without ORDER BY , all partition rows are peers). The default is equivalent to this frame specification: 

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 

Because the default frame differs depending on presence or absence of ORDER BY , adding ORDER BY to a query to get deterministic results may change the results. (For example, the values produced by SUM()  might change.) To obtain the same results but ordered per ORDER BY , provide an explicit frame specification to be used regardless of whether ORDER BY  is present. 

The meaning of a frame specification can be nonobvious when the current row value is NULL . Assuming that to be the case, these examples illustrate how various frame specifications apply: 

• ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING 

The frame starts at NULL  and stops at NULL , thus includes only rows with value NULL . 

• ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING 

The frame starts at NULL  and stops at the end of the partition. Because an ASC  sort puts NULL  values first, the frame is the entire partition. 

• ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING 

The frame starts at NULL  and stops at the end of the partition. Because a DESC  sort puts NULL  values last, the frame is only the NULL  values. 

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING 

The frame starts at NULL  and stops at the end of the partition. Because an ASC  sort puts NULL  values first, the frame is the entire partition. 

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING 

