Это видео недоступно.
Сожалеем об этом.
SQL Window Function with Example- RANK, DENSE RANK, ROW NUMBER, LEAD/LAG | SQL Tutorial in Hindi 14
HTML-код
- Опубликовано: 12 авг 2024
- SQL Full Course Playlist 👇
• SQL Tutorial In Hindi
Like, Share & Subscribe Now :)
⭐ Subscribe for more SQL tutorials like this: bit.ly/3Fc7Gc6
➖➖➖➖➖➖➖➖➖➖➖➖➖
👉 customer csv file: bit.ly/3KLPb2P
👉 payment csv file: bit.ly/41kIYjW
📢 GitHub link (all files): bit.ly/3Y7SRPr 📢
➖➖➖➖➖➖➖➖➖➖➖➖➖
Timestamps:
00:00 Intro
00:21 Window Function
01:26 Window Function Syntax
02:50 Window Function Terms
04:19 Window Function Types
06:04 Window Function Example Using Aggregate Function
11:09 Window Function Example Using Ranking Function
16:24 Window Function Example Using Analytic Function
19:20 Window Function Assignment
Target keywords: complete sql window functions, window function in sql, over clause, partition by clause, ranking function, analytic function, lead and lag function in sql
➖➖➖➖➖➖➖➖➖➖➖➖➖
Related Videos
Introduction to SQL - What Is SQL + Database | SQL Tutorial In Hindi 1
• What Is SQL ? | Databa...
Data Types, Primary-Foreign Keys & Constraints in SQL | SQL Tutorial In Hindi 2
• Data Types, Primary-Fo...
Create Table In SQL & Create Database | SQL Tutorial In Hindi 3
• Create Table In SQL & ...
INSERT UPDATE, DELETE & ALTER Table in SQL With Example | SQL Tutorial in Hindi 4
• INSERT UPDATE, DELETE ...
SELECT Statement & WHERE Clause with Example | Operators in SQL | SQL Tutorial in Hindi 5
• SELECT Statement & WHE...
How To Import Excel File (CSV) to SQL | Import Data From File | SQL Tutorial in Hindi 6
• How To Import Excel Fi...
String Functions in SQL | Functions in SQL | SQL Tutorial in Hindi 7
• String Functions in SQ...
Aggregate Functions in SQL - COUNT, SUM, AVG, MAX, MIN | SQL Tutorial in Hindi 8
• Aggregate Functions in...
Group By and Having Clause in SQL | SQL Tutorial in Hindi 9
• Group By and Having Cl...
TimeStamp and Extract Function | Date Time Function | SQL Tutorial in Hindi 10
• TimeStamp and Extract ...
Complete SQL JOINS For Beginners | SQL JOIN Queries with Examples | SQL Tutorial in Hindi 11
• Complete SQL JOINS For...
SQL SELF JOIN | UNION & UNION ALL in SQL | SQL Tutorial in Hindi 12
• SQL SELF JOIN | UNION ...
Complete SQL Subquery Using Comparison and Logical Operators | SQL Tutorial in Hindi 13
• Complete SQL Subquery ...
Useful videos
How to Become Data Analyst in 2022 | Data Analyst Roadmap
• How to Become Data Ana...
Data Analyst skills, jobs and Roadmap
• Data Analyst- Skills, ...
Excel Tutorial for Beginners
• Excel for Beginners | ...
➖➖➖➖➖➖➖➖➖➖➖➖➖
Connect on other Social Media handles:
Instagram: / rishabhnmishra
LinkedIn: / rishabhnmishra
Twitter: / rishabhnmishra
➖➖➖➖➖➖➖➖➖➖➖➖➖
If this is the first ever video you're watching allow me to introduce myself, Hey I'm Rishabh Mishra and currently working as Senior Data Analyst at Bangalore. On this channel I like to guide and help my juniors and data science aspirants regarding Data Science jobs and useful tips for college students.
Have a great day buddy!
Bhai sach bta rha hu....ekdm izzat se padha aur smjha de rhe ho ❤
Glad you liked it ✅️
Rishab bhai, maine 80% sql apke videos se sikha hai, thanks .........thanks lot ..........lots of love !
Glad it was helpful ✅️
Explanation superb, make him the professor of computer science Department.
Glad you liked the content ✅️
in aggregate fiction ex:- after order by add new_cat not include new_id ,Because new_id result are 100,300 and new_cat result are 300,300
thx bro
same problem bro . if you get any solution please share with me
It is really help full for me and i found roll model for data analyst thanks bro for enhance our skill, please Rishabh make a vedio on real time experience of data analyst project so that we can get clarity please.
Glad you liked it, thanks a lot. Yes i will make a complete video on how we analyze data using Python from scratch
Thanks we will wait your movement
Did you get a job?@@tejasthete7007
"IZZAT SE CHALTA HAI" 😄😄😄 THANK YOU Rishabh Bhaiya , lot of thanks
Good Video. Please share share more videos with real Time example. Also 1 query, For functions like FIRST_VALUE, KST_VALUE, LEAD & LAG in select clause we can have more col's & function can be applied on any one col or more col also?
To summarize, "Rank" leaves gaps if there are ties (same scores), while "Dense Rank" doesn't leave any gaps and keeps counting without skipping any numbers when there are ties.
Awesome 👌
Really liked your teaching style Mr. Rishabh Mishra Sir
Thanku so much,I understand very well
Really liked your teaching style bro .. keep it up
clean and simple, I like the way you explained everything.
Glad you liked it! ✅️
Rishabh bhai thank you so much. I am preparing for data analyst interview and your video's are helping me a lot to understand the SQL more easily
Thank you rishabh...
Thanks for the videos, very useful!!
I got the answer to the question you asked without using the method.
First, we will get the columns with lead and lag then apply the lead and lag again on those columns.🙃
Awesome, keep learning n keep growing 🚀
Great bhaiya❤❤
Bahot achese sikhate ho
sir! jab kuchh search krta hu sql ke liye hindi mein or apki shakal dikh jati hai tho search krna chhod deta hu kynki dil se ek hi awaz aati hai,"Bhai! hai tho apna." #Thanks_for_teach_us_sir
Bahut badiya tutorial banaya hai rishav bhai aapne ❤
Can we use where clause or any kind of filters in any way for each windows function seperately ?
We are unable to fully utilize window function without where clauses... or any trick to do so in sql server ?
Thank you so much for the clear and fluid explanation. My search for a good explanation of "window function" ends here.
Glad it helped! ✅️
Thanks for the video
Really like your Teaching Rishab keep it up.
Glad you liked ✅️
Thanku sir.
one basic q if not silly : in Rank() > is it applied on partition by col or order by col : while assign for duplicates does it look at partition by or order by data thx
we can use group by instead of partition by, is there any specific difference between these 2?
Superb @Rishabh Mishra sir❤
Thanku so much sir for the tutorial 🤩🤩🤩🤩 crystal clear explanation 😍😍😍😍 searching everywhere but finally got the best tutorial thanks alot
Glad it was helpful ✅️
When we are using order by new_id , following is the output I am getting in contrast with the output shown in the video
new_id new_cat Total
100 Agni 100
200 Agni 300
500 Dharti 500
700 Dharti 1200
200 Vayu 200
300 Vayu 500
500 Vayu 1000
Removing the ORDER BY clause from the window functions in your query, give same output as in pdf
Kindly, see below query-
SELECT
new_id,
new_cat,
SUM(new_id) OVER (PARTITION BY new_cat) AS "Total",
AVG(new_id) OVER (PARTITION BY new_cat) AS "Average",
COUNT(new_id) OVER (PARTITION BY new_cat) AS "Count",
MIN(new_id) OVER (PARTITION BY new_cat) AS "Min",
MAX(new_id) OVER (PARTITION BY new_cat) AS "Max"
FROM test_data;
or
use- "ORDER BY new_cat" instead of "ORDER BY new_id"
SELECT
new_id,
new_cat,
SUM(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Total",
AVG(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Average",
COUNT(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Count",
MIN(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Min",
MAX(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Max"
FROM test_data
amazing.......
very helpful
Very well explained
Thanks sir ❤
Best sql videos on youtube.
Glad you liked it ✅️
Thank you so much bhaiya for uploading such a wonderful content onto RUclips which is of great use to beginners like me
Glad you liked it ✅️
Very useful 👌👌
Sir you taught in a very simple manner.. Love your tutorial
Glad you liked ✅️
bht hi awesome bhai.. perfectly explained thanks
Glad it was helpful ✅️
hello sir , in the last question ,can we use rows between formula in the lead and lag function problem to solve this ?
Been looking for it. In a world full of free resources, found this of great help ❤.thank you
Glad I could help!
Thanks for explaining windows fun..really helpfull
Glad you liked it 👍
Thanks for the videos, very useful!!
Glad you like them! 👍
Thanks
Bhai tumhare videos sabse best aur easy way mein samjh mein ate hai….❤
Glad you liked ✅️
Hi Rishabh, thanks for your video. I see there is one discrepancy in output of first example:
"Partition BY new_cat Order BY "new_id". This doesn't give the output as shown in the picture in the video. It has to be ordered by "new_cat" instead of "new_id", then you will get that output. Eg: Partition BY new_cat Order BY new_cat. Please try this once and let me know if am wrong here.
Thanks!
good observation, with the query mentioned in the video we will get the cumulative sum in that group
great video especially for interviews
Glad you like them! 👍
Big fan of you sir,
Simplest explaination ever.🌹
Glad you liked it
Thanks... Really helpful!
Glad it helped! ✅️
Really nice video mate! You got a new subscriber.
Glad you liked it n Welcome aboard! 🚀
u R amazing
thanks bro for this wonderful explaination of window function
Glad you liked it ✅️
Hello Bhaiya, Actually You forgot to make interview question videos related to window functions and LEAD/LAG and its been quite a while like 10 months , please do it, it would be much helpful for us because your explination is really great .
Noted ✅️
Hi Rishabh, I ran below queries however the output is same for both. Could you please check and explain once pls. Thanks!
select
new_id,
first_value(new_id) over(order by new_id) First_Value,
last_value(new_id) over(order by new_id) Last_Value,
Lead(new_id) over(order by new_id) Lead,
Lag(new_id) over(order by new_id) Lag
from test_data;
--WITH RBUPAUF (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
select
new_id,
first_value(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) First_Value,
last_value(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Last_Value,
Lead(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Lead,
Lag(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Lag
from test_data;
very easy to understand, nice video.
Glad it was helpful! ✅️
I have a qstn plz reply 🙏🙏
I did my graduation in BCA, should I do MCA, then look for a data science field or BCA is enough for job and future also???
Plz reply🙏🙏🙏🙏
thankyou so much kitna acha padhaya hai
Glad you liked it ✅️
Thanks and Love from Pakistan
we can do all this using group by and having too. correct?
i have and interview and it helped me a lot
Awesome, keep learning n keep growing 🚀
Thanks for these superb explaination sir..... I will never forget these topic bcoz of ur wonderful teaching
Glad you liked ✅️
At first a great thanks for ur explanation,
Ekk Doubt h
Hame kb smjh ayega ki window function use krna h ya normal sql query lgana h ......Plzzz Reply
Practice n you will understand
In aggregation functions if i do Order by new_id then answer is different by your output sir after that i changed to Order by new_cat, output is same as per o/p table ... pls reply sir @rishabh mishra if i made a mistake
Thanks for this vdo
Glad you liked ✅️
smooth like a smoothie :)
before : window function is difficult to understand
after this video: what! window function is sooo easy
Glad you liked ✅️
Hello, first of all your videos are really helpful, thankyou. Secondly, I am trying to run the Windows Aggregate func but its giving different input, kinldy help. Attaching the code and the output below: select new_id, new_cat,
sum(new_id) over(partition by new_cat order by new_id)as "Total"
new_id new_cat Total
100 Agni 100
200 Agni 300
700 Dharti 700
500 Dharti 500
200 Vayu 200
300 Vayu 500
500 Vayu 1000
from test_data
If you remove the ORDER BY from the statement or ORDER BY new_cat then it will display exactly like in the video.
it was nice. lead and lag ka use case scenario kya hoga?
sir very helpful can you pls make a video on python visualization and tableau and how to tackle this question in interview like if we have a gap after college
Okayy noted ✅️
bro thanks a lot i am following your sql plylist this one your teaching is very good and iam from pakistan
but i am facing an issue in window function when i use sum with window function it not working like yours its show first the low value in in the next value add both values in show the sum in the third row combine all the three and show the sum
like not one unique value when u read my comment please guide me are tell me the name of mistake what iam doing so i can find some soulution on the internt
Made it very simple!
Glad you liked ✅️
Very well explained Rishabh.
Glad you liked ✅️
Very well explained ❤
Glad you liked ✅️
Unbounded preceeding and unbounded following ka meaning samaj nhi aya....woh kya kar raha hai
Thank you so much sir ❤
Happy to help ✅️
Thank you so much rishabh bhaiya for giving such a beautiful content ❤❤
13:23 Ijjat se chalta hai dense rank.........waaah rishabh bhai
Row clause smjh nhi aaya "rows b/w unbounded preceding and unbounded following" plz explain
Thanks for such a easy to understand video. ❤❤
Also I have se concern regarding data analyst job like ....is it necessary to know ML also or predictive modelling for this job??
It's good to hv but not compulsory
In 6:57 the output is different from the expected output, so just remove the order by clause and u are good to go
or use ORDER BY new_cat instead of new_id
@@swarnimamishra7007exactly. Ab sahi aa tha hai. Otherwise to Sum aur average me first value of I'd as it is print ho rhi thi
So what is the window in the window fucntion ? Are partions windows ?
best
Dense Rank - Magar yeh ijjat se chalta hai 😂 Loved the way of your teaching!! Thank you :)
Glad you liked ✅️
Pyspark playlist please..
brother there are no csv files for this tutorial available on your github repository, please upload
Thank You Rishabh
Glad you liked ✅️
Bahut hi aaacha padhate ho aaap. U earned a subscriber and a like. Aaaap apna course shuru karo, minimal fees like 200 or 300 rakhlo. Aaur pura detail mein sql lo. Kyunki market mein log short course le rahe hai. Aur pura 6 ghanta le rahe hai, lekin usme 4 ghante ki advertisement kar rahe hai aur baaate kar rahe hai. Lekin 4 ghanta nahi padha rahe , sirf 2 ghanta pada rahe hai, masterclass mein. Uske wo 400 rs le rahe. Aur lakhon kama rahe , aur baccho se gaddari kar rahe hai. Aaap toh acche insan ho acche teacher ho , aaap masterclass lo , aur 6 ghanta mein sql khatam kar do. Let me add 1ce again , you teach very lucidly, especially the hindi language
I think that the ORDER BY clause is not to be used since i am gettig the different result....by using order by clause each line is getting calculted instead of the defined window.....please correct me if anybody is also getting the different result instead of the desired result .....i am talking about this section 6:32
Window functions maintain the individual rows while also providing aggregate-like results.
bro informative video, but can you share test data file used in video
sir can you please share the ppt which you have used in the video @Rishabh Mishra sir
Please provide use case situations along with solution
It would be great if u could upload a dedicated video for partition by.
Okayy noted ✅️
Excellent teaching skills❤
Glad you liked ✅️
SELECT new_id,
FIRST_VALUE(new_id) over(order by new_id) as "First_Value",
LAST_VALUE(new_id) over(order by new_id) as "Last_Value",
LEAD(new_id) over(order by new_id) as "Lead",
LAG(new_id) over(order by new_id) as "LAG"
FROM [practice_sd].[dbo].[windows function]
sir apne bola ki percent rank 0 se chau hoke jaha last walue wo 100% tak jayega lekin sir aisa nahi hai uska formula ye hai rank-1/no. of rows -1
You r great , bcz u explain in hindi.
Pl tell how much packg we get as sql + tableau expert at start. What salary we get after 5 years exp. Pl tell the pkgs. Thabknyou. You must have worked in the industry
will make a detailed video on this! soon!
@@RishabhMishraOfficialu have not made a video
Good video Rishab. I really enjoy the learning through the content you shared.
superb❤❤❤❤
✅️
your explaination is good. however, you need to write the Query insted of directly pasting. if you write the query i belive your views will increase.
thank you.
Jabardast, really loved the way u explain concepts. Thanks so much!!
Glad you liked it ✨️