1장_7절. group by, having절
#내장함수는 단일행함수(single-row Function)와 다중행함수(multi-row Function)가 있다.
단일행함수(문자형,숫자형, 날짜형, 변환형, case함수,null관련 함수)
다중행함수(집계함수:Aggregate Function, 그룹함수:Group Function, 윈도우함수:Window Function)
#이장에서는 집계함수만 설명함, 그룹함수랑 윈도우함수는 SQL활용에서 설명
1. 집계함수(Aggregate Function)
-여러 행들의 그룹이 모여서 그룹당 하나의 결과를 return주는 함수
-group by 절은 행들을 소그룹화 한다
-select절, having절, order by절에 사용할 수 있다.
-집계함수명(distinct 이나 all 칼럼이나 표현식)
-그룹에 대한 정보를 제공하므로 주로 숫자유형에 사용된다
-단 max, min, count함수는 문자, 날짜유형에도 사용된다.
1)count(*) : null값을 포함한 모든 행의 수를 return
2)count(표현식) : null을 제외한 모든 행의 수를 return(문자, 날짜도 사용가능)
3)sum(표현식) : null값을 제외한 합계를 return
4)avg(표현식) : null값을 제외한 평균을 return
5)max(표현식) : 최대값을 return(문자,날짜도 사용가능)
6)min(표현식) : 최소값을 return(문자,날짜도 사용가능)
7)stddev(표현식) : 표준편차를 return
8)varian(표현식) : 분산을 return
9)기타 통계함수
ex1)
SQL> select count(*), count(nation), max(nation),min(nation)
2 from player;
COUNT(*) COUNT(NATION) MAX(NATION)
---------- ------------- ----------------------------------------
MIN(NATION)
----------------------------------------
482 27 크로아티아
김탈리아
ex2)
SQL> select count(*), count(height), max(height), min(height), round(avg(height),2)
2 from player;
COUNT(*) COUNT(HEIGHT) MAX(HEIGHT) MIN(HEIGHT) ROUND(AVG(HEIGHT),2)
---------- ------------- ----------- ----------- --------------------
482 448 196 165 179.29
ex3)
SQL> select count(*), count(height), max(height), min(height), avg(height)
2 from player;
COUNT(*) COUNT(HEIGHT) MAX(HEIGHT) MIN(HEIGHT) AVG(HEIGHT)
---------- ------------- ----------- ----------- -----------
482 448 196 165 179.290179
2.Group by절
-from절, where절뒤에 group by절이 온다.
-from과 where조건을 거친 데이터들은 group by절로 인해 2차분류된다.
-group by절을 통해 소그룹별 기준을 정하고, select절의 집계함수를 사용한다.
-집계함수의 통계정보는 null값을 가진 행을 제외하고 수행한다.
-group by절에서는 alias를 사용할 수 없다(select절에서만 사용가능)
-where절은 전체행을 group으로 나누기 전에 행들을 미리 제거한다.
-having절은 group by절의 조건절이다
-having절은 group by절의 뒤에온다.
ex1)그룹핑안하면 오류발생
SQL> select position 포지션, round(avg(position),2)평균키
2 from player;
select position 포지션, round(avg(position),2)평균키
*
ERROR at line 1:
ORA-00937: not a single-group group function
ex2)group by 절에서 별명사용하면 오류발생
SQL> select position, round(avg(position),2)평균키
2 from player
3 group by position 포지션;
group by position 포지션
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
=>적절한 질의어
SQL> select position 포지션, round(avg(height),2) 평균키
2 from player
3 group by position;
포지션 평균키
-------------------- ----------
GK 186.26
DF 180.21
FW 179.91
MF 176.27
3.having절
-group by절에서 묶인 그룹의 조건을 체크하는 절
-집계함수는 having절에서만 사용할 수 있다.(=집계함수는 group by절로 묶여야만 사용할 수 있다.)
-select문에서 count(*)로 테이블전체를 하나의 그룹으로 취급할때는 집계함수를 select절에서 사용할 수 있다.(이때도 where절에는 사용할 수 없다. 당연함..하나의 그룹에서 최대값,최소값,평균을 체크할 수 없음)??
ex1) where절에서 집계함수(count, sum, avg, max, min등)을 사용할 수 없다.
SQL> select position 포지션, round(avg(height),2)평균키
2 from player
3 where avg(height)>=180
4 group by position;
where avg(height)>=180
*
ERROR at line 3:
ORA-00934: group function is not allowed here
=>올바른 질의문
SQL> select position 포지션, round(avg(height),2)평균키
2 from player
3 group by position
4 having avg(height)>=180;
포지션 평균키
-------------------- ----------
GK 186.26
DF 180.21
ex2) K02선수와 K09선수의 인원을 구하기
방법1)
SQL> select team_id 팀명, count(*)
2 from player
3 group by team_id
4 having team_id in ('K02','K09');
팀명 COUNT(*)
------ ----------
K02 50
K09 49
=>player테이블에서 team_id별로 모든행을 그룹화한다(복잡도n)
=>그룹중에서 team_id가 K02,K09인것행 선택한다(복잡도n)
=>집계함수 count(*)를 사용한다.(복잡도 K02+K09의 원소갯수)
방법2)
SQL> select team_id 팀명, count(*)
2 from player
3 where team_id in('K02','K09')
4 group by team_id;
팀명 COUNT(*)
------ ----------
K02 50
K09 49
=>player테이블에서 team_id가 K02,K09인 행만 선택한다.(복잡도n)
=>team_id별로 그룹화한다(복잡도 K02+K09의 원소갯수)
=>집계함수 count(*)를 사용한다(복잡도 K02+K09의 원소갯수)
#방법1(having절사용) vs 방법2(where절 사용)의 차이??
=> 방법1(having) : group by 명령을 수행할 때 그룹핑 하기위해 모든 player행을 읽음
+ team_id가 일치하는 행들을 선택할때 그룹핑된 모든 player를 읽음
=> 방법2(where) : where 명령을 수행할 때 모든 player행을 읽어서 team_id가 일치하는 행만을 걸러냄
+ group by 명령을 수행할때는 team_id가 일치하는 행만을 읽음
결론 : where절에서 최대한 조건을 걸러주어야 전체행을 읽는 회수가 줄어드므로 조회속도가 빠름
1)단일행함수(문자형함수,숫자형함수,날짜형함수,변환형함수, case표현, null함수)와
2)비교연산, sql연산, 논리연산, 부정연산
=>조건에 맞는 행을 찾는 연산들임
#group by절에서 사용가능한 연산은
1)단일행함수
2)비교연산,sql연산, 논리연산, 부정연산
3)집계함수(count, sum, avg, max등등)
=>그룹안에서 각 행마다 조건에 맞는 행을 찾는 연산과
그룹안에서 여러행이 있을때 적용가능한 집계연산들임.
#팀별, 포지션별 포지션의 인원수와 전체인원수를 구하는 쿼리문...
SQL> select team_id, nvl(sum(case position when 'FW' then 1 else 0 end),0) FW,
2 nvl(sum(case position when 'MF' then 1 else 0 end),0) MF,
3 nvl(sum(case position when 'DF' then 1 else 0 end),0) DF,
4 nvl(sum(case position when 'GK' then 1 else 0 end),0) GK,
5 count(*) SUM
6 from player
7 group by team_id;
=>case : case 표현식1 when 표현식2 일때 1로, 아니면 0으로하고,
=>sum(표현식) : null값을 제외한 모든 값을 더함
//case의 결과값을 모두 더함
=>nvl(표현식1,표현식2) : 표현식1이 null이면 표현식2을, 아니면 표현식1값을 출력함.
//nvl(sum값,0) : sum값이 null이면 0을 아니면, sum값을 출력함.
=>count(*) : 집계함수를 사용, 그룹별로 모든 행의 개수를 더함
=>group by team_id : team id별로 그룹화하여 count(*)집계함수를 적용
TEAM_I FW MF DF GK SUM
------ ---------- ---------- ---------- ---------- ----------
K06 11 11 20 4 46
K14 0 1 1 0 2
K13 1 0 1 1 3
K15 1 1 1 0 3
K04 13 11 18 4 46
K03 6 15 23 5 49
K07 9 23 16 4 52
K05 10 19 17 5 51
K02 10 18 17 4 50
K08 8 15 15 4 45
K12 1 0 1 0 2
TEAM_I FW MF DF GK SUM
------ ---------- ---------- ---------- ---------- ----------
K01 12 15 13 5 45
K11 1 1 1 0 3
K10 5 15 13 3 36
K09 12 18 15 4 49
15 rows selected.
4.Case표현을 활용한 월별데이터 집계
1) 월별데이터 개별 추출
2) select절에 case문을 활용하여 월별로 구분
3) select절에 집계함수 avg()를 사용, avg함수안에 case문 사용, 부서별로 group by함수 사용.
5.집계함수와 null처리
1)데이터가 없는경우에도 select절에 null관련함수(nvl, nullif, coalesce)를 사용하여 집계함수적용이 가능함
##아..개어렵..헷갈림ㅎ##
댓글
댓글 쓰기