TOP 25% rows by Weightage
-
I have a table as below.
I need to find the members which are in the bottom quartile (25%) of the weights in each class
ClassIDMemberIDWeight
A110
A220
A340
A525
A615
A810
B150
B210
B360
B6400
For above example, total weight of class A is 120. If I order by weight and pick the top ones such that their summed weight comprise less than or equal to 25% of total weight (25% of 120 = 30 for class A)
I would pick Members 1 and 8. If I include member 6, it would exceed 25% (30)
The output I need is
ClassIDMemberIDWeight
A110
A810
B150
B210
B360
What would be the best way of achieving this in TSQL (Sql server 2008)?
Hope the question is clear
-
You can do this using a running total. Take a look at this article that explains how to do a running total in sql.
http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
If you get stuck feel free to post back here. It is best if you can provide data in a consumable format. Since you are brand new around here I did this for you so you can see.
;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
If you're using SQL Server 2012, you can do this
;with Data(ClassID, MemberID, Weight) as(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
),
CTE AS (
SELECT ClassID,MemberID,Weight,
SUM(Weight) OVER(PARTITION BY ClassID ORDER BY Weight) AS RunningTotalWeight,
SUM(Weight) OVER(PARTITION BY ClassID) AS TotalWeight
FROM Data)
SELECT ClassID,MemberID,Weight
FROM CTE
WHERE RunningTotalWeight <= TotalWeight/4
ORDER BY ClassID,MemberID;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks very much guys...Unfortunately I'm still on 2008
I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article
;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
)
--SELECT* FROM Data ORDER BY ClassID,Weight
SELECT d.classid,d.memberid,d.Weight
FROM Data d
INNER JOIN (
SELECT D2.ClassID,SUM(Weight) as total_weight
FROM Data D2
GROUP BY D2.ClassID
) Totals ON Totals.ClassID = d.ClassID
WHERE
(SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight)
<= 0.25*Totals.total_weight
Help is much appreciated
-
chintan.floydian (4/18/2013)
Thanks very much guys...Unfortunately I'm still on 2008I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article
;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
)
--SELECT* FROM Data ORDER BY ClassID,Weight
SELECT d.classid,d.memberid,d.Weight
FROM Data d
INNER JOIN (
SELECT D2.ClassID,SUM(Weight) as total_weight
FROM Data D2
GROUP BY D2.ClassID
) Totals ON Totals.ClassID = d.ClassID
WHERE
(SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight)
<= 0.25*Totals.total_weight
Help is much appreciated
There are a few things you would have to do differently here for the quirky update to work. You would need to move this to a table instead of a cte. You need to have a clustered index as described in the article. The idea here is that you could use the quirky update to get your running total and then select rows where the total column is less than max of that column * 25.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply