LAG and LEAD : Problem Solving using Analytic Functions
HTML-код
- Опубликовано: 4 ноя 2024
- In this video we work through some examples of using the LAG and LEAD analytic functions to solve some specific problems.
Analytic Functions : Introduction
oracle-base.co...
LAG and LEAD Analytic Functions
oracle-base.co...
Analytic Functions : All Articles
oracle-base.co...
Website: oracle-base.com
Blog: oracle-base.co...
Twitter: / oraclebase
Cameo by Gwen (Chen) Shapira :
Twitter : / gwenshap
Blog : prodlife.wordp...
Cameo appearances are for fun, not an endorsement of the content of this video.
All trademarks, product names and logos are the property of their respective owners.
You can find more information here.
oracle-base.com/articles/misc/lag-lead-analytic-functions
Perfect video with excellent examples, zero fluff and just GOLD! Thank you.
Many thank for the clear explaination! I spent a whole day trying to understand both of these functions lol, but I got it from you in 4 minutes 👍
Thanks brother
fml FINALLY a CLEAR tutorial. thanks so much chief. that's a sub for you
I was finding lag with negative offset and found your video here. It helps a lot! Thank you!
Cannot thank you enough , best way to invest and learn through your videos.
concise. to the point, doesn't overload you with information, and uses the commonly used HR schema, quick and easy, EXACTLY what a master procrastinator like me needs to learn
EXCELLENT EXPLANATION
Excellent Very Very Good and to the Point
Thank you so much for the explaination
iam enjoying it please add more videos....good job.
This playlist was really helpful. Thanks!
que crack eres hermano, saludos desde Peru
I enjoyed it...nice.
Great explanation
Thank you!
Very good!!
Hey Tim, do you have any thoughts on if you then wanted to filter down a result set based on lag/lead?
For example the database I’m using is based on website page views. With each row being a visitor Id, the page url, and time stamp.
So I’ve figured out how to use the lag, and lead to display the previous and next pages for each row.
But is there a way to have the lead or lag results in a where statement? So that way I could only view rows where the previous page was the home page for example.
When I try I get an error that analytics functions can only be used in select statements, but wanted to see if you knew of a workaround
Sure. Just wrap your query in an inline view and then put the predicates against that.
SELECT * FROM (Your query goes here) WHERE column_name = ???
Alternatively, use a WITH clause.
WITH data AS (Your query goes here)
SELECT * FROM data WHERE column_name = ???
Those are probably the easiest way to achieve this. :)
To the point...thanks
Hi I’m trying working on a project with stock prices of many different companies. I have rows which show date and closing stock price. I calculated the change in percentage per each day per each company easily with the LAG function.
However, now I want to get an average of the change in prices within a specified date range and then group that average per stock. I’ve been at this for hours but no dice. Is it possible to use AVG AND LAG in the same computation?
You can have multiple analytic function calls in a single statement, and they can do completely different things (ordering, partitioning) with the same data.
Can I use these functions in the Where Clause? I would like to only display values where the current value is not equal to previous value
Analytic functions can only be used in a select list, but you can wrap a query up in an inline-view and use a value in the where clause. Example:
SELECT *
FROM (
SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp) a
WHERE a.sal_prev = 1100;