LAGApplies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Returns <value> evaluated at the row that’s <offset> rows before the current row within the partition.
1lag(<value> [, <offset> [, <default> ]]) OVER ( [ PARTITION BY <expression> [, ...] ] [ ORDER BY <expression> [, ...] ] )<value>: The expression to evaluate at the target row.<offset>: The number of rows before the current row. Default: 1.<default>: The value to return when the target row doesn’t exist, for example when the offset precedes the start of the partition. Must be of the same type as <value>. Default: NULL.Returns the same type as <value>.
Null handling:
<offset> positions before the current row, lag returns <default> instead of <value>.<default> is omitted, lag returns NULL when the target row doesn’t exist.<offset> and <default> are evaluated with respect to the current row, not the target row, so they can reference the current row’s own column values.lag depends on the row order defined by ORDER BY in the OVER clause and looks backward through that order. It operates over the whole partition rather than the window frame, so frame clauses (RANGE, ROWS) don’t affect its result.OVER clause and window frames, see Window Function Call Syntax. For more information about naming a window definition with a WINDOW clause, see SELECT.Compare each employee’s salary to the salary of the employee before them, ordered by salary within each department.
1SELECT depname, empno, salary, lag(salary) OVER (PARTITION BY depname ORDER BY salary DESC)
2FROM empsalary;For each row, lag returns the salary from the previous row in the ORDER BY sequence within the same depname partition. The first row of each partition has no preceding row, so lag returns NULL there.
Look back two rows instead of one, and substitute 0 when no such row exists.
1SELECT empno, salary, lag(salary, 2, 0) OVER (ORDER BY salary DESC)
2FROM empsalary;The first two rows in the ORDER BY sequence have no row two positions before them, so lag returns 0 for those rows instead of NULL.
j