Question on Window Function

Question

Hello,

Would you please help me with this question?

Thank you in advance,
Michael

Answer 1

@Mikhail Firsov

Here is how you need to read the query. What you understand already is that your over clause partitions the data by warehouse. What you are not understanding is the Order by clause.

The Order by clause states that the window your aggregates acts on starts with the value in the Product table and goes back to the beginning.

Let's take the San Francisco warehouse. The first three rows have a window that includes all three of these rows since iPhone is the first value in the order by.

What is the min quantity in that window? 10
What is the sum of the quantity in that window? 260

Then look at the next two rows. The window includes all rows that have a product Samsung or have a value that is less than Samsung. That means that the iPhone rows are included in the window.

What is the min quantity in that window? 10
What is the sum of the quantity in that window? 560

Can you insert a row for San Francisco for product Samsung with a quantity of 5? If you did then this would be the results:

For iPhone the min would be 10
For Samsung the min would be 5.

Answer 2

P.S. Is it because of this?

"If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not."

Answer 3

Hello RusselLoski-0634,

Thank you so much for your clarification - I did understand!!!

Regards,
Michael

Answer 4

Hi @Mikhail Firsov ，

You can also refer to:
SELECT - OVER Clause (Transact-SQL)

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Hi EchoLiy-msft,

I've already done it - and haven't found it of much help :(