April 18, 2017 at 4:19 pm
Sean Lange - Friday, April 14, 2017 7:06 AMNot sure if it is a lack of coffee or not but I am 99.99% certain I chose the correct answer. In my mind I certainly did but perhaps I clicked the wrong one. I have always wished the QOTD page would show your select in addition to the correct answer. Good question Steve.
I too was 99.9% sure I checked the right box amd got told it was wrong.
But there is a simple explanation: the third option is wrong and the 6th option is right because the default is a RANGE specification, not a ROWS specification, so that what the result will be is unambiguous even if the order specified is not a total order (with ROWS what the result is depends on what the total order generated by the sort based on the order by sub-clause is, and that is indeterminate - it depends on which sort algorithm the optimizer chooses - unless the specified order is total, in which case ROWS and RANGE are both the same).
I guess neither you nor I was looking at whether it said RANGE or ROW, Sean, just at the window bounds.
Anyway, I wasn't and it's my fault I didn't read on to the fourth option which would have reminded me that the default wasn't just the frame bound expressions but also how CURRENT ROW (the default for the upper frame bound) is to be interpreted when the ordering makes that relevant. That also accounts for the appalling result with only 28% correct so far - with 23% choosing option 3. If the three RANGE options had been first insted of the three ROW options that 28% would probably have been 51%.
Tom
April 18, 2017 at 5:29 pm
Jeff Moden - Monday, April 17, 2017 3:37 PMdrew.allen - Monday, April 17, 2017 1:33 PMJeff Moden - Friday, April 14, 2017 7:58 AMI know what the correct answer is according to BOL... I just happen to disagree with it because functions like MAX won't be limited to just the preceding and current rows.You can disagree all you want, but the MAX is limited as soon as you add an ORDER BY clause and that limit defaults to the answer marked as correct in the question.
Drew
Edited to remove answer to question.
Ok... so explain how that might work if the current row isn't the max row. I still think that BOL has explained it incorrectly.
Jeff, the whole point of window functions is that they allow us to do aggregates (and other things) over each window instead of over the whole table, so that you can get things like movingaaverages, moving maxima, moving counts for example over overlapping 3 month periods with startdates seperated by say 1 month (or if you want, by just it by 1 day) for a whole year with very simple code. Writing it will be fast compared with messing about with GROUP BY repeated 3 (or 92) times with that many different WHERE clauses, and running it will be one hell of a lot faster than that too. So I hope BOL has explained that bit correctly, as otherwise these widowing things would be pointless.
Tom
April 18, 2017 at 5:38 pm
drew.allen - Tuesday, April 18, 2017 1:02 PMI do think that BOL is poorly worded, but the information is there.Quoting from OVER Clause
Note
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, <answer obscured> 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.
Drew
Well, the bit you quote does demonstrate that the windowing stuff wasn't fully thought out, because it's obviously useful if it can be used for ranking functions - ranking products by profitabilty is something people do, and the ability easily to see how that ranking fluctuates over a period of time is valuable, similarly ranking salesmen by sales volume or by customer satisfaction and watching it change can be useful.
I think "poorly worded" is a bit of an understatement - the description of the difference between RANGE and ROWS on that page is the sort of thing that can be used as an example of how NOT to explain something if you want to be understood by most of your readers.
Tom
April 18, 2017 at 8:12 pm
TomThomson - Tuesday, April 18, 2017 5:29 PMJeff Moden - Monday, April 17, 2017 3:37 PMdrew.allen - Monday, April 17, 2017 1:33 PMJeff Moden - Friday, April 14, 2017 7:58 AMI know what the correct answer is according to BOL... I just happen to disagree with it because functions like MAX won't be limited to just the preceding and current rows.You can disagree all you want, but the MAX is limited as soon as you add an ORDER BY clause and that limit defaults to the answer marked as correct in the question.
Drew
Edited to remove answer to question.
Ok... so explain how that might work if the current row isn't the max row. I still think that BOL has explained it incorrectly.
Jeff, the whole point of window functions is that they allow us to do aggregates (and other things) over each window instead of over the whole table, so that you can get things like movingaaverages, moving maxima, moving counts for example over overlapping 3 month periods with startdates seperated by say 1 month (or if you want, by just it by 1 day) for a whole year with very simple code. Writing it will be fast compared with messing about with GROUP BY repeated 3 (or 92) times with that many different WHERE clauses, and running it will be one hell of a lot faster than that too. So I hope BOL has explained that bit correctly, as otherwise these widowing things would be pointless.
Heh... Oh yeah... I absolutely DO understand that, Tom. In fact, I recently gave a 90 minute presentation on the subject that used such windowed aggregates to return totals across 5 different temporal partitions and calculate the "% of the whole" for 4 of those using each as a divisor and the 5th as a numerator along with a demonstration that "DRY" code can be achieved in cascading CTEs if you can instill a blocking function at just the right time on 7 million 1040 byte rows in 4 seconds flat and compared them to the horrendous run durations and resource usage that similar GROUP BY functionality had. (Please, no smites from word smiths or grammar teachers on the long sentence ๐ )
The point to my question isn't actually a question. It's an observation that the description in Books Online is seriously misleading if not incorrect altogether. ๐
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 11:33 pm
Jeff Moden - Monday, April 17, 2017 9:34 PMRevenant - Monday, April 17, 2017 5:37 PMSteve... are you trying to say that I should not trust BoL?
If not, what should be done about it? (Dead serious, although I do realize that you can be dead or serious.)
Or seriously dead. ๐
BoL reminds me of something that former President Ronald Reagan learned... "Trust but verify".
I have learned, through experience, to take what BOL states with a handfull of salt.
It is poorly worded, gramattically inconsistant and, in many cases, just plain wrong.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
โlibera tute vulgaris exโ
June 26, 2017 at 1:58 pm
If you change the word 'partition' with the word 'frame' in the question, the answer you suggest will be correct. Otherwise, is not. Partition size represent set of rows for a given team. Aggregate function MAX will return the same result for whole team.
So, my opinion is that the question is a little bit confusing and suggest wrong answer.
I suggest you to ask Mr. Itzik Ben-Gan for a opinion ๐. Although, I pretty sure that he will have the same opinion as me.
I'm a little bit late for discussion, because I recently dicover this quiz stuff.
And I have found that is very challenging and educational ๐
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply