I've seen so many videos trying to explain windows functions and none of them Explain it as you do. I think the difference is you showed the difference between using group by, CTEs and windows function. Thanks a lot, Dev.
I came to TH-cam after going through a paid online SQL course wanting to understand window functions better. You explained/showed it to a level where conceptually it sunk in. Thanks.
I have watched this video, and others (in fact all of them), of yours and really enjoy your approach and pace. For what it's worth, I have watched many other SQL Tutorial channels, however, I find myself drawn back to your videos time and again.
In the last bullet of your "Window Functions" slide, you want to use the preposition "into" rather than the phrase "in to". The result set is being split into partitions, denoting that the abstract positioning of the data is changing.
A short time ago I was contacted by an employer for an SQL Developer job (Entry Level), and they wanted me to take a skills assessment. The manager told me that I should know CTE's and Window Functions. I was pretty comfortable with CTE's but I basically learned what Window Functions were (and how they are used) from your videos, and also found your CTE videos a useful refresher. I watched the series over a few times until I was comfortable playing around on my machine with them, and, turns out, like 90% of the assessment was a practical coding assessment for Window Functions/CTE's (moderately more complex than the examples in your videos). I ended up being hired for the position. I just wanted to say that I appreciated your videos and they were apparently clear enough to both introduce Window Functions and help me understand it enough to apply it appropriately (in tandem with playing around a bit with a practice database i.e. AdventureWorks). So thanks!
That’s absolutely fantastic news, congratulations on the position. It’s great to hear you took the concepts from the videos and were able to apply it to different databases and answer questions, that’s exactly what I was hoping for when I set up the channel. Keep asking questions and learning and you will go far.
Wow! I've literally been searching for an easy-to-understand explanation of what Window Functions are, and this one is the best explanation so far. Thank you so much!
Awesome video, thanks! The explanation is better than some paid online courses provide. It would be great if you also shared the table you use for querying to be able to practice and compare the results.
Clear and logistic! I find a lot of videos about introducing window functions, but this is the best one and expound some relations between other functions!
Best explanation for Window functions! Can you please zoom in onto the area where you are writing the code, I listen to your videos on the go on my phone and it's not always easy to see what's being written on a mobile screen
Wonderful video. Is there a way to created window functions in “Views” - where you can see the tables and fields being used? All while still performing a window function
I've always used the partition inside the over, so just using the over alone is pretty cool! A fellow developer I know uses the case statement. Very versatile, and much better than CTE's, IMHO.
That’s an interesting approach with CASE statements, what if you wanted partition by CustomerId and had 100s of customers or partition by multiple columns surely the approach would become way too long winded.
Great video! If you could make some videos with Problems where we would be using these Analytical functions, that would help the people preparing for interviews. And would help in better understand the topic.
Please do work along projects for data analysis or business intelligence from start to finish. It'd teach how to do basic projects from start to finish. And would help in updating skills as well
@@BeardedDevData yeah, i'm using these functions for the first time and need to join multiple tables and use the right table info in the partition for different columns. Eg count and partition from table b for one column and do the same from table c for another. Just wondering if you had covered this at all. I've tried a few tests but with different results. I'm doing this in Salesforce marketing Cloud so its not a full sql db environment, so that might be why. Its sql server 2005.
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 ?
Unfortunately not, window functions will run over the entire set of data. There are a couple of options, one is to build separate window functions and then join the results using derived tables or CTEs, this has the problem with running over the same data multiple times, the other option is to get creative with case statements, could potentially flag rows for whether they should be included in calculations or not, this isn't that straightforward though depending on what you want to achieve.
Hi Phung, unfortunately I no longer have the script for it but because of that I have made another video where you can follow along: th-cam.com/video/lBcDSsgp0RU/w-d-xo.html
For this line , SUM(Line_Total) OVER() AS Total what is the point of adding the over clause? Is this the same as doing the sum(line_total) and then adding group by at the end? Is over() allowing us to skip the group clause? And is partition by essentially equivalent to group by when doing aggregate functions?
OVER() is used to define the window, as it’s blank all rows are considered. It will return the same value as grouping but with window functions we can retain the detail rows. PARTITION BY is part of the OVER clause and further defines the window, this is different from grouping, with grouping we lose the detail rows, with window functions we retain the detail. It is important to understand that grouping is evaluated before window functions, which are usually part of the SELECT clause but can be used in ORDER BY too. You can use grouping and window functions in the same query. You can find more information on logical query processing here: m.th-cam.com/video/sBRfBU5jh18/w-d-xo.html
Hi, thanks for the educational video. I am interested in the performance details, did you address this in a followup video somewhere? Can't seem to find it.
Great Video, Thanks! How to we select multiple conditions on the OVER part. e.g, Order numbers got repeated every day and we had to partition over 'day' AND 'order number' to get to the order total. Is this possible?
Great video, thanks a lot :) Perhaps you know how to use window function in where clause? Let's say, I have a query like this: select count(inve_key) over (partition by inve_key) as c_inve_key from JEREIN and I want only results where c_inve_key is greater than 10. How to write this in where condition?
Hi, thanks for the feedback, that is a great comment, I am actually uploading a video on this next week so I will let you know as soon as it's available. We have to be careful when working with Window Functions with a WHERE clause as the WHERE clause filters data before the function is applied so if the data is needed it cannot be part of the WHERE clause. To resolve this issue I would generally use a CTE and add the WHERE clause when selecting from the CTE, have you used CTEs before?
Yes I did. Not a lot, but I did it. My windows function is part of a much larger query. I would like to use where inside window function. Like you make partitioning, but only on limited sets.
Hi Beardeddev, from where I can get this Sales Database ? Actually I want to do hands on along with your tutorials. Would be glad if you provide the link of the same :)
Hi, thanks for your comment, I no longer have the database but if you need a demo database to work with you can download AdventureWorks, I’m going to being uploading a video on how to download and restore soon.
I’m going to do my best to make sure everyone can follow along in future videos, whether that be using AdventureWorks or creating my own, if you let me know if you have any difficulty I will help you through the examples.
@@BeardedDevData Yeah actually it should be like that only, so that everyone can follow with you and can do hands on simultaneously. So, for now am practicing it with the databases that I already have, so yeah thanks a lot for your quick revert !!
Hi, unfortunately I no longer have the database but as so many people have requested it I’m going to upload a new copy that people can follow along to. I will upload it this weekend.
Good video. Thanks. Can we use where clause on aggregate function (eg: select count(*) over(partition by department_id order by department_id)no_emp ,e.* from employees e where count(*) >3;
Unfortunately you can’t within the WHERE clause as this is evaluated before the aggregations are applied. If you need to filter based on an aggregate function you have the option of HAVING COUNT(*) > 3 if using GROUP BY or use a CTE and then adding a WHERE clause when selecting from the CTE.
One feedback. next time when you record your videos try increasing the font size. I'm trying to watch this video on iPhone and its impossible to see the text on the screen. Why not increase the font size? you have 99% white space on the screen.
Hi, I will put this up as soon as I can. Thanks for the feedback on the videos, let me know if there is a particular area you would like to see a video on.
Unfortunately I don't have it, but if you take a look at this video, th-cam.com/video/lBcDSsgp0RU/w-d-xo.html, the description includes the code to follow along.
Appreciate the feedback, I have enlarged the queries on my latest videos so hopefully this is an improvement. I am also looking in to some software that will allow zooming in to show everything more clearly on all devices.
That's a really good question and the answer is quite extensive. There is generally multiple ways we can achieve the same result working with SQL and if we put performance aside it really depends on what we are trying to achieve. If we are looking at applying aggregates to a whole data set or just one type of group then group by is more suitable, there is also the benefit of the having clause that we can apply if we want to see groups that meet a criteria. When working with window functions, they are part of the select statement so filtering based on criteria can be more difficult but if we want to return the underlying data, calculate running totals/averages, find next or previous value then they're are extremely useful. That being said we can also use both together, we can group our data using group by and find out the total of all groups by using a window function. Hopefully that helps clarify the difference and I will be doing some videos over the next couple of months focusing on performance and I will cover the differences between how group by and window functions are executed.
What if you wanted get the Sales_Details_ID on the row with an aggregate value (say MAX(Line_Total) ) within a Sales_Id group? So I want to look at each Sales_Id group, and get the Sales_Details_Id on the row with the maximum line_total. I've been looking for a way to do this without a CTE but that might end up being the only way, CTE + RowNumber() partition by sales ID
So what I want is something like SELECT Sales_Details_ID, MAX(Line_Total) FROM Sales_Details GROUP BY Sales_ID (This wouldn't work however because Sales_Details_ID would have to be in the GROUP BY clause
Can you tell me the relationship between sale_id and sales_details_id? The reason I ask is that if one sale_id can have many sales_details_ids related then when you group by sale_id which of the sales_details_ids would need to be returned? As one value can only be returned then you need an aggregate function on sales_details_id. If it’s a one to one relationship then you can group by sales_details_id.
@@BeardedDevData So in my case it's EmployeeID, EmployeeReviewScore, and ReviewDate. The relationship is one EmployeeID to many EmployeeReviewScore and ReviewDate. What I want is the most recent EmployeeReviewScore for each employeeID. So I would need to do something like this: SELECT EmployeeID, EmployeeReviewScore, MAX(ReviewDate) FROM table GROUP BY EmployeeID I only want to group by EmployeeID, but SQL Server syntax requires you to group by ALL non-aggregate fields. If I add EmployeeReviewScore to the GROUP BY clause, then I'll get multiple rows for each EmployeeID, which I don't want. I've solved this by creating a CTE ordered by Date with a rownumber() partition and joining to that, but I was just wondering if there is a cleaner way I can do this without a CTE.
In the scenario you have explained a CTE is the perfect choice, to get the result you require you need to perform multiple operations on the data, first identify the MAX(review date) and then use that to identify the employee review score. Of course there are other ways to do it using other types of temporary objects but I like to use CTEs for this exact situation. In fact when somebody asks me what a CTE is and what’s it’s used for I would describe this exact problem.
Conditional window functions come to mind, like max(case when)over(...). as opposed to case when max()over(). They are very particular for sure. but its not something that I find well explained in many literature. But mostly the former is of interest.
Because the window function happens in SELECT or ORDER BY phases you cannot filter in the WHERE clause in the same statement. To do this you need to use a derived table, SELECT * FROM ( ) AS D WHERE Line_Total >= 3
not working over function in derby or sqlite getting error " [Exception, Error code 30,000, SQLState 42X01] Syntax error: Encountered "PARTITION" at line 2, column 22." SELECT SALES_ID,SALES_DATE,ITEM,PRICE,QUANTITY,LINE_TOTAL, SUM(LINE_TOTAL) OVER(PARTITION BY SALES_ID) AS SALES_TOTAL FROM SALES_DETAILS;
You can add a Window Function to an Order By clause but I cannot think of a use case where it would be beneficial, did you just want to know if it’s possible or do you have a particular example you are working on?
I’m not sure what currency that’s in but I’m guessing it’s a lot, I see a lot of training providers are in it for the money and don’t deliver good content. It’s part of the reason I set up the channel, more people are needed in the world of data and analytics and training should be readily available. Microsoft offer a lot of free training through Microsoft Docs and Microsoft Learn and edX are also quite good.
Sorry could anyone here help me out with the question about Window Functions, that: it's already include the ORDER BY, so is it necessary to explicit again when we back to the main query ORDER BY again. For example: SELECT sales_transaction_date, SUM(total_sales) OVER (ORDER BY sales_transaction_date) FROM sales ORDER BY sales_transaction_date;
Yes, the order by within the windows function does not impact the order of the results even if it seems to do so. You must add an order by at the end of the query to guarantee ordered results.
It is important to notice that the Sales_Date column in your DB has its value all truncated by days. So, if someone wants to do that with a more granular level of "datetime", this someone should learn how to truncate dates.
I wouldn’t use the term truncate personally, the Sales_Date column is a data type of DATE which has a granularity of day therefore you are correct if you are using a DATETIME data type then you will need to remove the time element and for that I would recommend CAST or CONVERT. I generally only use the term truncate when removing characters from a string or removing all rows from a table but I’m aware other RDBMS might refer to the operation as truncate.
I loved this video, looking forward to see more videos from you
I've seen so many videos trying to explain windows functions and none of them Explain it as you do. I think the difference is you showed the difference between using group by, CTEs and windows function. Thanks a lot, Dev.
Agreed
I came to TH-cam after going through a paid online SQL course wanting to understand window functions better. You explained/showed it to a level where conceptually it sunk in. Thanks.
That’s great.
I have watched this video, and others (in fact all of them), of yours and really enjoy your approach and pace. For what it's worth, I have watched many other SQL Tutorial channels, however, I find myself drawn back to your videos time and again.
Thanks, appreciate the support.
One of the best explaination of Window functions to get started on !!
In the last bullet of your "Window Functions" slide, you want to use the preposition "into" rather than the phrase "in to". The result set is being split into partitions, denoting that the abstract positioning of the data is changing.
Yes, that’s my mistake.
A short time ago I was contacted by an employer for an SQL Developer job (Entry Level), and they wanted me to take a skills assessment. The manager told me that I should know CTE's and Window Functions. I was pretty comfortable with CTE's but I basically learned what Window Functions were (and how they are used) from your videos, and also found your CTE videos a useful refresher. I watched the series over a few times until I was comfortable playing around on my machine with them, and, turns out, like 90% of the assessment was a practical coding assessment for Window Functions/CTE's (moderately more complex than the examples in your videos). I ended up being hired for the position.
I just wanted to say that I appreciated your videos and they were apparently clear enough to both introduce Window Functions and help me understand it enough to apply it appropriately (in tandem with playing around a bit with a practice database i.e. AdventureWorks). So thanks!
That’s absolutely fantastic news, congratulations on the position. It’s great to hear you took the concepts from the videos and were able to apply it to different databases and answer questions, that’s exactly what I was hoping for when I set up the channel. Keep asking questions and learning and you will go far.
Wow! I've literally been searching for an easy-to-understand explanation of what Window Functions are, and this one is the best explanation so far. Thank you so much!
That's fantastic to hear.
The best description of windows functions that I have seen. Thank you!
Thanks so much.
You are by far the best SQL tutor. I dont get it why you dont have many subscribers and views. Great videos.
Thank you very much.
Thank you so much, i was never able to understand these until now !
Awesome introduction to Window functions. Clear as Icelandic water on a sunny day. good job
Awesome video, thanks! The explanation is better than some paid online courses provide. It would be great if you also shared the table you use for querying to be able to practice and compare the results.
Explain window functions with examples (sum, count) very clearly. Thank you.
Short, convice and to the point. Very good video!
Great video. one of those topics as a software dev I never really cared to look into but glad you made it so simple to learn!
Love your SQL tutorial videos! Help me a lot. Super clear and easy to understand.
Thanks so much 👍.
Very well explained video. I studied it for the first time and clearly understood it. Thanks!
Thanks a lot the best explanation for OVER() and PARTITION BY I ever seen :)
Thanks so much.
Another great tutorial from BeardedDev!!
Thanks so much.
Clear and logistic! I find a lot of videos about introducing window functions, but this is the best one and expound some relations between other functions!
Thanks so much!
great explanation of window functions, thanks a lot
Your explaining methods and skills are awesome. It helped me a lot. Thanks.
Very good demo and explanation for windows functions.
great example, very well explained. Thank you.
No problem 👍
Awesome video! Love that you explained the group by version and included both in the description of the video. Thank you!
Best explanation for Window functions! Can you please zoom in onto the area where you are writing the code, I listen to your videos on the go on my phone and it's not always easy to see what's being written on a mobile screen
Thanks so much, appreciate the feedback, I have added zoom to my latest videos.
Thank you for the video. Excellent tutorial and explanation.
Cooool ! I was confused by the concept and now I fully understand!
Great tutorial thank you!
Done thanks took notes
Very well explained. Thank you.
thank you, thank you, thank you so much! you made this topic so easy to understand!!!
Thanks so much for the feedback.
Some videos on the Statistical functions would be fantastic.
Hi, I will put them down on my to do list for the next couple of months.
love the video! window function well-explained! Thank you so much! also love your accent!!!
Wonderful video. Is there a way to created window functions in “Views” - where you can see the tables and fields being used? All while still performing a window function
You can create a window function in views but to see the table and columns being used you'd have to look at the definition of the view
Dude, great video, well planned, well explained, good audio, amazing work.
Thanks, let me know if there are any any particular tutorials you would like to see.
Absolutely mate. Maybe Git would be good hahahaha still a newbie
yo I came here for window function but the axxxent is a def a bonus ;) love from the USA!
Thanks so much.
I've always used the partition inside the over, so just using the over alone is pretty cool! A fellow developer I know uses the case statement. Very versatile, and much better than CTE's, IMHO.
That’s an interesting approach with CASE statements, what if you wanted partition by CustomerId and had 100s of customers or partition by multiple columns surely the approach would become way too long winded.
Any plans to do a couple of videos on the Windows statistical functions?
I can certainly do some, I'm just finishing off my DA-100 certification then I will be making a lot more videos
@@BeardedDevData thanks, they would be great.
Great video!
If you could make some videos with Problems where we would be using these Analytical functions, that would help the people preparing for interviews.
And would help in better understand the topic.
Great idea, I will come up with some scenarios.
Please do work along projects for data analysis or business intelligence from start to finish. It'd teach how to do basic projects from start to finish. And would help in updating skills as well
Your tutorial is just so amazing!!
Thanks so much
Could you please share the dataset to practice along. Thanks !!
Good explanation...
Thank you :)
Hi, love the tutorials. Have you done anything on using windows functions within multiple join selects?
Hi, do you mean using window functions over multiple tables joined together?
@@BeardedDevData yeah, i'm using these functions for the first time and need to join multiple tables and use the right table info in the partition for different columns. Eg count and partition from table b for one column and do the same from table c for another. Just wondering if you had covered this at all. I've tried a few tests but with different results. I'm doing this in Salesforce marketing Cloud so its not a full sql db environment, so that might be why. Its sql server 2005.
By the way love your videos. I'm learning a lot!
I haven’t done a video on different partitions and joining tables in the same query but I will get one up as soon as possible.
I was looking for Window Function in R and I got this. Anyway it was a great learning experience . For sure I gonna use it in the future.
That’s great.
Great Video! I like the way you teach...
Thanks so much, look out for new videos coming soon
Need dataset for all your window functions video tutorials as you are using different dataset for different videos
clearly understand! Thank you for uploading this tutorial
Thank you for the positive feedback, let me know if there is anything you would like me to do a video tutorial on
Thanks for sharing this amazing SQL tutorial!
No problem, more tutorials coming soon.
Very good explanation
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 ?
Unfortunately not, window functions will run over the entire set of data. There are a couple of options, one is to build separate window functions and then join the results using derived tables or CTEs, this has the problem with running over the same data multiple times, the other option is to get creative with case statements, could potentially flag rows for whether they should be included in calculations or not, this isn't that straightforward though depending on what you want to achieve.
Thanks a lot for this tutorial. can i get the either Db backup or script? so that i can practice?
Hi Phung, unfortunately I no longer have the script for it but because of that I have made another video where you can follow along: th-cam.com/video/lBcDSsgp0RU/w-d-xo.html
@@BeardedDevData Thanks you very much sir. much appreciate for your contribution.
For this line , SUM(Line_Total) OVER() AS Total
what is the point of adding the over clause? Is this the same as doing the sum(line_total) and then adding group by at the end? Is over() allowing us to skip the group clause? And is partition by essentially equivalent to group by when doing aggregate functions?
OVER() is used to define the window, as it’s blank all rows are considered. It will return the same value as grouping but with window functions we can retain the detail rows. PARTITION BY is part of the OVER clause and further defines the window, this is different from grouping, with grouping we lose the detail rows, with window functions we retain the detail. It is important to understand that grouping is evaluated before window functions, which are usually part of the SELECT clause but can be used in ORDER BY too. You can use grouping and window functions in the same query. You can find more information on logical query processing here: m.th-cam.com/video/sBRfBU5jh18/w-d-xo.html
Great job explaining this concept!
Awesome content
Thank you. Awesome video
Hi, thanks for the educational video. I am interested in the performance details, did you address this in a followup video somewhere? Can't seem to find it.
Hi, thanks for your comment, I haven’t uploaded any videos yet on performance of queries but will be doing some over the next couple of months.
@@BeardedDevData Awesome.
Which video do you look at frames? I can't find it
m.th-cam.com/video/6S7z2wabJxk/w-d-xo.html
Great Video, Thanks! How to we select multiple conditions on the OVER part. e.g, Order numbers got repeated every day and we had to partition over 'day' AND 'order number' to get to the order total. Is this possible?
Yes, definitely, as you mentioned in the comments you just need to partition by day, order number.
Top stuff!
Great video! Thank you.
Can you make a video about GROUPING_ID
Beautiful!!!!!!
Brilliant tutorial, thank you.
Thanks so much.
Great video, thanks a lot :) Perhaps you know how to use window function in where clause? Let's say, I have a query like this:
select count(inve_key) over (partition by inve_key) as c_inve_key
from JEREIN
and I want only results where c_inve_key is greater than 10. How to write this in where condition?
Hi, thanks for the feedback, that is a great comment, I am actually uploading a video on this next week so I will let you know as soon as it's available. We have to be careful when working with Window Functions with a WHERE clause as the WHERE clause filters data before the function is applied so if the data is needed it cannot be part of the WHERE clause. To resolve this issue I would generally use a CTE and add the WHERE clause when selecting from the CTE, have you used CTEs before?
Yes I did. Not a lot, but I did it. My windows function is part of a much larger query. I would like to use where inside window function. Like you make partitioning, but only on limited sets.
I mean like part of subquery :)
Hello. Can I get the Data as I wish to practice it simultaneously
Hi, please have a look at this video, m.th-cam.com/video/lBcDSsgp0RU/w-d-xo.html, included in the description is the code to create the table.
Hi Beardeddev, from where I can get this Sales Database ?
Actually I want to do hands on along with your tutorials.
Would be glad if you provide the link of the same :)
Hi, thanks for your comment, I no longer have the database but if you need a demo database to work with you can download AdventureWorks, I’m going to being uploading a video on how to download and restore soon.
@@BeardedDevData Okay no issues.
I’m going to do my best to make sure everyone can follow along in future videos, whether that be using AdventureWorks or creating my own, if you let me know if you have any difficulty I will help you through the examples.
@@BeardedDevData Yeah actually it should be like that only, so that everyone can follow with you and can do hands on simultaneously. So, for now am practicing it with the databases that I already have, so yeah thanks a lot for your quick revert !!
Is it anyway possible to have a copy of the database that you used for the demo
Hi, unfortunately I no longer have the database but as so many people have requested it I’m going to upload a new copy that people can follow along to. I will upload it this weekend.
Thanks Dev.... But I managed with the AdventureWorks database
Good video. Thanks. Can we use where clause on aggregate function (eg: select count(*) over(partition by department_id order by department_id)no_emp ,e.* from employees e where count(*) >3;
Unfortunately you can’t within the WHERE clause as this is evaluated before the aggregations are applied. If you need to filter based on an aggregate function you have the option of HAVING COUNT(*) > 3 if using GROUP BY or use a CTE and then adding a WHERE clause when selecting from the CTE.
first time of seeing it, can decide to sub at the first time!
One feedback. next time when you record your videos try increasing the font size. I'm trying to watch this video on iPhone and its impossible to see the text on the screen. Why not increase the font size? you have 99% white space on the screen.
for an oldie like me, even watching on a PC, I cant read anything.
can you provide your the create query as well so e can follow you along? loving your videos
Hi, I will put this up as soon as I can. Thanks for the feedback on the videos, let me know if there is a particular area you would like to see a video on.
Great Job. John Snow!
Wow, thank you so much!
You're welcome!
great lectures.
Can you share the link to the database info so we can connect as well.
Unfortunately I don't have it, but if you take a look at this video, th-cam.com/video/lBcDSsgp0RU/w-d-xo.html, the description includes the code to follow along.
Awesome video, thanks!
Thanks for the positive feedback.
Really hard to see your queries on mobile phone :(
mobile phone? not much better on a laptop!
Appreciate the feedback, I have enlarged the queries on my latest videos so hopefully this is an improvement. I am also looking in to some software that will allow zooming in to show everything more clearly on all devices.
very nice.
Love the accent.
Hey BeardedDev, I wanted to know the difference between Aggregate Function and Windows Function, Since they perform the same somewhat
That's a really good question and the answer is quite extensive. There is generally multiple ways we can achieve the same result working with SQL and if we put performance aside it really depends on what we are trying to achieve. If we are looking at applying aggregates to a whole data set or just one type of group then group by is more suitable, there is also the benefit of the having clause that we can apply if we want to see groups that meet a criteria. When working with window functions, they are part of the select statement so filtering based on criteria can be more difficult but if we want to return the underlying data, calculate running totals/averages, find next or previous value then they're are extremely useful. That being said we can also use both together, we can group our data using group by and find out the total of all groups by using a window function. Hopefully that helps clarify the difference and I will be doing some videos over the next couple of months focusing on performance and I will cover the differences between how group by and window functions are executed.
What if you wanted get the Sales_Details_ID on the row with an aggregate value (say MAX(Line_Total) ) within a Sales_Id group? So I want to look at each Sales_Id group, and get the Sales_Details_Id on the row with the maximum line_total. I've been looking for a way to do this without a CTE but that might end up being the only way, CTE + RowNumber() partition by sales ID
So what I want is something like
SELECT Sales_Details_ID, MAX(Line_Total)
FROM Sales_Details
GROUP BY Sales_ID
(This wouldn't work however because Sales_Details_ID would have to be in the GROUP BY clause
Can you tell me the relationship between sale_id and sales_details_id? The reason I ask is that if one sale_id can have many sales_details_ids related then when you group by sale_id which of the sales_details_ids would need to be returned? As one value can only be returned then you need an aggregate function on sales_details_id. If it’s a one to one relationship then you can group by sales_details_id.
@@BeardedDevData So in my case it's EmployeeID, EmployeeReviewScore, and ReviewDate. The relationship is one EmployeeID to many EmployeeReviewScore and ReviewDate. What I want is the most recent EmployeeReviewScore for each employeeID. So I would need to do something like this:
SELECT EmployeeID, EmployeeReviewScore, MAX(ReviewDate)
FROM table
GROUP BY EmployeeID
I only want to group by EmployeeID, but SQL Server syntax requires you to group by ALL non-aggregate fields. If I add EmployeeReviewScore to the GROUP BY clause, then I'll get multiple rows for each EmployeeID, which I don't want.
I've solved this by creating a CTE ordered by Date with a rownumber() partition and joining to that, but I was just wondering if there is a cleaner way I can do this without a CTE.
In the scenario you have explained a CTE is the perfect choice, to get the result you require you need to perform multiple operations on the data, first identify the MAX(review date) and then use that to identify the employee review score. Of course there are other ways to do it using other types of temporary objects but I like to use CTEs for this exact situation. In fact when somebody asks me what a CTE is and what’s it’s used for I would describe this exact problem.
@@BeardedDevData Okay, thanks for the advice and informational video
very useful video but its hard to see the queries you wrote or the results on SQL can you zoom out them I literally can not see anything
Thanks for the feedback, I will make sure I do this on new videos.
@@BeardedDevData You are very welcome
BeardedDev thank you, fair play.
Thanks, let me know if there are any any particular tutorials you would like to see.
Conditional window functions come to mind, like max(case when)over(...). as opposed to case when max()over(). They are very particular for sure. but its not something that I find well explained in many literature. But mostly the former is of interest.
very well explained
Thanks for the positive feedback.
can we get the script for the database which is being used in video for explanation?
I haven’t published the script anywhere, but I am going to make sure in my future videos that the scripts are available.
❤ Thank You
No problem.
How can we filter in these situations? I am trying to use a "WHERE" to only see Line_Count >= 3 but it's not working
Because the window function happens in SELECT or ORDER BY phases you cannot filter in the WHERE clause in the same statement. To do this you need to use a derived table, SELECT * FROM ( ) AS D WHERE Line_Total >= 3
not working over function in derby or sqlite getting error "
[Exception, Error code 30,000, SQLState 42X01] Syntax error: Encountered "PARTITION" at line 2, column 22."
SELECT SALES_ID,SALES_DATE,ITEM,PRICE,QUANTITY,LINE_TOTAL,
SUM(LINE_TOTAL) OVER(PARTITION BY SALES_ID) AS SALES_TOTAL
FROM SALES_DETAILS;
The syntax looks fine?
Thank you! But please use some lower screen resolution. Not everyone is able to watch this in HD quality.😢
Sure, thanks for the feedback.
Is there a link to the data used?
Hi, unfortunately I don't have the data but if you like to follow along, you can here: th-cam.com/video/lBcDSsgp0RU/w-d-xo.html
i'll have to watch peaky blinders before watching this
Can window function be applied to ORDER BY clause?
You can add a Window Function to an Order By clause but I cannot think of a use case where it would be beneficial, did you just want to know if it’s possible or do you have a particular example you are working on?
Paid 10k in tuition only to have to resort to youtube for an actual easy to understand lesson.
I’m not sure what currency that’s in but I’m guessing it’s a lot, I see a lot of training providers are in it for the money and don’t deliver good content. It’s part of the reason I set up the channel, more people are needed in the world of data and analytics and training should be readily available. Microsoft offer a lot of free training through Microsoft Docs and Microsoft Learn and edX are also quite good.
Sorry could anyone here help me out with the question about Window Functions, that: it's already include the ORDER BY, so is it necessary to explicit again when we back to the main query ORDER BY again.
For example:
SELECT sales_transaction_date, SUM(total_sales) OVER (ORDER BY sales_transaction_date) FROM sales ORDER BY sales_transaction_date;
Yes, the order by within the windows function does not impact the order of the results even if it seems to do so. You must add an order by at the end of the query to guarantee ordered results.
@@BeardedDevData Oh, thank you very much (Y)
bearededlegend
It is important to notice that the Sales_Date column in your DB has its value all truncated by days. So, if someone wants to do that with a more granular level of "datetime", this someone should learn how to truncate dates.
I wouldn’t use the term truncate personally, the Sales_Date column is a data type of DATE which has a granularity of day therefore you are correct if you are using a DATETIME data type then you will need to remove the time element and for that I would recommend CAST or CONVERT. I generally only use the term truncate when removing characters from a string or removing all rows from a table but I’m aware other RDBMS might refer to the operation as truncate.
@@BeardedDevData makes a lot sense. Thank you.
here's the full window function playlist for anyone interested: th-cam.com/play/PLgR-BOYibnN0QqIPFbMlS01bw8x9g07Ll.html
USEFUL