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

Analytical Functions in oracle explained with real examples

273 ratings | 15651 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 (66)
Nikhar Tare (1 month ago)
Is it possible to find nth highest/lowest salary by using analytical function?
Tech Coach (1 month 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 (2 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 (2 months ago)
Thanks Satheesh I am happy I could help.
satheeshy2k (2 months ago)
Got u !!! Appreciate your quick response
Tech Coach (2 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 (2 months ago)
Can I have the ddl & dml for the tables explained above pls ...
Tech Coach (2 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 (3 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 (3 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 (5 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 (5 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 (5 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 (5 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 (5 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 (5 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 (5 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 (5 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 (5 months ago)
Hi, can you plz make a video on listagg.
Tech Coach (5 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 (6 months ago)
Excellent. You made my life easy. Thanks
Tech Coach (6 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 (7 months ago)
Thanks... Nicely explained
Tech Coach (7 months 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 (7 months 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 (7 months ago)
IT ICCL (7 months ago)
can u share me your mail address. i send you query with database..
Tech Coach (7 months 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 (7 months 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 (7 months 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 (7 months ago)
superb explanation. Thank you.
Tech Coach (7 months 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 (8 months 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 (8 months 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 (8 months 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 (8 months ago)
sure, I will be really happy if you subscribe to my new channel as well. https://www.youtube.com/c/AnIndianAbroadd
Ranaj Parida (8 months ago)
I was totally viewing the vedio for partitions today... So please upload the remaining vedio for analytic function...
Tech Coach (8 months 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 (8 months ago)
Hello. At 17.40 scenario, please try without giving order by clause. i think output will be different. Thanks
Tech Coach (19 days ago)
+Khobare Amol yes sir, you are right that's why I wrote "Almost".
Khobare Amol (19 days ago)
+Tech Coach But I don't think below will give error, SELECT SUM(SALARY) OVER( ) CUM_SAL, E.* FROM EMPLOYEES E
Tech Coach (8 months 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 (8 months 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 (8 months 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 (8 months 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 (9 months 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 (8 months 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 (9 months ago)
Thanks for the kind words, I will review what I have mentioned at 18:35 :)
Deepankar Kotnala (9 months ago)
Can you please post the answers as well
Tech Coach (8 months 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 (9 months ago)
Tech Coach okay..thanks :) We want to match our queries with the solutions provided by you :)
Tech Coach (9 months ago)
Deepankar Kotnala Sure I will post them soon :)
Santhosh Gayakvad (10 months ago)
Vivek , great explanation :) and good/simple examples to make us understand. Helps amateurs like me too to catch it quickly :)
Tech Coach (8 months 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 (10 months ago)
+Santhosh Gayakvad Thanks a lot buddy for the kind words :)
venkata krishna (1 year ago)
Good explanation
Tech Coach (8 months 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 (8 months 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 (8 months 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 (8 months 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.