0

If we would consider window functions as an extension to vanilla SQL, what is their advantage over it?

Can you perform queries and things that wouldn't be possible with 'plain' SQL?

microwth
  • 1,016
  • 1
  • 14
  • 27
  • 2
    Window functions have been a part of ANSI SQL for almost two decades. Why would you consider them an extension? But a simple answer to your question is that they are more concise, faster, and expand functionality of basic SQL. Perhaps that's why they are in the standard. – Gordon Linoff Jul 19 '16 at 21:45
  • 'extension ' was used as a way to disambiguate the scope of the question;if you can have the same outcome without using window functions – microwth Jul 19 '16 at 21:48

1 Answers1

1

The simple answer is "yes", you can do things that wouldn't be possible otherwise -- at least not in a single query.

A simple example is row_number(), which you cannot replicate (in a single select) on a table that has no unique keys.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    And replicating row_number on a table *with* unique keys whilst possible would likely be horrifically inefficient with a triangular join with quadratic complexity. – Martin Smith Jul 19 '16 at 21:49