假设我有一个带有数字列的表(让我们称之为“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之间。
有什么简单的方法吗?你有什么建议吗?
当前回答
对于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
其他回答
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
select cast(score/10 as varchar) + '-' + cast(score/10+9 as varchar),
count(*)
from scores
group by score/10
我会用一种稍微不同的方式来做,这样它就不必定义每种情况:
select t.range as [score range], count(*) as [number of occurences]
from (
select FLOOR(score/10) as range
from scores) t
group by t.range
没有测试过,但你懂的…
在postgres中(其中||是字符串连接操作符):
select (score/10)*10 || '-' || (score/10)*10+9 as scorerange, count(*)
from scores
group by score/10
order by 1
给:
scorerange | count
------------+-------
0-9 | 11
10-19 | 14
20-29 | 3
30-39 | 2
下面是如何在T-SQL中做到这一点:
DECLARE @traunch INT = 1000;
SELECT
CONCAT
(
FORMAT((score / @traunch) * @traunch, '###,000,000')
, ' - ' ,
FORMAT((score / @traunch) * @traunch + @traunch - 1, '###,000,000')
) as [Range]
, FORMAT(MIN(score), 'N0') as [Min]
, FORMAT(AVG(score), 'N0') as [Avg]
, FORMAT(MAX(score), 'N0') as [Max]
, FORMAT(COUNT(score), 'N0') as [Count]
, FORMAT(SUM(score), 'N0') as [Sum]
FROM scores
GROUP BY score / @traunch
ORDER BY score / @traunch
因为要排序的列(Range)是一个字符串,所以使用字符串/单词排序而不是数值排序。
只要字符串有0来填充数字长度,排序在语义上仍然是正确的:
SELECT t.range AS ScoreRange,
COUNT(*) AS NumberOfOccurrences
FROM (SELECT CASE
WHEN score BETWEEN 0 AND 9 THEN '00-09'
WHEN score BETWEEN 10 AND 19 THEN '10-19'
ELSE '20-99'
END AS Range
FROM Scores) t
GROUP BY t.Range
如果范围是混合的,简单地填充一个额外的0:
SELECT t.range AS ScoreRange,
COUNT(*) AS NumberOfOccurrences
FROM (SELECT CASE
WHEN score BETWEEN 0 AND 9 THEN '000-009'
WHEN score BETWEEN 10 AND 19 THEN '010-019'
WHEN score BETWEEN 20 AND 99 THEN '020-099'
ELSE '100-999'
END AS Range
FROM Scores) t
GROUP BY t.Range