SQL Server: Count how big a column value is compared to the others
I have table column filled with开发者_JAVA百科 float values e.g.:
id-Values
1- 0.19230769230769232
2- 8.4848484848484854E
3- 0.10823529411764705
4- 0.05459770114942529
...
I would like to know: is there a SQL function that allows me to return a percentage of the selected row position compared to the others?
For example:
I want to know if there is an easy way to check if the row 4 is in the TOP 10%
. Or if the row 2 is in the LAST 10%
(order by values).
I know it's not possible to do a SELECT TOP 10%
or a SELECT LAST 10%
with SQL Server but it's just to give an example of what I want to do.
@Solution:
declare @a int
declare @b int
declare @values float
select @values = values from test where id <= 2
select @a = count(*) from test where values <= @values
select @b = count(*) from test
select cast( cast(@a as float) / cast(@b as float) as float) * 100 as percentage
Here's one way to do it. Based on the sample data set
CREATE TABLE Test (Id int not null, Data float not null)
insert Test values (1, 0.19230769230769232)
insert Test values (2, 8.4848484848484854E)
insert Test values (3, 0.10823529411764705)
insert Test values (4, 0.05459770114942529)
this will return the something like the percentage that you're looking for, based on the desired Id value as set in @Id:
DECLARE @Id int
SET @Id = 2
SELECT
Test.*, 100 * xx.Position / (select count(*) from Test) PercentagePosition
from Test
inner join (select Id, row_number() over (order by Data) / 1.0 Position from Test) xx
on xx.Id = Test.Id
where Test.Id = @Id
I don't much like this, as it requires two table scans. Shortcuts might be devised, depending on what else the application needs to do.
Check if following code help you.
declare @a int
declare @b int
select @a = count(*) from Foo where FooId <= 2
select @b = count(*) from Foo
select cast( cast(@a as float) / cast(@b as float) as float) * 100 as percentage
Ok, this should be a SQL 2000 compatible version. Based on the same table structure as my prior answer:
DECLARE
@Id int
,@Data float
SET @Id = 3
SELECT @Data = Data
from Test
where Id = @Id
SELECT (sum(case when Data < @Data then 1.0 else 0.0 end) + 1) / count(*)
from Test
Assuming an index on Id, there's now only 1 table scan. In case of duplicate values, this will select the position based on the first occurance. Mess around with that +1; without it, the first value will get you 0%, with it, with four rows you'd get 25% -- so what is right for your application? Also, if the table is empty, you'll get a divide by zero error, so you'll need to handle that as appropriate to your application.
精彩评论