Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a formal/academic name for the family of functions in Sql Server like ROW_NUMBER(), LAG, LEAD, RANK etc, that make use of the OVER(ORDER BY <PARTITION BY> ) syntax? How much of this is covered by the ansi standard?

I can see on MSDN that some of those are listed as analytic functions, but others are ranking functions.

I ask using Sql Server names, but one of the reasons I want to know is to help me find out how to translate some of these queries for other databases, and gauge support among those databases. The other reason is to help me wrap my head around the concept more completely. Most of my experience pre-dates their Sql Server use, and I find lately I'm too used to old workarounds when one of these functions might be the easy fit. Knowing how to refer to them will help me when reading other things.

share|improve this question
1  
They were called 'analytic' functions on Oracle for many years, but 'window(ing) functions' is the more generic term. –  Jack Douglas Jul 30 at 16:23
    
@JackDouglas: "window functions" is what the SQL standard uses. –  a_horse_with_no_name Jul 30 at 16:58
    
@a_horse yes, they chose that term in SQL2003 over the Oracle term that pre-dates it. Fwiw I think it was the right choice, it's far more descriptive. –  Jack Douglas Jul 30 at 17:03
    
I've just suggested the "analytic-functions" as a synonym of "window-functions", at SO. (113 and 213 questions respectively) –  ypercube Jul 30 at 17:05

1 Answer 1

up vote 7 down vote accepted

I believe the term you are looking for is window functions. Most databases have support for these types of functions.

Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on.

Windowing functions were added to the standard SQL:2003 that is managed by the ISO and it was specified in more detail in SQL:2008 For some time, other DBMSs such as Oracle, Sybase and DB2 have had support for window functions. Even the open source RDBMS PostgreSQL has a full implementation. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012.

Source

A window function is one of:

  • A rank function.
  • A distribution function.
  • The row number function.
  • A window aggregate function.
  • The ntile function.
  • The lead function.
  • The lag function.
  • The first-value function.
  • The last-value function.
  • The nth-value function.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.