Monday, December 28, 2015

Analysis with SQL Server 2012

Hello Readers,

Today I would like to share some information regarding analysis with SQL Server 2012.

We all have huge data inside our application. Like an e-commerce website will have orders, customer viewing history, etc. For a normal admin of your website this is just data and not much usable for him. However when you go to a higher level management, you will need analysis.

Suppose a managing director of the e-commerce website, customer would like to compare his product's comparison current vs previous month or year to identify the trend.

As a developer, your job is to provide meaningful data to the customer. We can always write traditional queries and then tweak them to our needs. Now if there are functions which will give you basic data required for the analysis, it will give you lot more time to work on actual tasks instead fumbling on tweaking your queries.

Considering above challenges Microsoft has introduced a few Analytic functions into SQL Server 2012.

Below are the list of few such functions.

  • CUME_DIST
  • FIRST_VALUE
  • LAST_VALUE
  • LEAD
  • LAG
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • PERCENT_RANK
We will look into these functions in my future post.

Thanks for reading,
Nirav