Major Description
Spark SQL support some window function like NTH_VALUE
If we specify window frame like
UNBOUNDED PRECEDING AND CURRENT ROW
or
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
We can elimate some calculations.
For example: if we execute the SQL show below:
SELECT NTH_VALUE(col,
2) OVER(ORDER BY rank UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM tab;
The output for row number greater than 1, return the fixed value. otherwise, return null. So we just calculate the value once and notice whether the row number less than 2.
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is simpler.
