March 24, 2015 at 5:20 pm
Hi all,
I have the following script. I need to calculate percentile by DEPT. I looks like I can do it for all, but how do I add DEPT to it?
CREATE TABLE TT (Id INT IDENTITY(1,1), DEPT VARCHAR(10), DATA INT)
INSERT INTO TT VALUES('B',1)
INSERT INTO TT VALUES('A',1)
INSERT INTO TT VALUES('A',2)
INSERT INTO TT VALUES('A',3)
INSERT INTO TT VALUES('A',4)
INSERT INTO TT VALUES('A',5)
INSERT INTO TT VALUES('A',6)
INSERT INTO TT VALUES('A',7)
INSERT INTO TT VALUES('A',8)
INSERT INTO TT VALUES('A',9)
INSERT INTO TT VALUES('A',10)
INSERT INTO TT VALUES('B',1)
INSERT INTO TT VALUES('B',2)
INSERT INTO TT VALUES('B',3)
INSERT INTO TT VALUES('B',4)
INSERT INTO TT VALUES('B',5)
INSERT INTO TT VALUES('C',1)
INSERT INTO TT VALUES('C',2)
INSERT INTO TT VALUES('C',3)
SELECT * FROM TT
--90th percentile
SELECT ((
SELECT TOP 1 DATA
FROM (
SELECT TOP 90 PERCENT DATA
FROM TT
WHERE DATA IS NOT NULL
ORDER BY DATA
) AS A
ORDER BY DATA DESC) +
(
SELECT TOP 1 DATA
FROM (
SELECT TOP 10 PERCENT DATA
FROM TT
WHERE DATA IS NOT NULL
ORDER BY DATA DESC
) AS A
ORDER BY DATA ASC)) / 2.0
Thanks,
March 25, 2015 at 2:54 am
I'm not exactly sure I get your requirement, but you might try looking at NTILE for this:
SELECT *
FROM
(
SELECT *, nt=NTILE(10) OVER (PARTITION BY DEPT ORDER BY DATA)
FROM TT
) a
-- for 90th percentile
WHERE NT > 9; -- OR: WHERE NT < 2 FOR 10th percentile
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 25, 2015 at 11:33 am
Thanks for reply, but I wanted the result to state:
A 9.5
B 4.5
C 2.5
as I think it is 90th percentile for each DEPT
March 25, 2015 at 12:16 pm
I adapted some code by Itzik Ben-Gan to generate the values you need. The original code and explanation is posted in here: http://sqlmag.com/t-sql/calculate-percentiles
DECLARE @mark-3 AS INT;
SET @mark-3 = 90;
WITH MarksRnkCnt AS
(
SELECT DEPT, DATA,
RANK() OVER(PARTITION BY DEPT ORDER BY DATA) AS rnk,
DENSE_RANK() OVER(PARTITION BY DEPT ORDER BY DATA) AS drnk,
COUNT(*) OVER(PARTITION BY DEPT ) AS cnt
FROM dbo.TT
)
,PctRanks AS
(
SELECT DISTINCT
DEPT,
drnk AS rownum,
DATA,
1.*(rnk-1)/(cnt-1) AS pctrnk
FROM MarksRnkCnt
)
,PctRankRanges AS
(
SELECT
Cur.DEPT,
Cur.rownum,
Cur.DATA AS mark_from, Nxt.DATA AS mark_to,
Cur.pctrnk AS pctrnk_from, Nxt.pctrnk AS pctrnk_to
FROM PctRanks AS Cur
JOIN PctRanks AS Nxt
ON Nxt.rownum = Cur.rownum + 1
AND Nxt.DEPT = Cur.DEPT
)
SELECT DEPT,
CASE @mark-3
WHEN pctrnk_from THEN mark_from
WHEN pctrnk_to THEN mark_to
ELSE (mark_from + mark_to) / 2.
END AS pctrnk
FROM PctRankRanges
WHERE (@mark/100. > pctrnk_from AND @mark-3/100. <= pctrnk_to)
OR (rownum = 1 AND @mark-3/100. = pctrnk_from);
March 25, 2015 at 12:30 pm
Of course, everything becomes easier on 2012+ with PERCENTILE_DISC and PERCENTILE_CONT analytic functions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply