Solution: Moving average - SQL教程

- [Instructor] Here's how I solve this challenge. As with the previous solution, I'm using CTE notation here to save on typing. And just like in that one, we're using our three tables here to get the values we need for this challenge. We're joining orders with dishes through the orders dishes linking table. That's the same as before. So, I won't spend time here going through all that again. Let's focus on what's different in this challenge. At the beginning, we're returning the customer ID, the order ID, and the order total for each order from the orders table as the challenge requested. And here, we're taking the average of the sum of the prices of the dishes in the window we define down here. That's being rounded to two decimal places which the challenge also asked for and it's called moving average. We're partitioning the table by customer ID so we treat each group of records for the same customer ID together. And within each of those partitions, we're sorting by order ID. We get ascending order as the default, which makes sense if we're basing this on order IDs through time. As we take orders, the order ID increases. So higher order ID numbers are more recent and it will appear lower down in the table than older orders. And we're using the window of rows between 2 preceding and current row. That is we're getting the price value of the current row, the one above it, and the one above that in order ID sequence for each customer ID. Here's what that looks like using a little piece of the table. Partitioning by customer ID puts all the records that have the same customer ID together and then sorting by order ID ascending makes them appear in numeric order per customer. That happens to be in chronological order because of the way our database creates the records for our orders. And our window looks like this. Well, not exactly just yet, but it will. That's the 2 preceding and current row from the query. Let's start with just the first record, though. When we start working with a new customer window whether that's customer ID 1 or customer ID 77, our window starts at the top of each partition. The result here for the first row of each customer will be the first order total divided by one because there's only one row in our window so far and that will return the same price. The second row will be the current row plus the previous row divided by two because now there's two rows in our window. And when we get to the third row, this value will be the price for the current row plus the price for the previous row plus the price for the row above that divided by three. At this point, we have three rows in the window and this pattern will continue with a window of three rows for the rest of each customer's orders. When we reach the end of a customer's order records, we move on to the next customer and start the process again. Back here in the query, we finish up by grouping and ordering by the same values in our window to keep the results organized how they should be. Okay, I'll run this and we'll see if it matches the solution. It does. That's good. Window functions are useful in certain scenarios and can be a little bit tricky to think about. But with knowledge of window functions, you'll be able to dive into data analysis and other advanced database tasks.