December 16, 2014 at 12:32 pm
Hi, I'm trying to find the best way to break the following numbers up by quartile percentage, particularly the bottom 25%
I'm thinking of using a cte and row_number and selecting the top row numbers but am not having success ... any help would be appreciated.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
MainGroup varchar(50),
SubGroup varchar(50),
Value int
)
insert into #mytable
(MainGroup, SubGroup, Value)
select 'Temporary Assignments','Internal','783' union all
select 'Temporary Assignments','Internal','225' union all
select 'Temporary Assignments','Internal','118' union all
select 'Temporary Assignments','Internal','1014' union all
select 'Operation''s Support','Internal','2006' union all
select 'Operation''s Support','Internal','6352' union all
select 'Operation''s Support','Internal','186' union all
select 'Operation''s Support','Internal','186' union all
select 'Technical','external','186' union all
select 'Technical','external','367' union all
select 'Technical','external','142' union all
select 'Technical','external','46' union all
select 'Loyalty','external','5600' union all
select 'Loyalty','external','194' union all
select 'Loyalty','external','3679' union all
select 'Loyalty','external','48' union all
select 'Loyalty','external','5520' union all
select 'Technical','external','60' union all
select 'Technical','external','140' union all
select 'Loyalty','external','1287' union all
select 'Technical','external','261' union all
select 'Loyalty','external','5533'
December 16, 2014 at 12:37 pm
You need to provide more information. Do we assume you want to use the number? Do you want by group?
What code have you tried? What output do you want?
December 16, 2014 at 12:45 pm
Ok, I want the bottom 25% value of the 'Value' column, broken up by MainGroup ideally broken into 4 quarters.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
MainGroup varchar(50),
SubGroup varchar(50),
Value int
)
insert into #mytable
(MainGroup, SubGroup, Value)
select 'Temporary Assignments','Internal','783' union all
select 'Temporary Assignments','Internal','225' union all
select 'Temporary Assignments','Internal','118' union all
select 'Temporary Assignments','Internal','1014' union all
select 'Operation''s Support','Internal','2006' union all
select 'Operation''s Support','Internal','6352' union all
select 'Operation''s Support','Internal','186' union all
select 'Operation''s Support','Internal','186' union all
select 'Technical','external','186' union all
select 'Technical','external','367' union all
select 'Technical','external','142' union all
select 'Technical','external','46' union all
select 'Loyalty','external','5600' union all
select 'Loyalty','external','194' union all
select 'Loyalty','external','3679' union all
select 'Loyalty','external','48' union all
select 'Loyalty','external','5520' union all
select 'Technical','external','60' union all
select 'Technical','external','140' union all
select 'Loyalty','external','1287' union all
select 'Technical','external','261' union all
select 'Loyalty','external','5533' ;
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value desc) as 'rn'
from #mytable
)
select * from cte
December 16, 2014 at 12:49 pm
Marcus Farrugia (12/16/2014)
Ok, I want the bottom 25% value of the 'Value' column, broken up by MainGroup ideally broken into 4 quarters.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULLDROP TABLE #mytable
CREATE TABLE #mytable
(
MainGroup varchar(50),
SubGroup varchar(50),
Value int
)
insert into #mytable
(MainGroup, SubGroup, Value)
....
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value desc) as 'rn'
from #mytable
)
select * from cte
use SELECT TOP(25) PERCENT * FROM cte ORDER BY rn DESC
December 16, 2014 at 1:03 pm
Hi Old Hand, I'm probably not explaining this as well as I could ...
I need the bottom 25% of the 'Value' grouped by 'MainGroup'
Loyaltyexternal12875
Loyaltyexternal1946
Loyaltyexternal487
Operation's SupportInternal1864
Technicalexternal1405
Technicalexternal606
Technicalexternal467
Temporary AssignmentsInternal1184
December 17, 2014 at 11:25 am
djj (12/16/2014)
Marcus Farrugia (12/16/2014)
Ok, I want the bottom 25% value of the 'Value' column, broken up by MainGroup ideally broken into 4 quarters.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULLDROP TABLE #mytable
CREATE TABLE #mytable
(
MainGroup varchar(50),
SubGroup varchar(50),
Value int
)
insert into #mytable
(MainGroup, SubGroup, Value)
....
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value desc) as 'rn'
from #mytable
)
select * from cte
use
SELECT TOP(25) PERCENT * FROM cte ORDER BY rn DESC
Actually that's a bit of a trick. If use TOP and use the DESC order - you will get the TOP 25%. If you order it from the lowest value (order ASC), the TOP(25) perscent syntax will yield the bottom 25% based on the value column.
So the correct code would be
SELECT TOP(25) PERCENT * FROM cte ORDER BY rn ASC -- or skip the ASC altogether, since that's the default direction with an ORDER BY
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2014 at 11:43 am
All right - the requirement is a bit different than initially read.
Since you're trying to get a groups bottomo 25% - I think you're looking at something like the following:
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value) as 'rn',
COUNT() over(PARTITION by MainGroup ) as 'CountByMainGroup',
from #mytable
)
select * from cte
where RN<=CEILING(CountByMainGroup*.25)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2014 at 11:55 am
Matt Miller (#4) (12/17/2014)
All right - the requirement is a bit different than initially read.Since you're trying to get a groups bottomo 25% - I think you're looking at something like the following:
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value) as 'rn',
COUNT() over(PARTITION by MainGroup ) as 'CountByMainGroup',
from #mytable
)
select * from cte
where RN<=CEILING(CountByMainGroup*.25)
Matt, what about something like:
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value DESC) as 'rn'
from #mytable
)
select * from cte
where RN<=25
December 17, 2014 at 1:23 pm
djj (12/17/2014)
Matt Miller (#4) (12/17/2014)
All right - the requirement is a bit different than initially read.Since you're trying to get a groups bottomo 25% - I think you're looking at something like the following:
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value) as 'rn',
COUNT() over(PARTITION by MainGroup ) as 'CountByMainGroup',
from #mytable
)
select * from cte
where RN<=CEILING(CountByMainGroup*.25)
Matt, what about something like:
with cte as(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value DESC) as 'rn'
from #mytable
)
select * from cte
where RN<=25
two items - you need to have the order by be ASCENDING (start at the bottom going up) if you want to have the "bottom" records.
As to your use of RN = that would correct if and only if every maingroup had exactly 100 entries. Otherwise, you'd be returning more or less than 25%.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2014 at 1:32 pm
I am feeling silly. There's a much simpler implementation, but I basically never use this function.
The trick is to leverage the NTILE aggregate function rather than ROW_NUMBER. In which case the query would look a lot more like djj's.
with cte as
(
select *,
NTILE(4) over(PARTITION by MainGroup order by value DESC) as 'GroupN'
--the NTILE parameter divvies up the partitioned groups into 4 parts
--in this case NTILE #1 is the top 25% and NTILE 4 = bottom 25%
from #mytable
)
select * from cte
where GroupN=4
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 18, 2014 at 4:43 am
Matt Miller (#4) (12/17/2014)
I am feeling silly. There's a much simpler implementation, but I basically never use this function.The trick is to leverage the NTILE aggregate function rather than ROW_NUMBER. In which case the query would look a lot more like djj's.
with cte as
(
select *,
NTILE(4) over(PARTITION by MainGroup order by value DESC) as 'GroupN'
--the NTILE parameter divvies up the partitioned groups into 4 parts
--in this case NTILE #1 is the top 25% and NTILE 4 = bottom 25%
from #mytable
)
select * from cte
where GroupN=4
Maybe that was what I was remembering.
December 18, 2014 at 11:12 am
Statistically speaking you want the first quartile, so borrowing on Matt's solution I would just shift a few things to make it intuitive ...
with MYcte as (
selectMAINGROUP,vALUE, NTILE(4) OVER (PARTITION BY MAINGROUP ORDER BY VALUE ASC) AS NTILE_GROUP
FROM#MYtABLE
)
select MAINGROUP, VALUE, NTILE_GROUP AS FIRST_QUARTILE
FROM MYcte WHERE NTILE_GROUP=1
Here in your code imples you want the first quartile, the bottom 25%. That keeps confusion from setting in when you read your code in four months 😎
----------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply