假设我有一个带有数字列的表(让我们称之为“score”)。
我想生成一个计数表,显示分数在每个范围内出现的次数。
例如:
score range | number of occurrences ------------------------------------- 0-9 | 11 10-19 | 14 20-29 | 3 ... | ...
在这个示例中,有11行分数在0到9之间,14行分数在10到19之间,3行分数在20到29之间。
有什么简单的方法吗?你有什么建议吗?
假设我有一个带有数字列的表(让我们称之为“score”)。
我想生成一个计数表,显示分数在每个范围内出现的次数。
例如:
score range | number of occurrences ------------------------------------- 0-9 | 11 10-19 | 14 20-29 | 3 ... | ...
在这个示例中,有11行分数在0到9之间,14行分数在10到19之间,3行分数在20到29之间。
有什么简单的方法吗?你有什么建议吗?
当前回答
create table scores (
user_id int,
score int
)
select t.range as [score range], count(*) as [number of occurences]
from (
select user_id,
case when score >= 0 and score < 10 then '0-9'
case when score >= 10 and score < 20 then '10-19'
...
else '90-99' as range
from scores) t
group by t.range
其他回答
declare @RangeWidth int
set @RangeWidth = 10
select
Floor(Score/@RangeWidth) as LowerBound,
Floor(Score/@RangeWidth)+@RangeWidth as UpperBound,
Count(*)
From
ScoreTable
group by
Floor(Score/@RangeWidth)
也许你问的是如何让这样的事情继续下去……
当然,您将为查询调用全表扫描,如果包含需要统计(聚合)的分数的表很大,您可能想要一个性能更好的解决方案,您可以创建一个辅助表并使用规则,例如关于插入—您可能会研究它。
不过,并不是所有的RDBMS引擎都有规则!
select t.range as score, count(*) as Count
from (
select UserId,
case when isnull(score ,0) >= 0 and isnull(score ,0)< 5 then '0-5'
when isnull(score ,0) >= 5 and isnull(score ,0)< 10 then '5-10'
when isnull(score ,0) >= 10 and isnull(score ,0)< 15 then '10-15'
when isnull(score ,0) >= 15 and isnull(score ,0)< 20 then '15-20'
else ' 20+' end as range
,case when isnull(score ,0) >= 0 and isnull(score ,0)< 5 then 1
when isnull(score ,0) >= 5 and isnull(score ,0)< 10 then 2
when isnull(score ,0) >= 10 and isnull(score ,0)< 15 then 3
when isnull(score ,0) >= 15 and isnull(score ,0)< 20 then 4
else 5 end as pd
from score table
) t
group by t.range,pd order by pd
对于PrestoSQL/Trino应用Ken https://stackoverflow.com/a/232463/429476的答案
select t.range, count(*) as "Number of Occurance", ROUND(AVG(fare_amount),2) as "Avg",
ROUND(MAX(fare_amount),2) as "Max" ,ROUND(MIN(fare_amount),2) as "Min"
from (
select
case
when trip_distance between 0 and 9 then ' 0-9 '
when trip_distance between 10 and 19 then '10-19'
when trip_distance between 20 and 29 then '20-29'
when trip_distance between 30 and 39 then '30-39'
else '> 39'
end as range ,fare_amount
from nyc_in_parquet.tlc_yellow_trip_2022) t
where fare_amount > 1 and fare_amount < 401092
group by t.range;
range | Number of Occurance | Avg | Max | Min
-------+---------------------+--------+-------+------
0-9 | 2260865 | 10.28 | 720.0 | 1.11
30-39 | 1107 | 104.28 | 280.0 | 5.0
10-19 | 126136 | 43.8 | 413.5 | 2.0
> 39 | 42556 | 39.11 | 668.0 | 1.99
20-29 | 19133 | 58.62 | 250.0 | 2.5
另一种方法是将范围存储在表中,而不是将它们嵌入到查询中。你最终会得到一个表,命名为Ranges,它看起来像这样:
LowerLimit UpperLimit Range
0 9 '0-9'
10 19 '10-19'
20 29 '20-29'
30 39 '30-39'
查询如下所示:
Select
Range as [Score Range],
Count(*) as [Number of Occurences]
from
Ranges r inner join Scores s on s.Score between r.LowerLimit and r.UpperLimit
group by Range
这确实意味着要建立一个表,但是当所需的范围发生变化时,维护这个表是很容易的。不需要更改代码!