[Oracle] #3-4 데이터 가공하기 : distinct, group by, 집계함수

1. 데이터 다시 가공하기

 

 축적된 데이터들을 다시 재가공하는 것도 가능하다.

 이어질 내용들은 아래의 데이터 안에서 가공되어가고 있다.

 

 <전체 데이터>

-- sungjuk 테이블 생성
create table sungjuk(
     sno    int          not null  --일련번호
    ,uname  varchar(50)  not null
    ,kor    int          not null
    ,eng    int          not null
    ,mat    int          not null
    ,tot    int             
    ,aver   int          
    ,addr   varchar(50)            --주소
    ,wdate  date                   --등록일(년월일시분초)
);


● [sungjuk 테이블 입력 데이터]

insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values(sungjuk_seq.nextval,'솔데스크', 90, 85, 95, 'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',40,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'진달래',90,50,90,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'개나리',20,50,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'봉선화',90,90,90,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'나팔꽃',50,50,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'선인장',70,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'소나무',90,60,90,'Busan',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'참나무',20,20,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'홍길동',90,90,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',80,80,90,'Suwon',sysdate);


commit;
select count(*) from sungjuk; -- 전체 레코드 갯수
select * from sungjuk;

 

1) distinct

 - 칼럼에 중복내용이 있으면 대표값 1개만 출력
 - 형식) distinct 칼럼명

 

select addr from sungjuk order by addr;

사는 지역을 오름차순으로 출력

 

select distinct(addr) from sungjuk;

distinct를 사용해서 중복값을 제거하고 하나만 출력

 

 

2) group by

 - 칼럼에 동일 내용끼리 그룹화를 시킴
 - 형식) group by 칼럼1, 칼럼2, 칼럼3, ~~~~

 

-- 주소가 동일한 값을 그룹화시키고 주소를 조회 
select addr
from sungjuk
group by addr;  -- distinct와 결과값 동일

 

-- addr은 값이 동일하게 묶이는데 그 안에서 uname값은 다 다르기에 에러가 뜬다.
-- 그룹을 시키면 나올 수 있는 그룹의 값이 1개만 가능하여야 하다.
-- ORA-00979: not a GROUP BY expression
select addr, uname
from sungjuk
group by addr;  -- 에러

 

 문1) 주소별 인원수를 조회하시오.

select addr as 주소, count(*) as 개수
from sungjuk
group by addr;

-- 주소별 오름차순 정렬해서 조회
select addr
from sungjuk
group by addr
order by addr;

-- 주소별 인원수를 내림차순으로 정렬해서 조회
select addr as 주소, count(*) as 개수
from sungjuk
group by addr
order by count(*) desc;

select addr as 주소, count(*) as 개수       -- 해석순서 3)    
from sungjuk                                -- 해석순서 1)
group by addr                               -- 해석순서 2)            
order by 개수 desc;                         -- 해석순서 4)

 

 

2. 집계 함수

 

 집계함수는 어렵지 않기 때문에 문제를 풀면서 정리하려 한다.

 

 문2) 주소별 국어점수에 대해서 집계하시오

         (개수, 최대값, 최소값, 합계, 평균)

select addr, count(*), max(kor), min(kor), sum(kor), avg(kor) -- 개수, 최대값, 최소값, 합계, 평균
from sungjuk
group by addr;


-- round(값, 0) 소수점 이하 값에서 반올림하고 소수점은 없음
select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor), 0) -- 개수, 최대값, 최소값, 합계, 평균
from sungjuk
group by addr
order by addr;


-- 국어 평균을 소수점 없이 반올림하고 내림 차순으로 정렬해서 조회하시오
select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor),0) -- 개수, 최대값, 최소값, 합계, 평균
from sungjuk
group by addr
order by round(avg(kor),0) desc;

 

 

3. 연습문제

 

 문3) 총점(tot), 평균(aver) 구하시오

update sungjuk set tot=kor+eng+mat, aver=(kor+eng+mat)/3;

 

 문4) 평균(aver)이 80점이상 행을 대상으로 주소별 인원수를 인원수순으로 조회하시오

select addr, count(*) 평균80점이상
from sungjuk
where aver>=80
group by addr
order by count(*) desc;

 

 

4. 2차 그룹

 

 그룹으로 묶인 데이터 내에서 또 다른 그룹으로 묶을 수도 있다.

 먼저 데이터를 조회해보자.

 (계속 같은 데이터 안에서 조회하고 있다)

 

 1) 주소별 순으로 조회하시오

select addr, kor, eng, mat
from sungjuk
order by addr, kor desc;

 

 2) 주소별(1차) 그룹을 하고, 주소가 같다면 국어점수(2차)로 그룹화 하기

select addr, kor, count(*)
from sungjuk
group by addr, kor
order by addr;

 

 3) 1차 그룹(kor), 2차 그룹(eng), 3차 그룹(mat)

select kor, eng, mat, count(*)
from sungjuk
group by kor, eng, mat
order by kor, eng, mat;