sql 강좌 오라클:범위에서 "그룹화"하는 방법?



pl/sql 강좌 (8)

이런 식의 테이블이 있다면 :

pkey   age
----   ---
   1     8
   2     5
   3    12
   4    12
   5    22

나는 각 연령의 카운트를 얻기 위해 "그룹화"할 수 있습니다.

select age,count(*) n from tbl group by age;
age  n
---  -
  5  1
  8  1
 12  2
 22  1

연령대별로 그룹화하는 데 사용할 수있는 쿼리는 무엇입니까?

  age  n
-----  -
 1-10  2
11-20  2
20+    1

나는 10gR2에 있지만, 11g의 특정 접근법에도 관심이 있습니다.


내 접근 방식 :

select range, count(1) from (
select case 
  when age < 5 then '0-4' 
  when age < 10 then '5-9' 
  when age < 15 then '10-14' 
  when age < 20 then '15-20' 
  when age < 30 then '21-30' 
  when age < 40 then '31-40' 
  when age < 50 then '41-50' 
  else                '51+' 
end 
as range from
(select round(extract(day from feedback_update_time - feedback_time), 1) as age
from txn_history
) ) group by range  
  • 범위를 정의 할 때 유연성이 있습니다.
  • select 및 group 절에서 범위를 반복하지 않습니다.
  • 그러나 어떤 사람들은 저에게, 규모에 따라 그들을 주문하는 방법을 말해주십시오!

대신 age_range 테이블과 age_range_id 필드를 테이블과 그룹에 추가하십시오.

// DDL을 용서하지만 생각을해야합니다.

create table age_range(
age_range_id tinyint unsigned not null primary key,
name varchar(255) not null);

insert into age_range values 
(1, '18-24'),(2, '25-34'),(3, '35-44'),(4, '45-54'),(5, '55-64');

// 다시 DML을 핑계로 삼으십시오. 그러나 아이디어를 얻으십시오.

select
 count(*) as counter, p.age_range_id, ar.name
from
  person p
inner join age_range ar on p.age_range_id = ar.age_range_id
group by
  p.age_range_id, ar.name order by counter desc;

원하는 경우이 아이디어를 구체화 할 수 있습니다 - age_range 테이블 등에서 from_age to_age 열을 추가하십시오.하지만 그 것을 남겨 두겠습니다.

희망이 도움이 :)


시험:

select to_char(floor(age/10) * 10) || '-' 
|| to_char(ceil(age/10) * 10 - 1)) as age, 
count(*) as n from tbl group by floor(age/10);

다음은 하위 쿼리에 "범위"테이블을 만든 다음이를 사용하여 주 테이블의 데이터를 분할하는 솔루션입니다.

SELECT DISTINCT descr
  , COUNT(*) OVER (PARTITION BY descr) n
FROM age_table INNER JOIN (
  select '1-10' descr, 1 rng_start, 10 rng_stop from dual
  union (
  select '11-20', 11, 20 from dual
  ) union (
  select '20+', 21, null from dual
)) ON age BETWEEN nvl(rng_start, age) AND nvl(rng_stop, age)
ORDER BY descr;

당신이 찾고있는 것은 기본적으로 histogram 의 데이터입니다.

x 축에는 연령 (또는 연령대)을, y 축에는 카운트 n (또는 빈도)을 사용합니다.

가장 간단한 형식으로 이미 설명한 것처럼 각 연령 값의 수를 계산할 수 있습니다.

SELECT age, count(*)
FROM tbl
GROUP BY age

그러나 x 축에 너무 많은 다른 값이있는 경우 그룹 (또는 클러스터 또는 버킷)을 만들 수 있습니다. 귀하의 경우에는 10의 일정한 범위로 그룹화합니다.

우리는 각 범위에 대해 WHEN ... THEN 행을 쓰는 것을 피할 수 있습니다 - 연령과 관련이없는 경우 수백 가지가있을 수 있습니다. 대신 @MatthewFlaschen의 접근법은 @NitinMidha가 언급 한 이유 때문에 바람직합니다.

이제 SQL을 만들자 ...

첫째, 나이를 범위 그룹 10으로 나눠야합니다.

  • 0-9
  • 10-19
  • 20 - 29
  • 기타

이것은 연령 열을 10으로 나누고 그 결과 FLOOR를 계산하여 얻을 수 있습니다.

FLOOR(age/10)

"FLOOR는 n보다 크거나 같은 가장 큰 정수를 반환합니다." http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions067.htm#SQLRF00643

그런 다음 원본 SQL을 가져와 해당 표현식으로 바꿉니다.

SELECT FLOOR(age/10), count(*)
FROM tbl
GROUP BY FLOOR(age/10)

이것은 괜찮습니다. 그러나 범위를 볼 수는 없습니다. 대신 우리는 0, 1, 2 ... n 계산 된 바닥 값만 봅니다.

실제 하한값을 얻으려면 다시 10을 곱해야 0, 10, 20 ... n .

FLOOR(age/10) * 10

또한 각 범위의 상한은 하한 + 10-1 또는

FLOOR(age/10) * 10 + 10 - 1

마지막으로이 두 문자열을 다음과 같은 문자열로 연결합니다.

TO_CHAR(FLOOR(age/10) * 10) || '-' || TO_CHAR(FLOOR(age/10) * 10 + 10 - 1)

'0-9', '10-19', '20-29' 등이 생성됩니다.

이제 SQL은 다음과 같습니다.

SELECT 
TO_CHAR(FLOOR(age/10) * 10) || ' - ' || TO_CHAR(FLOOR(age/10) * 10 + 10 - 1),
COUNT(*)
FROM tbl
GROUP BY FLOOR(age/10)

마지막으로 주문과 멋진 열 별칭을 적용합니다.

SELECT 
TO_CHAR(FLOOR(age/10) * 10) || ' - ' || TO_CHAR(FLOOR(age/10) * 10 + 10 - 1) AS range,
COUNT(*) AS frequency
FROM tbl
GROUP BY FLOOR(age/10)
ORDER BY FLOOR(age/10)

그러나보다 복잡한 시나리오에서는 이러한 범위가 크기가 10 인 청크로 그룹화되지는 않지만 동적 클러스터링이 필요합니다. 오라클에는보다 고급 히스토그램 기능이 포함되어 있습니다 ( http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_histo.htm#TGSQL366

그의 접근을 위해 @MatthewFlaschen에 대한 크레딧; 나는 단지 세부 사항을 설명했다.


Oracle 9i +를 사용하는 경우 NTILE 분석 함수 를 사용할 있습니다.

WITH tiles AS (
  SELECT t.age,
         NTILE(3) OVER (ORDER BY t.age) AS tile
    FROM TABLE t)
  SELECT MIN(t.age) AS min_age,
         MAX(t.age) AS max_age,
         COUNT(t.tile) As n
    FROM tiles t
GROUP BY t.tile

NTILE에 대한주의 사항은 중단 점 자체가 아니라 파티션 수만 지정할 수 있다는 것입니다. 따라서 적절한 번호를 지정해야합니다. IE : NTILE(4) 는 100 개의 행으로 4 개의 버킷 / 파티션 각각에 25 개의 행을 할당합니다. 분석 함수를 중첩 할 수 없으므로 원하는 세부 분류를 얻기 위해 하위 쿼리 / 하위 쿼리 인수 분해를 사용하여 분석 함수를 계층화해야합니다. 그렇지 않으면 다음을 사용하십시오.

  SELECT CASE t.age
           WHEN BETWEEN 1 AND 10 THEN '1-10' 
           WHEN BETWEEN 11 AND 20 THEN '11-20' 
           ELSE '21+' 
         END AS age, 
         COUNT(*) AS n
    FROM TABLE t
GROUP BY CASE t.age
           WHEN BETWEEN 1 AND 10 THEN '1-10' 
           WHEN BETWEEN 11 AND 20 THEN '11-20' 
           ELSE '21+' 
         END

하루에 몇 건씩 샘플을 얻어야했습니다. @Clarkey에서 영감을 얻어 TO_CHAR을 사용하여 타임 스탬프에서 ISO-8601 날짜 형식으로 샘플 날짜를 추출한 다음이를 GROUP BY 및 ORDER BY 절에 사용했습니다. (더 많은 영감을 얻었고 다른 사람들에게 유용 할 수 있기 때문에 여기에 게시합니다.)

SELECT 
  TO_CHAR(X.TS_TIMESTAMP, 'YYYY-MM-DD') AS TS_DAY, 
  COUNT(*) 
FROM   
  TABLE X
GROUP BY
  TO_CHAR(X.TS_TIMESTAMP, 'YYYY-MM-DD')
ORDER BY
  TO_CHAR(X.TS_TIMESTAMP, 'YYYY-MM-DD') ASC
/

SELECT CASE 
         WHEN age <= 10 THEN '1-10' 
         WHEN age <= 20 THEN '11-20' 
         ELSE '21+' 
       END AS age, 
       COUNT(*) AS n
FROM age
GROUP BY CASE 
           WHEN age <= 10 THEN '1-10' 
           WHEN age <= 20 THEN '11-20' 
           ELSE '21+' 
         END




oracle10g