HomeОбразованиеRelated VideosMore From: Tech Coach

Analytical Functions in oracle explained with real examples

491 ratings | 28830 views
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. http://www.internshipsfromhome.com/oracle-analytical-functions-ddl-and-dml/
Html code for embedding videos on your blog
Text Comments (90)
Amrita Misra (8 days ago)
Vivek...too good...very good explanation...it's worth watching your videos
Tech Coach (8 days ago)
Thanks amrita for the kind words,I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. https://youtu.be/GnVn3mPBRz4
vignesh nagaraj (2 months ago)
That was an interesting piece of information. Many thanks!
anuran acharjee (2 months ago)
Solutions to the exercises: --Select the details of employees with minimum salary on their designation select min(salary) over(partition BY job_id ORDER BY salary asc) as minsal,a.* from employees a; --Select the oldest employees from each department, Add an additional column and give them a 10% bonus of their salary select * from( select lag(hire_date) over(PARTITION BY department_id ORDER BY HIRE_DATE) as oldestemployee,SALARY + salary * .10 as bonus,a.* from employees a)--order by a.DEPARTMENT_ID; where oldestemployee is NULL; --Display all employee details along with the count of reportees their manager has. select count(manager_id) over(PARTITION BY manager_id) countofreportees,a.* from employees a; --.Details of all employees along with an additional column which shows average of salary partitioned by --Designation but only for employees that were hired before 12 years select avg(salary) over(partition by job_id ORDER BY salary) as avgsaldesig,a.* from employees a where months_between(SYSDATE,hire_date) >= 144 ;
Nibi Binu (2 months ago)
@Tech Coach share the link of regex excercise video
anuran acharjee (2 months ago)
@Tech Coach Sure will try it out, appreciate if you could make some tutorial videos of concepts in Ab initio if you are using it.
Tech Coach (2 months ago)
NICE work anuran, would you be willing to solve the regex excercise that I have posted in a recent video?
Vishwanath Nikam (3 months ago)
SELECT COUNT(*) over (partition by MANAGER_ID),EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM Employees order by EMPLOYEE_ID ; is this the correct answer for question #3 ?
Fathima Rizwana (3 months ago)
thank u for ur useful video...in the sample scenario u asked to , Select the details of employees with minimum salary on their designation...i got the query as follows ---- select a.*,min(salary) over (partition by job_id) from employees a order by job_id;but it still retrieves multiple JOB_IDs with all salary ....BUt it need to the minimum salary according to each designation...how to correct it??pls calrify
Tech Coach (3 months ago)
I will take a look at the query Fathima and will get back to you soon, you haven't specified the order by clause along with partition by but that shouldn't cause any issues. Meanwhile I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. https://www.youtube.com/watch?v=IP2Vn6jK8Hg&t=64s
sabila khan (3 months ago)
ques: select oldest employee for each designation and give them 10% BONUS? SELECT ID,NAME, ROLE_ID, SALARY , CREATED_DATE, OLDEST, CASE WHEN OLDEST IS NULL THEN (SALARY*(10/100)) WHEN OLDEST IS NOT NULL THEN 0 END AS "BONUS" FROM ( select a.id,a.name, a.role_id,a.salary, a.created_date, LAG(created_date) over (partition by role_id order by created_date) as "OLDEST" from employees a) order by id; Please let me know if the above is right
Raf D'Naah (3 months ago)
You can use this query to find the recent joinee: SELECT department_id , max (hire_date) recent_hiredate , min (employee_id) keep (dense_rank first order by hire_date desc) recent_employee_id FROM employees GROUP BY department_id ORDER BY department_id; for a more detailed resultset, use this SELECT department_id , MAX(hire_date) recent_hiredate , MAX(employee_id) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_emp_id , MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_last_name , MAX(first_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_first_name FROM employees GROUP BY department_id ORDER BY department_id;
Tushar Gursale (3 months ago)
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...
Abhishek Vedula (4 months ago)
clear as crystal...thank you :)
Tech Coach (4 months ago)
Thanks Abhishek for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. https://www.youtube.com/watch?v=IP2Vn6jK8Hg&t=64s
Raj Kumar Pandit (4 months ago)
Great Sir. Highly appreciated. The way you presented and talked highly impressive. #Respect.
Tech Coach (4 months ago)
Thanks Raj for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. https://www.youtube.com/watch?v=IP2Vn6jK8Hg&t=64s
Pankaj Rao (4 months ago)
Thanks a lot for detailed explanation....
Tech Coach (4 months ago)
Thanks Pankaj for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. https://www.youtube.com/watch?v=IP2Vn6jK8Hg&t=64s
Aishwarya Pattnaik (4 months ago)
Can you post the answers too for the sample problems :) Do you have any forum to contact in case of any doubts.
whoami k (5 months ago)
I think these are one of the best videos available on YouTube. Appreciate your efforts in making these videos.. And many many thanks
Tech Coach (4 months ago)
Thanks a lot for the kind words. I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it. https://youtu.be/IP2Vn6jK8Hg
Nikhar Tare (7 months ago)
Is it possible to find nth highest/lowest salary by using analytical function?
ajay mohite (3 months ago)
@Tech Coach Small mistake in above query.. Corrected in below.. Select * FROM (Select RANK() OVER(Order By Salary ASC) as Latest_Rank, e.* From employees e) Where Latest_Rank=&N;
niharranjan dash (5 months ago)
NO
Tech Coach (7 months ago)
yes it is and it's pretty easy, select * from ( select rank() over (order by salary) as rank1,e.* from employees ) where rank1=n please let me know if it helps. I have a small request I am working on this new channel "An Indian Abroad " I will really appreciate if you watch and subscribe to it. https://www.youtube.com/watch?v=IR6mVE181a4&t=8s
satheeshy2k (8 months ago)
Hi ..nice video about analyitcal functions..thank you...instead of "lead(hire_date)" can'nt we use "max(hire_date)" after partitioning by as below : select MAX(hire_date) OVER(PARTITION BY department_id) ,a.* from employee a
Tech Coach (8 months ago)
Thanks Satheesh I am happy I could help.
satheeshy2k (8 months ago)
Got u !!! Appreciate your quick response
Tech Coach (8 months ago)
HI Sateesh in this particular case max can be used, but max and lead are very different functions in general. Let's say I am trying to analyze how my aircarft flies throughout the day. Source|Dest|dept|arrival|aircraft_type Delhi|Mumbai|7:00|9:00|787 Chennai|Hyderabad|14:00|16:00|787 Mumbai|Chennai|10:00|12:00|787 Hyderabad|Delhi|17:00|19:00|787 I can use lead function here to identify my aircraft positions which can't be done using max. I hope this clarifies the difference. I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it. https://www.youtube.com/watch?v=11DK-oyRql0&t=5s
sudhee reddy (8 months ago)
Can I have the ddl & dml for the tables explained above pls ...
Tech Coach (8 months ago)
Hi Sudhee, Unfortunately I don't have it handy right now, Majority of the tables used here are standard HR schema tables which comes by default when you install oracle express edition. I would recommend installing oracle express edition and trying the queries shown in video on those tables. I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Sameer Kumar Patro (9 months ago)
One question - For cumulative salary you didn't mention the significance of "Order By" in over() clause, which is most imp. This would explain how sum function provides cumulative sum.
Tech Coach (9 months ago)
Thanks sameer, I will review what I have mentioned. I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. https://youtu.be/11DK-oyRql0
Nitin Goalla (11 months ago)
Hey Vivek, you are doing a great job. Keep it up. Also, have you posted the solutions for the practice questions anywhere?
Tech Coach (11 months ago)
Thanks for the kind words, I have not posted the answers but I will try and post soon. I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. https://youtu.be/HBQHekM1U2c
Ahmed Osman (11 months ago)
Thank you for the excellent explanation, I would like to ask you if the following query solve the fourth point in the exercise ? select b.* , avg(salary) over (partition by job_id order by hire_date range 4380 preceding) as Average_Salary from EMPLOYEES b ;
Tech Coach (11 months ago)
Thanks for the kind words.I will review the query soon. I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it. https://youtu.be/HBQHekM1U2c
Ashutosh Sharma (11 months ago)
sum(salary) : it should give total sum of salary department wise, for cumulative sum i think we add 'rows unbounded preceding' in analytical function, can you please clear my doubt.
Tech Coach (11 months ago)
Great Question Ashutosh. First Explanation: once you have used partition by department that means the sum function will be applied on individual departments, now here order by is of vital importance actually because of it the 1st row doesn't have any preceding row so we will get just its salary sum, the 2nd row will have 1 preceding row so it will have the sum of 1st and itself and so on. Try running the query on your own it will give you the correct values. Rows unbounded preceding : yes you are right there are multiple ways to achieve the same result you can use Rows unbounded preceding clause instead of partition by departments to obtain the same result, In Rows unbounded preceding the order by clause is used to describe the window instead of ordering. sum(salary) over (order by dept_no rows unbounded preceding) cum_sum will give you cum_sum of salary department_wise, Because here it will continue to do cum_sum as long as it find the same dept_no, As soon the dept_no changes it will again cum_sum for that dept.So here order by is acting as a windowing function instead of ordering. I hope this clarifies your doubt, I have a small request I am working on this new youtube channel "An Indian Abroad " and it will really help me if you watch and subscribe to it. https://www.youtube.com/watch?v=HBQHekM1U2c&t=1s Please share it with your friends as well, your support is my motivation.
mayur shinde (11 months ago)
Hello,Please find sql's regarding assignment:1. SELECT MIN(A.SALARY) OVER (PARTITION BY A.JOB_ID ORDER BY JOB_ID),A.* FROM MY_EMPLOYEES A; 2. SELECT MIN(HIRE_DATE) OVER (PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID) OLDEST_EMP,         CASE WHEN HIRE_DATE = MIN(HIRE_DATE) OVER (PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID) THEN (SALARY*10)/100              ELSE 0         END BONUS,        A.* FROM MY_EMPLOYEES A; 3. SELECT COUNT(MANAGER_ID) OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.DEPARTMENT_ID) TOTAL,A.* FROM MY_EMPLOYEES A;Please provide solution for last requirement.
Tech Coach (11 months ago)
mayur shinde Good work buddy. I will review them. I have a small request I am working on this new video channel "An Indian Abroad" It would really help me if you watch and subscribe to it. https://youtu.be/q4eXyOPp-Ls
Kashif Ihsan (11 months ago)
Hi, can you plz make a video on listagg.
Tech Coach (11 months ago)
Please check this video it should help . https://www.youtube.com/watch?v=-DDBt48G2ug I have a small request I am working on this new youtube channel "An Indian Abroad" It will really help me if you watch and subscribe to it https://www.youtube.com/c/AnIndianAbroadd
SS Thanu (11 months ago)
Excellent. You made my life easy. Thanks
Tech Coach (11 months ago)
Thanks for the kind words, I have a small request I am working on a new youtube channel " An Indian Abroad" . I would really appreciate if you watch and subscribe to it. https://youtu.be/HBQHekM1U2c
Krishna Paneri (1 year ago)
Thanks... Nicely explained
Tech Coach (1 year ago)
Thanks a lot for the kind words. I would really appreciate if you help me in my new Initiative "An Indian Abroad" by subscribing viewing and sharing. https://www.youtube.com/c/AnIndianAbroadd
IT ICCL (1 year ago)
Hi.. have any idea to know how can i find Query to generate Employee absent report for a given range of dates but.. not possible to query.. can you help me.
Tech Coach (1 year ago)
IT ICCL (1 year ago)
can u share me your mail address. i send you query with database..
Tech Coach (1 year ago)
Can be done using analytical function or a stored procedure both. I can share it with you after 15 days as I am on vacation right now.
IT ICCL (1 year ago)
im working in RMG sector in Bangladesh as a IT person.. every month many people absent continuously like 5 or more days.. so i calculate in those employe who absent continuously basent five or more days in a month ... i can't do this. I need your help to find out this.
Tech Coach (1 year ago)
I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Mithra Pamaraju (1 year ago)
superb explanation. Thank you.
Tech Coach (1 year ago)
Thanks a lot for the kind words :), I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Fatima Alawami (1 year ago)
Hi, Thank u for the great illustration of these functions and their usage. keep up the good work. I have solved the queries for 1,2 and 3 as well. Please check them and confirm. Regarding 4 will employees hired 12 years ago be excluded from the avg calculation or from the end results only. Problem(1) SELECT EMP.*, MIN(SALARY) OVER (PARTITION BY JOB_ID) MIN_DESIGN_SAL FROM EMPLOYEES EMP ORDER BY JOB_ID, SALARY; Problem(2) SELECT OLDEST_HIRE.*, OLDEST_HIRE.SALARY * 0.1 AS BONUS FROM (SELECT EMP.*, MIN(HIRE_DATE) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS OLDEST_JOINEE FROM EMPLOYEES EMP) OLDEST_HIRE WHERE HIRE_DATE = OLDEST_JOINEE AND DEPARTMENT_ID IS NOT NULL; Problem(3) SELECT COUNT(MANAGER_ID) OVER(PARTITION BY MANAGER_ID ORDER BY MANAGER_ID) REPORTEE, EMP.* FROM EMPLOYEES EMP;
Tech Coach (1 year ago)
Hey Fatima great work, I will take a look for sure soon. I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Ranaj Parida (1 year ago)
Hi Vivek, I was looking the vedio for the analytic function vedio no 19 and it was really good and help full. I jst want to ask you that you have not uploaded the next analytic vedio that might be helpful. I was searching that but still I am not getting it in your channel.. If possible please upload it for which i will get notification. Really i am very much interested to get more and more knowledge on analytic function and much more about oracle... If you will publish the vedio class wise or pattern wise for every concept then i would be a great help for the people like me those who are very much interested to get knowledge.. Please requesting you to upload the remaining vedio for analytic function for which it would be helpful.
Tech Coach (1 year ago)
sure, I will be really happy if you subscribe to my new channel as well. https://www.youtube.com/c/AnIndianAbroadd
Ranaj Parida (1 year ago)
I was totally viewing the vedio for partitions today... So please upload the remaining vedio for analytic function...
Tech Coach (1 year ago)
sure Ranaj, I will try to make some more, if you have specific question let me know . I am working on this new youtube channel"An Indian Abroad", please support me by watching subscribing and sharing. https://www.youtube.com/c/AnIndianAbroadd
Santosh Pandit (1 year ago)
Hello. At 17.40 scenario, please try without giving order by clause. i think output will be different. Thanks
Tech Coach (6 months ago)
@Khobare Amol yes sir, you are right that's why I wrote "Almost".
Khobare Amol (6 months ago)
@Tech Coach But I don't think below will give error, SELECT SUM(SALARY) OVER( ) CUM_SAL, E.* FROM EMPLOYEES E
Tech Coach (1 year ago)
you need to specify order by clause in almost every analytical function other wise it will give you an error. Please help me in building my new channel by subscribing sharing and watching. https://www.youtube.com/c/AnIndianAbroadd
Yabu Boys (1 year ago)
hi your videos have good stuff, thanks for providing it in your channel. i have moderate knowledge in sql,plsql' but i don,t know how real time applications are build. will you please make a video in it.
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Tech Coach (1 year ago)
I would recommend watching my videos on DWH,ODS and star snow flake schema to get a basic idea, I will try to make some more
Sahil Pundora (1 year ago)
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.
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Tech Coach (1 year ago)
Thanks for the kind words, I will review what I have mentioned at 18:35 :)
Deepankar Kotnala (1 year ago)
Can you please post the answers as well
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Deepankar Kotnala (1 year ago)
Tech Coach okay..thanks :) We want to match our queries with the solutions provided by you :)
Tech Coach (1 year ago)
Deepankar Kotnala Sure I will post them soon :)
Santhosh Gayakvad (1 year ago)
Vivek , great explanation :) and good/simple examples to make us understand. Helps amateurs like me too to catch it quickly :)
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Tech Coach (1 year ago)
+Santhosh Gayakvad Thanks a lot buddy for the kind words :)
venkata krishna (1 year ago)
Good explanation
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Harish Kumar (1 year ago)
Hi.., the explanation is clear. But can you please tell on sum(salary) using order by employee gives sum of last two employees like window function. Similarly i am confused on window as avg is done three rows where we require only for two rows Please explain Does order by clause has these effects?
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Amaresh Gangal (1 year ago)
Very Good Series..
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Tech Coach (1 year ago)
Thanks Amaresh for the kind words :), I hope you share it with your team and friends as well :)
you are explaining awsome sir...
Tech Coach (1 year ago)
I would love your support for my new channel, Please watch subscribe and share with your friends. https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg
Tech Coach (1 year ago)
Bhavana Priyanka zanavarapu Thanks a lot for the kind words :)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.