Tuesday, April 12, 2016

Quick Reference For Ranking and Sums with Ranking

/*
Developer: Christopher Harsch

A quick and simple example of ranking and summing functions against a rank function.

Ranking Functions in SQL (some syntax only applies to 2012 sql server)

https://msdn.microsoft.com/en-us/library/ms189798.aspx

-- This shows the use of over clauses in sql which are used below.

https://msdn.microsoft.com/en-us/library/ms189461.aspx

*/

IF Object_id('tempdb..#tmp_rank') IS NOT NULL
  BEGIN
      DROP TABLE #tmp_rank
  END

CREATE TABLE #tmp_rank
  (
     myid   INT,
     value1 VARCHAR(255),
     value2 VARCHAR(255),
        DecimalValue decimal(18,2)
  )

INSERT INTO #tmp_rank
            (myid,
             value1,
             value2,
                      DecimalValue)
SELECT 1,
       'A',
       'B',
          10
UNION ALL
SELECT 1,
       'A',
       'C' ,
          10
UNION ALL
SELECT 1,
       'A',
       'D' ,
          10
UNION ALL
SELECT 1,
       'A',
       'B' ,
          10
UNION ALL
SELECT 2,
       'B',
       'B' ,
          10
UNION ALL
SELECT 2,
       'B',
       'B' ,
          10
UNION ALL
SELECT 1,
       'C',
       'D' ,
          10
UNION ALL
SELECT 1,
       'D',
       'B' ,
          10
UNION ALL
SELECT 1,
       'C',
       'A' ,
          10

IF object_id('tempdb..#tmp_ranks') IS NOT NULL
BEGIN
   DROP TABLE #tmp_ranks
END

SELECT *,
       Rank()
         OVER (
           partition BY tr.value1
           ORDER BY value2) AS myRank        -- Ranks with numbering being numerically continuous IE 1,1,3,4
       ,
       Dense_rank()
         OVER (
           partition BY tr.value1
           ORDER BY value2) AS mydenserank        -- This provides contiguous rank numbering within ranked sets, 1,1,2,3
       ,
       Row_number()
         OVER (
           partition BY tr.value1
           ORDER BY value2) AS myrownum -- Individually numbers
       ,
       Dense_rank()
         OVER (
           partition BY 1
           ORDER BY value1) AS myDenseRankgroupnumber        -- by partitioning by a static number you can use it as a group number.
       ,
       Rank()
         OVER (
           partition BY 1
           ORDER BY value1) AS myRankGroupNumber        -- Ranks with numbering being numerically continuous IE 1,1,3,4 not the best for group numbers
       , sum(DecimalValue) over(partition by myid) as SumForAllInId  -- Lets you sum by a grouping
       , sum(DecimalValue) over(partition by myid,value1) as SumForAllValue1InID -- Lets you sum within groups and sub groups
into #tmp_ranks
-- Individually numbers within a partitioned set.
FROM   #tmp_rank tr
-- This shows how you can use a two step ranking, then summing process.
-- In later versions of SQL this can be moved upward into the above query.
select
*
, SUM(1) over (Partition By myDenseRankgroupnumber) as  TotalInDenseGroup -- Count total records in a group that was created by the dense rank
, SUM(DecimalValue) over (Partition By myDenseRankgroupnumber) as  SumTotalInDenseGroup -- Sum records in a group that was created by the dense rank
from #tmp_ranks