Bookmark Topic Watch Topic
 
friki data migration
Ranch Hand
Posts: 772
1
  • Mark post as helpful
  • send pies
  • Report post to moderator
Use window functions instead of aggregate functions, when possible

Before the introduction of window functions, the only means to aggregate data in SQL was by using a GROUP BY clause along with aggregate functions in the projection. This works well in many cases, and if aggregation data needed to be enriched with regular data, the grouped query can be pushed down into a joined subquery.

But SQL:2003 defined window functions, which are implemented by many popular database vendors. Window functions can aggregate data on result sets that are not grouped. In fact, each window function supports its own, independent PARTITION BY clause, which is an awesome tool for reporting.

Using window functions will:


  • Lead to more readable SQL (less dedicated GROUP BY clauses in subqueries)
  • Improve performance, as a RDBMS is likely to optimise window functions more easily


  • The Cure:

    When you write a GROUP BY clause in a subquery, think again if this cannot be done with a window function.

    Source


    SqlBestPractices
     
      Bookmark Topic Watch Topic
    • New Topic