LAG and LEAD
1. LAG and LEAD
Get ready - we're about to use maybe the coolest window functions yet; `LAG` and `LEAD`.2. LAG
`LAG` allows for one value to be compared to another value in a previous record. `LAG` takes three arguments; the field in the previous record to retrieve, the number of records to lookback, and the default value if a record is not present, which is optional. Like with other ranking window functions, we can partition by some column, but we MUST order by a field to properly use `LAG`.3. LAG
In our upcoming example, we'd like the `past_cb` column for January third to be populated with the calories burned (or `cb`) from the previous workout, in this case, on January first.4. LAG
To do this, we'll pass the `calories_burned` field to `LAG`, followed by the number one to specify "looking back" one record. Since we order by the `workout_date`, the `past_cb` column will be populated with the calories burned from the previous workout. Here, we're partitioning by member ID.5. LAG
What if we'd like to find the difference in `calories_burned` between a current and a past workout? We can do that! Here, we use the minus sign to subtract the `calories_burned` from the previous workout from those in the current workout. When building the `more_cb` column, if the previous record does not exist, it'll default to the current value.6. LAG
Now, our results become more powerful. Using `LAG`, we can easily compare the number of calories burned between the current and previous workout. This is great for tracking trends over time.7. LEAD
If we'd rather look ahead, rather than behind, we can use `LEAD`. `LEAD` allows for the comparison of a value to another in a "future" record. The syntax is nearly identical to `LAG`. `LEAD` takes field in the proceeding record to retrieve, the number of records to look ahead, and the default value if a record is not present, which is optional. `LEAD` requires an `ORDER BY` statement, and can be partitioned by the field of our choosing. `LEAD` is commonly used for building datasets for predictive tasks, such as training data science or machine learning models.8. LEAD
To find a member's next workout date, we can use `LEAD` to "look ahead" one record. We specify this by passing `workout_date` to `LEAD`, followed by the number one.9. LEAD
Since we ordered by `workout_date`, it's easy to find the next workout date. For the member's workout on January first, the next workout date is January third, and so on. Like with `LAG`, if we'd like to perform numerical operations between two values, we can do so using arithmetic operators like minus, plus, etc.10. Let's practice!
The best way to master `LAG` and `LEAD` is with some practice; good luck!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.