SQL에서 범위를 어떻게 "그룹화"할 수 있습니까?

SQL에서 범위를 어떻게 "그룹화"할 수 있습니까?

---

숫자 열이있는 테이블이 있다고 가정합니다 ( "스코어"라고 함).

카운트 테이블을 생성하고 싶습니다. 각 테이블에 점수가 몇 번이나 나타 났는지 보여줍니다.

예를 들면 다음과 같습니다.

점수 범위 | 발생 횟수
-------------------------------------
   0-9 | 11
  10-19 | 14
  20-29 | 삼
   ... | ...

이 예에서는 0 ~ 9 범위의 점수를 가진 11 개의 행, 10 ~ 19 범위의 점수를 가진 14 개의 행과 20-29 범위의 점수를 가진 3 개의 행이있었습니다.

이것을 설정하는 쉬운 방법이 있습니까? 추천 메뉴가 무엇인가요?

---

SQLServer 2000에서는 가장 높은 투표 응답 중 어느 것도 정확하지 않습니다. 아마도 다른 버전을 사용하고 있었을 것입니다.

다음은 SQLServer 2000에서 올바른 버전입니다.

select t.range as [score range], count(*) as [number of occurences]
from (
  select case  
    when score between 0 and 9 then ' 0- 9'
    when score between 10 and 19 then '10-19'
    else '20-99' end as range
  from scores) t
group by t.range

또는

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'
         when score >= 10 and score< 20 then '10-19'
         else '20-99' end as range
     from scores) t
group by t.range

---

다른 방법은 범위를 쿼리에 포함시키는 대신 테이블에 저장하는 것입니다. 테이블로 끝나고 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

이는 테이블 설정을 의미하지만 원하는 범위가 변경 될 때 유지 관리가 쉽습니다. 코드를 변경할 필요가 없습니다!

---

SQL Server의 구문에서 작동하지 않는 답변이 여기에 있습니다. 나는 사용할 것이다 :

select t.range as [score range], count(*) as [number of occurences]
from (
  select case 
    when score between  0 and  9 then ' 0-9 '
    when score between 10 and 19 then '10-19'
    when score between 20 and 29 then '20-29'
    ...
    else '90-99' end 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

---

James Curran의 대답은 제 의견으로는 가장 간결하지만 결과는 정확하지 않습니다. SQL Server의 경우 가장 간단한 명령문은 다음과 같습니다.

SELECT 
    [score range] = CAST((Score/10)*10 AS VARCHAR) + ' - ' + CAST((Score/10)*10+9 AS VARCHAR), 
    [number of occurrences] = COUNT(*)
FROM #Scores
GROUP BY Score/10
ORDER BY Score/10

이것은 테스트에 사용한 #Scores 임시 테이블을 가정하고, 0에서 99 사이의 임의의 숫자로 100 개의 행을 채웠습니다.

---

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

---

This will allow you to not have to specify ranges, and should be SQL server agnostic. Math FTW!

SELECT CONCAT(range,'-',range+9), COUNT(range)
FROM (
  SELECT 
    score - (score % 10) as range
  FROM scores
)

---

I would do this a little differently so that it scales without having to define every case:

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

Not tested, but you get the idea...

---

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)

---

select t.blah as [score range], count(*) as [number of occurences]
from (
  select case 
    when score between  0 and  9 then ' 0-9 '
    when score between 10 and 19 then '10-19'
    when score between 20 and 29 then '20-29'
    ...
    else '90-99' end as blah
  from scores) t
group by t.blah

Make sure you use a word other than 'range' if you are in MySQL, or you will get an error for running the above example.

---

Because the column being sorted on (Range) is a string, string/word sorting is used instead of numeric sorting.

As long as the strings have zeros to pad out the number lengths the sorting should still be semantically correct:

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

If the range is mixed, simply pad an extra zero:

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

---

Try

SELECT (str(range) + "-" + str(range + 9) ) AS [Score range], COUNT(score) AS [number of occurances]
FROM (SELECT  score,  int(score / 10 ) * 10  AS range  FROM scoredata )  
GROUP BY range;

---

Perhaps you're asking about keeping such things going...

Of course you'll invoke a full table scan for the queries and if the table containing the scores that need to be tallied (aggregations) is large you might want a better performing solution, you can create a secondary table and use rules, such as on insert - you might look into it.

Not all RDBMS engines have rules, though!

참고URL : https://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges