Skip to content

Instantly share code, notes, and snippets.

@YiLi225

YiLi225/lag.sql

Last active March 10, 2020 04:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save YiLi225/a92256adbb84b7a340733563a9919bb8 to your computer and use it in GitHub Desktop.
Save YiLi225/a92256adbb84b7a340733563a9919bb8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
lag.sql
--- 5) LAG() or LEAD() function
SELECT
DAT.ID_VAR,
DAT.SEQ_VAR,
DAT.NUM_VAR,
NUM_VAR - PREV_NUM AS NUM_DIFF
FROM
(
SELECT
T.*,
LAG(NUM_VAR, 1, 0) OVER (PARTITION BY ID_VAR ORDER BY SEQ_VAR) AS PREV_NUM
FROM
CURRENT_TABLE T
) DAT
ORDER BY ID_VAR, SEQ_VAR
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment