Analytical Functions in oracle explained with real examples

This video explains analytical functions and how they are implemented in real projects.
Analytical functions are somewhat similar to aggregate functions,but they offer much more.
Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times
They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function.
I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions.
You can find the sample problems along with dataset in the below link.
#AnalyticalFunctions #OracleAnalyticalFunctions #TechCoach

[nileshk]

I think these are one of the best videos available on YouTube. Appreciate your efforts in making these videos.. And many many thanks

[sahilpundora]

Thank you for the video. Really helpful to understand OLAP basics.
On a light note, when you said at 18:35 "to come up with an easier answer to find the money spent by company as salary to employees", well, instead of cumulative, we can just use SELECT SUM(SALARY) FROM EMPLOYEE; and if we want department wise, we can use the same with GROUP BY clause. I believe there is a fine line where we should use all these OLAP functions or aggregate functions.

[mithaSantara]

Correction -- @12:05 you said Previous value -- so in LEAD() it should be Next row not previous value. if its a LAG() I can understand. Hope this is useful to the viewers.

[anctay]

This amazing video you made 6 years back from now and even today it is very relevant and helpfull. You have such an amazing explanation talent, why you dont made video on SQL queries regularly!!! .

[ambarkumar]

Hi, at 5:15 I didn't gt why would we use a self join without self join we got the department wise avg sal

[devinderbombrah]

The narrative is very well-paced, a very good presentation. Good to see such videos and hope we shall have more from you.

[madhavikatta]

Vivek please continue your effort. Nice explanation.

[SAK-yj]

Excellent !!!. Thank you very much for deep explanation.


Solution of exercises 1:
select min(salary) over (partition by job_id) as min_sal
, a.* from emp a;
2:
select e.*, (salary+salary*.10) as Bonus
from (
select lag(hire_date, 1, null) over (partition by department_id order by hire_date ) as old_emp, a.* from emp a) e where e.old_emp is null;
3:select count(*) over (partition by manager_id ) as cntofReportee, a.* from emp_bk a
4:select a.*, avg(salary) over (partition by department_id) from emp a where
(months_between(sysdate, hire_date) /12)> =12;


I am confused about order by, when to use and when not .Please clear my doubt.

[tushargursale]

Hello
In your video ...at the point where you are explaining cumulative sum, you said order by doesn't matter but in fact it matters a lot if you don't specify order by clause it will not provide the cumulative sum of salaries instead it will give sum of salaries department wise.


Other than that it was a very helpful video thanks a lot man...keep up good work...

[bishwash.sharma]

Why do we need to go with Lead function? We can simply do with the dense_rank() function and it gives result set if we have multiple joinees at same date.

[shivkanyashinde]

Yes correct, but what if we have 2 employees in same department hired on same date ...for example in this scenario department 80 hired 2 employees on same day so it should show 2 employees with analytical function as recent joinee but it shows only one .

[vishvadeepmohanpandey]

Very Good Explanation looking forward more videos like that. Given link is not working please provide link to download the data set

[nishikantadik]

You are doing great . You are actually help me to understand it in easier way . 😃 Thank you so much brother.

[akshaykadbane]

select*from (
select lag(hire_date) over(partition by department_id order by hire_date) as old_emp, a.*, (salary+(0.1*salary)) as new_salary from employees a order by employee_id) where old_emp is null order by employee_id

[ShaikAbdulArif]

thanks for making best video, I haven't seen before, great job and appreciate your efforts

[vru]

what if I want lead(hiredate) -1 in output . how to do this?

[patrickng]

Wow didn’t know analytical function is so powerful until now

[rashisingh]

Best Explanation of Analytical functions. Thank you!

[ursus]

So the main advantage is to avoid joins in your query, right. Now, I have a DataWarehouse 3rd normal form with 2000 tables, will AF be an advantage for me?

[AdityaVermaopenheartedme]

Hi great video on analytical functions, just got one issue.
In the video, at 22:18 you said that it is going to compute the average salary of Neena by taking average of three salaries, whereas the function is row 2 preceding, should not it be calculating the average salaries only using only 2 prior salaries, the salary of Neena and only one before her?
Please give clarification.