CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
n8.
n8.

Reputation: 1738

Bucketing percentiles

Something I have an ongoing demand for is putting personnel into "buckets" based on certain performance metrics. NTILE doesn't do what I want since it splits the population into equal divisions. Graphically, you can see what I want here:

Percentages

This essentially comes up with each person's position so that management can categorize them. Here you can see the "top" 10 percent of qty is provided by 12 or 13 people (where the "cumulative" column reaches 10). Not pictured is the "bottom" 10 percent, which is populated by over 200 people. I am currently accomplishing this with Excel, but I would prefer to keep the complexity on the server side.

Here is how I get to percentage:

SELECT PREmain.*
     , PREtotal.[Qty] [Total Qty]
     , (PREmain.[Qty] / PREtotal.[Qty]) * 100 [Qty Percentage]
FROM PRE PREmain INNER JOIN
     (SELECT [Week]
            ,[Year]
            ,SUM([Qty]) [Qty]
      FROM PRE
      GROUP BY [Week]
              ,[Year]) PREtotal ON PREmain.[Week] = PREtotal.[Week]
                               AND PREmain.[Year] = PREtotal.[Year]

...where "PRE" is populated by another query. Can somebody tell me how I can accomplish this?

edit

I am currently stuck with 2008 R2, but the below answers will hopefully provide a catalyst to rationalize a 2012 upgrade.With the suggestions below I searched for "SUM OVER SQL 2008" and found this:

SQL Server 2008 using SUM() OVER(ORDER BY...)

This applies to 2008 and, modified, worked for me. Here's my query:

,

PRE2 AS (
    SELECT PREmain.*
         , PREtotal.[Qty] [Total Qty]
         , (PREmain.[Qty] / PREtotal.[Qty]) * 100 [Qty Percentage]
         , PREtotal.[Gross Sales] [Total Gross Sales]
         , (PREmain.[Gross Sales] / PREtotal.[Gross Sales]) * 100 [Gross Sales Percentage]
         , PREtotal.[Net Sales] [Net Sales Qty]
         , (PREmain.[Net Sales] / PREtotal.[Net Sales]) * 100 [Net Sales Percentage]
    FROM PRE PREmain INNER JOIN
         (SELECT [Week]
                ,[Year]
                ,SUM([Qty]) [Qty]
                ,SUM([Gross Sales]) [Gross Sales]
                ,SUM([Net Sales]) [Net Sales]
          FROM PRE
          GROUP BY [Week]
                  ,[Year]) PREtotal ON PREmain.[Week] = PREtotal.[Week]
                                   AND PREmain.[Year] = PREtotal.[Year])

SELECT a.*, x.s [Cumulative]
FROM PRE2 a
     CROSS APPLY (   SELECT ISNULL(SUM(v), 0)
                    FROM (  SELECT b.[Qty Percentage]
                            FROM PRE2 b
                            WHERE b.[Qty Percentage] < a.[Qty Percentage]
                              AND a.[Week] = b.[Week]
                             ) x(v)
                ) x(s)
ORDER BY a.[Qty Percentage]

It took 3 minutes to get 1719 results. Kind of slow, but it's what I've got to work with until I can upgrade to 2012.

Update:

It's way faster with TABLE variables. Runs in about 4 seconds.

Upvotes: 1

Views: 743

Answers (2)

hines
hines

Reputation: 278

You should be able to accomplish the cumulative total with an analytic function which sums all preceding rows. I wasn't sure which columns were included in PREmain so I used * in the select statement which wrapped around your original query. 

SELECT 
    *, 
    SUM([Qty Percentage]) OVER (ORDER BY [Qty Percentage]) as Cumulative
FROM (SELECT PREmain.*
             , PREtotal.[Qty] [Total Qty]
             , (PREmain.[Qty] / PREtotal.[Qty]) * 100 [Qty Percentage]
        FROM PRE PREmain INNER JOIN
             (SELECT [Week]
                    ,[Year]
                    ,SUM([Qty]) [Qty]
              FROM PRE
              GROUP BY [Week]
                      ,[Year]) PREtotal ON PREmain.[Week] = PREtotal.[Week]
                                       AND PREmain.[Year] = PREtotal.[Year]
    ) a

Upvotes: 1

sqluser
sqluser

Reputation: 5672

I just make a sample table with the output you provide and then you can expand it to your own query

If you are using SQL Server 2012+, you can have SUM() with OVER() clause

CREATE statement

CREATE TABLE tbl(Id INT IDENTITY(1, 1), Staff INT, QtyPercentage DECIMAL(10, 9))

INSERT statement

INSERT INTO tbl VALUES  (1855005, 0.968992248)
                       ,(182806, 0.904392765)
                       ,(429406, 0.839793282)

Query

SELECT Staff
       ,QtyPercentage
       ,SUM(QtyPercentage) OVER(ORDER BY Id) AS Cumulative
FROM tbl

Output

Staff   QtyPercentage   Cumulative
1855005 0.968992248 0.968992248
182806  0.904392765 1.873385013
429406  0.839793282 2.713178295

SQLFiddle

Upvotes: 1

Related Questions