SQL Tutorial - Window Functions - Calculate Running Totals, Averages
Вставка
- Опубліковано 15 лис 2017
- Another fantastic SQL Tutorial brought to you by BeardedDev.
If you are new to working with Window Functions check out this video:
• SQL Tutorial - Window ...
T-SQL Querying
www.amazon.com/gp/product/073...
T-SQL Fundamentals
www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
www.amazon.com/gp/product/073...
In this video we learn how to use Window Functions to calculate running totals and running averages. This video teaches about Window Frames:
Rows
Range
Preceding
Current Row
Following
Window Frames are a filtered portion of a partition.
Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012.
Window Functions can only be included within SELECT or ORDER BY clauses.
Functions Available:
Aggregate - COUNT, SUM, MIN, MAX, AVG
Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE
Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG
Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST
Windows Functions also have FRAMES
ROWS
RANGE
Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future.
SQL:
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total]
, CAST(AVG(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS DECIMAL(8, 2)) AS [Running Average]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date - Наука та технологія
Thank you for explaining everything clearly !!
#BeardedDev obviously understands some of the keys to learning, and employs that understanding to each video. That makes his presentations so valuable - and rare.
1. Step-by-step demonstration of the material.
2. Manageable content length.
3. The expectation that viewers will review the content until it sinks in.
#MVP, in my opinion. Thank you, Brad! 👍
Thanks for the support Houston.
Most underrated SQL channel ever
Thanks so much.
Fantastic Tutorial - exploring the murky depths of SQL for some truly useful Window functions! Great job!
Hi BeardedDev.
I have just watched a few of your SQL videos and I have to say they are the clearest explanations I have ever seen of some potentially complex concepts. Concise, clear, practical and easy to understand.
Great work! Thanks for sharing.
Thanks so much.
@@BeardedDevData you have a real gift for simplifying complex concepts in a concise way with great examples.
Liked and subscribed.
Just one thing I struggle with in this (not your fault, you didn’t invent the terminology!)
I struggle with a frame being smaller than a window - in my house the windows fit within the frames which means in my mind whenever I hear frame I visualise it as bigger than the window and have to fight to see it the other way round 🤣
The first video I've come across that simplifies the concept of running totals. It had always seemed difficult for me to grasp but thanks to your clear explanation, I now understand running totals (and averages)
Thanks so much, that's great to hear.
Thanks for the step-by-step explanation and practical examples, really helped me understand the concepts!
Thank you for the great video
Window frames: a frame is a portion of a partition, the window frame moves as u go to the next row if it’s defined in terms of rows preceding and rows following current row. The frame applies within each partition, not to the whole table, but if you want it to apply to whole table then don’t partition and the entire table is one big partition. For example, if partitioning by customer Id, then the window will be all the rows with that customer id and the frame inside the window can be defined as current row + 1 preceding (2 row frame inside the partition)
Aggregate functions applied at each row will only apply to the frame not to the partition
3:20 without partitioning, calculating running total n avg (whole table treated as one big partition)
For running total our frame will be current row and all the preceding rows to show total up until this point (unbounded preceding)
The aggregate function is applied to the frame only
11:15 partitioning so that the frame applies only inside the partition window
Excellent information
Thanks for the videos :). It made my life easy to understand window functions.
Binge watched your videos. They are awesome. Thanks
Didn’t skipped the ads to help u as much as possible
Thanks so much.
Thanks for these videos! Was really confused by these and the group by clause!
That’s great, thanks for your feedback.
Really astonishing
Outstanding!!!
Thanks for the video!
BD is my new goto for SQL knowledge!
Thanks so much, let me know if there’s anything else you will find useful, I’m currently working on the data engineering certification so haven’t been posting much lately but will be a lot more Azure data platform coming soon.
You explain things sooo clearly! Thank you!!
Thanks so much, that's exactly what I try to do.
This was good stuff!
Very well explained.
Thank you for explaining very clearly, specially running totals and running averages with examples, it made understood how I can apply that logic to my own dataset.
Again appreciated.
That's fantastic Kerim, that was always the intention, there is a big difference between following an example and being able to apply the logic to your own data, it's very pleasing to hear.
thanks for making this
Thanks alot. Great video!!!
Amazing thank you very much
Thank you so much!!! Very useful and clear videos!
Thanks so much, let me know if there's any areas you are particularly interested in, I'm going to be back doing videos soon.
Great tutorials hoping for more!
Thanks for the feedback, more coming next week.
Great tutorial. Easy to understand!
Thanks so much
Thanks sir 🙏
Great! Tks
you're a rockstar
Thanks so much.
Thank you.. very tidy
thanks!
Hey Dev, great video. As a suggestion, maybe you could omit recording the explorer tab inside SQL management studio. Can't see queries as clearly as I'd like.
Thanks for the suggestion, I will implement that in future videos
Hi Ankhang, I have recently published a new video, SQL - LIKE, ua-cam.com/video/sGbs-5nxWes/v-deo.html, can you have a look and let me know if you can see the queries more clearly.
@@BeardedDevData not Ankhang but let me say that it is perfectly seen...thank you
Great Video, Really helped me.
One tiny suggestion, In the title of the video, if you could add numerical order. It would help following the series much easier.
Thanks
agreed
Noice!
This solution breaks down if there are not any sales for a day: it would grab sales from a neighboring day instead of counting the missing day as zero.
How would you normalize the time series data to account for that?
The solution here is about running totals or averages rather than replacing values. Let's say we start our data on 01/01/2024 and sales were 0, on 02/01/2024 sales were 100. If we have a running total on 02/01/2024 it would be 100 taking into account the previous days sales of 0 and the current days of 100. We might want to do this if say we work for a company that has peaks of sales on weekends, we might want a rolling total of the last 7 days rather than peaks every weekend. All we have really done is change the scope of the dates considered in that particular column, this is easily communicated through column names such as rolling7daytotal. We can also add a description if we use a tool to output the data that allows us to, hopefully that helps.
Increase font size please. Straining my eyes!
Thanks DEv, it is a great video
please help me understand why have we used ORder by in the windows function despite it being present outside in the conditions
Sure, the ORDER BY in the outer query determines how the results are displayed, this is logically evaluated last which means it has no impact in what happens as part of the SELECT phase. The Window Function in this example is part of SELECT, the data has no order at this point, the operation to calculate a running total requires the data to be in order for it to be meaningful therefore we must add an explicit ORDER BY in the Window Function. It looks odd to have two ORDER BYs in the same query but they have different purposes, inner serves the purpose of passing the data in order to the calculation whereas outer serves the purpose of presentation.
Enjoying the video series on windows functions -- might have missed it mentioned elsewhere but is there a place to get the book shop sample database to practice the examples?
Unfortunately I no longer have the data which I know is less than ideal. You can try using a database like AdventureWorks to follow along and if you need any assistance please let me know. I have included code samples in my latest videos.
@@BeardedDevData thanks for the reply
Hi Bearded Dec, Your tutorials are a life-saver! Thank you. I wanted to ask, for this table, is it possible to create a single customer view for the entire table? And if so, how? If you don't mind.
Sure, you can create a view by using the syntax: CREATE VIEW [name] AS [query], you will then just be able to select from the view. You just need to put your logic within the query with any aggregations or window functions. An important point to note though is that you cant have an ORDER BY in the query within a view unless TOP or OFFSET/FETCH is used, this is different to the ORDER BY in the window function, hopefully that makes sense, if not if you can give me more detail about what you want to achieve and I can help
@@BeardedDevData thank you. Will try it out when I'm done with your Window series. If I have a problem I'll add another comment. Thank you.
No problem 👍
hi when we use rows between 1 or 2 or n and current row...which row is supposed to be the current row
The current row is what we use as a starting point, for example if we are on row 3 and are looking at rows between 1 preceding and 1 following we need to look at row 2 for preceding and row 4 for following but row 3 is current row. Let me know if you need any further clarification.
thank you for great sql tutorials. quick question. Say we want to have a running balance of a customer. the problem is the column which shows how much customer spent or loaded to the card is always positive. Its sign is dependent on another column code. say if that column is A then Positive and B then negative (Like Case). How we can write this in a window function. is there a way to use case inside window function?
That's a very interesting scenario, you can put a case within a window function so you could have something along the lines of when that is negative multiply the value column by -1, this can get a bit messy though in terms of readability, my suggestion would to use a derived table or CTE to perform the case then apply the window function on the derived table or CTE.
@@BeardedDevData that makes allot of sense. I was thinking to create a materialized view for it as its a table that we use in everyday reporting. I have a feeling that a 200 line sql code that my co-worker wrote for a reporting can be written with a window function in a few lines.
Hi BeardedDev,Could you please explain how running total for sales_id 83 is 140.98 when unbounded condition is changed from unbounded to 2? Thanks in advance
Sure, when we use unbounding preceding and current row, this means add up the sales_total for all the previous rows and the current row, the calculation is 84.30 + 84.30 + 46.69 + 9.99 = 225.28, when we change it to 2 preceding and current row, this means add up sales_total for the previous 2 rows and the current row, the calculation is 84.30 + 46.69 + 9.99 = 140.98
Any tutorials on incorporating window functions into Tableau with parameters etc?
Would you be passing the parameter to the database? If so, there are two objects you can create, an inline table-valued function or a stored procedure providing you can call these from Tableau. Alternatively you could load data and perform the logic in Tableau, I haven’t used it for a very long but I can do it in Power BI and Qlik.
How do I create a column categorizing the sum of sales - for example - by every three rows? So every three rows of sales will have their own collective sum in a particular column.
Hi Josh, hope you're well, if you want to get a rolling total for three rows you can change the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, however I don't think this is quite what you are after. If you want to have the same amount for the sum of rows 1 - 3, 4 - 6 etc. you need a way of identifying these rows within a partition, if this doesn't exist you will have to add something, NTILE would be an option but if your data grows so will the number of tiles. Another option would be to create a new column based on the PRIMARY KEY such as SELECT CEILING(PK / 3.00) AS PartitionId - this creates a new id column that will be 1 for the first 3 rows, 2 for the second 3 rows etc, you can then SUM(Sales) OVER(PARTITION BY PartitionId ORDER BY PK) AS PartitionSum, this will involve multiple operations on the data so you will need to make use of a derived table or CTE.
@@BeardedDevData thank you!
Thank you for the tutorial! Can you please help with a question about the difference between rows and range? I recently came across a case - when using 'Sum(value) over (partition by id order by month range between 2 preceding and current row) as total_value' - the result table is correct, whereas using 'Sum(value) over (partition by id order by month rows between 2 preceding and current row) as total_value' - the result table is wrong. I assume that this is because the months for the ids are not consecutive like 1,2,3,4,7, but I couldn't find a valid explaination. Could you please help?? Thank you.
It's a good question, you will probably find you have duplicate values for month in your data. An example simple table would be Month, Amount, 1, 20, 2, 30, 2, 40, 2, 50, if you use rows between 2 preceeding and current row, you will end up with 20, 50, 90, 120, if you use range you will end up with 20, 140, 140, 140. This is because rows will only take in to consideration rows, doesn't matter what value month is, if you use range it will add the values of all months that are the same regardless of amount of rows. Hopefully that makes sense, I will post a video on it as soon as possible.
@@BeardedDevData Thank you so much for your reply! But this case is different - the table is as below - ["Id", "Month", "Value"] - [[1, 1, 20], [1, 2, 30], [1, 3, 40], [1, 4, 60], [1, 7, 90], [2, 1, 20], [3, 2, 40], [3, 3, 60]] --- Used Sum(Value) over (partition by id order by month range between 2 preceding and current row) to calculate the cumulative value of the previous 2 month and the current month. For id 1, no value for months 5 and 6, so in the result table - id 1 month 7's value is 90(90+0+0). But from what I understand, the previous 2 rows being added up should be months 3 and 4?
It's because there is a gap in the data, when using range, you are stating include id 7 - 2 to 7 but because there is no data for 5 and 6 you will only get the total for 7, if you change it to rows it should include 3 and 4
You can test by adding values for months 5 and 6 and see how this impacts the value, this makes sense when ordering by numeric values because the gap can be detected but I wonder how this would impact ordering by a string column
@@BeardedDevData Thank you so much. I will give it a go and see what will happen!
Hey what if we wanted to exclude current row while calculating averaging
You would have to change the frame of rows that are involved, ORDER BY [column] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, this is a fantastic idea for a video though as changing the frame is very important
@@BeardedDevData I figured it out 😀 thanks for the reply
How to count distinct userId in window function?
@LoveJ66
Try this and let me know what happens:
,MAX(UserID) OVER(ORDER BY UserID DESC) AS [Distinct ID Count]
Hello BeardedDev A-M-A-Z-I-N-G tutorials bro! Keep up the good work ^_^ .. I just have a comment, I think *ROWS UNBOUNDED PRECEDING* is useless if we want to calculate running total/average from the first row in the partition to the current row, I mean we can omit it and get the same results, right?
No, it you omit ROWS UNBOUNDED PRECEDING then you will get the total for the partition not the running total, if you need the running total then you must define a frame, which is where we use ROWS UNBOUNDED PRECEDING, you can omit CURRENT ROW as that is implied.
BeardedDev Alright, thanks for clarification
This is so helpful. I have a trouble adding negative and positive values as I have hire and termination at the same time. the hire are positive numbers and the termination are negative numbers: for example: 402+35 gave me 437 while the next row 402+36 bring it to 876, 402+37 gave me 878 and 402+38 gave 440.
also 6+(-1) gave me 5 while 10+(-1) gave me 18. Any help please?
I tried to test if it is positive then number1+ number 2 if it is negative then number 1 - abs (number 2) and same issue
Do you have positive and negative numbers in the same column? And are you calculating a running total or running average?
@@BeardedDevData my table has all employees who get hired or terminated between a start date and an end date. i also have the count at the start date.
If it is hire then 1 if it is termination then -1 . I am using Running total and the issue appear for example in a case i have termination (-1) so running total take -1 then hire (1) so running total take the value 0 then termination -1 which supposed to be -1 but the running total took 1. Also i have a column for variation number and it is not calculating correctly as Count at start=10, running total=-1 and variation show 18 :(
Are you able to send me your query and a screenshot of the data? If needed, my email address is askbeardeddev@gmail.com
@@BeardedDevData sent. Thank you
@@BeardedDevData it said address not found
Everything is great except that no zooming in which makes it difficult for me to see and understand
I can understand that, I have got some new screen recording software that allows me to zoom in, if you want to look at a video on window functions with zooming I have done one here: m.ua-cam.com/video/lBcDSsgp0RU/v-deo.html
please zoom the query bcoz it will be more clearer you are showing whole screen its taking so time
Appreciate the feedback, have added zooming to my latest videos
@@BeardedDevData Thnx Buddy And Great Content ❤️
great tutorial I like your style. But the table you worked on is not attached so I am giving it 6/10, thumb down. Please upload the table. Like now the video is below modern standards because it's hard to follow what you talked about.
Appreciate that, the idea behind was that if I show you how to do something and you can apply it with your data then you've learnt something, if I give you the data and you follow what I'm doing all you've done is follow a tutorial. I understand people might like to test things out first though so will take that on board.