SQL_집계함수, GROUP BY, HAVING, ORDER BY

1. 집계함수

집계함수의 특징

• 여러 데이터들의 정보를 집계하여 연산을 해주는 함수.
• SELECT, HAVING, ORDER BY절에 사용할 수 있다
• count(*)를 제외하고는 NULL값은 제외
• where절에 올 수 없다.

집계함수의 종류

2. group by

group by절 특징

• group by 절에서는 alias명 사용 불가
• where절은 전체 데이터를 group으로 나누기 전에 행들을 미리 주어진 조건에 맞춰 가져온다.
• 그룹을 지은 상태에서 원하는 조건으로 필터링하려면 ? -> having 절 사용
• 원칙적으로는 order by절을 명시해야 데이터가 정렬된다.
• group by 절보다 where절이 먼저 수행되기 때문에 집계 함수는 where 절에 올 수 없다.

group by 활용하기

SELECT [DISTINCT] 칼럼명 [ALIAS명] 
FROM 테이블명 
[WHERE 조건식] 
[GROUP BY 칼럼이나 표현식] 
[HAVING 그룹조건식] ;

CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20) DEFAULT 'normal',
	weight NUMBER
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'grass', 30);
INSERT INTO pokemon VALUES (2, 'Ivysaur', 'grass', 50);
INSERT INTO pokemon VALUES (3, 'Venusaur', 'grass', 150);
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', 80);
INSERT INTO pokemon VALUES (5, 'Charmeleon', 'Fire', 200);
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', 15);
INSERT INTO pokemon (pm_id, name) VALUES (86, 'Seel');

-- 전체 데이터 먼저 확인하고 진행
SELECT * FROM pokemon;

-- 그룹 별로 평균 몸무게 조회
SELECT 
	attr,
	AVG(weight)
FROM pokemon
GROUP BY attr;

3. having

having 특징

• where절과 같이 특정 조건을 만족하는 결과 데이터만 표시할 수 있으나, 집계된 결과에 대해 사용해야 한다.
• 집계된 결과를 기준으로 조건이 필요한 경우 having절을 사용한다.
• group by 절과 함께 사용하여 특정한 제한 조건의 그룹화된 내용을 필터링한다.
  

having 절 활용하기

SELECT [DISTINCT] 칼럼명 [ALIAS명] 
FROM 테이블명 
[WHERE 조건식] 
[GROUP BY 칼럼이나 표현식] 
[HAVING 그룹조건식] ;

CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20),
	weight VARCHAR2(20)
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'Grass', '30');
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', '80');
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', '15');
INSERT INTO pokemon (pm_id, name) VALUES (54, 'Psyduck');
INSERT INTO pokemon (pm_id, name, attr) VALUES (76, 'Golem', 'Rock');
INSERT INTO pokemon (pm_id, name, weight) VALUES (86, 'Seel', '85');

-- Oracle

-- 1번과 2번 비교하기
-- attr 칼럼의 NULL 값 여부에 따른 차이

--1번
SELECT 
	attr, 
	AVG(weight)
FROM pokemon
GROUP BY attr
HAVING attr IS NOT NULL;


-- 2번
SELECT 
	attr, 
	AVG(weight)
FROM pokemon
GROUP BY attr
-- HAVING attr IS NOT NULL;

4. order by

order by 특징

• 오름차순은 ASC(생략가능), 내림차순은 DESC
• alias 값을 사용할 수 있다.

CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20) DEFAULT 'normal',
	height NUMBER
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'grass', 50);
INSERT INTO pokemon VALUES (2, 'Ivysaur', 'grass', 90);
INSERT INTO pokemon VALUES (3, 'Venusaur', 'grass', 250);
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', 80);
INSERT INTO pokemon VALUES (5, 'Charmeleon', 'Fire', 120);
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', 50);

SELECT 
	name, 
	height 
FROM pokemon
ORDER BY height DESC, name ASC;

select 문장 실행순서

5. SELECT 칼럼명 [ALIAS명] -- 5. 데이터의 값을 출력/계산
1. FROM 테이블명 -- 1. 발췌대상 테이블 참조
2. WHERE 조건식 -- 2. 발췌 대상 데이터가 아닌 것은 제거
3. GROUP BY 칼럼(Column)이나 표현식 -- 3. 행동들을 소그룹화
4. HAVING 그룹조건식 -- 4. 그룹핑된 값의 조건에 맞는 것만을 출력
6. ORDER BY 칼럼(Column)이나 표현식;-- 6. 데이터를 정렬

ROWNUM(Oracle)

• 데이터에 번호를 매겨 원하는 개수의 데이터를 가져온다.
• 주의할점 : 정렬이 완료되고 rownum이 아니라 where절에 작성된 rownum으로 데이터의 일부가 먼저 추출되고 나중에 데이터 정렬작업 발생

CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20) DEFAULT 'normal',
	height NUMBER
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'grass', 50);
INSERT INTO pokemon VALUES (2, 'Ivysaur', 'grass', 90);
INSERT INTO pokemon VALUES (3, 'Venusaur', 'grass', 250);
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', 80);
INSERT INTO pokemon VALUES (5, 'Charmeleon', 'Fire', 120);
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', 50);
INSERT INTO pokemon VALUES (86, 'Seel', 'Ice', 80);

-- Oracle

SELECT 
    name, 
    attr,
    height 
FROM pokemon WHERE ROWNUM < 4 ORDER BY height, name;

TOP(SQL server)

• order by로 정렬 후 원하는 데이터의 일부를 추출할 수 있음
• PERCENT : Expression의 몇 퍼센트를 출력할지 정하는 옵션
• WITH TIES : ORDER BY 조건을 기준으로 데이터의 값이 동일하여 Expresstion의 개수를 초과하게 되는 경우에도 동일한 값의 데이터를 추가로 표시

SELECT TOP(2) WITH TIES ename, sal FROM emp ORDER BY sal DESC;

CREATE TABLE pokemon (
  pm_id INT PRIMARY KEY NOT NULL,
  name VARCHAR(20) NOT NULL,
  attr VARCHAR(20) DEFAULT 'normal',
	height INT
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'grass', 50);
INSERT INTO pokemon VALUES (2, 'Ivysaur', 'grass', 90);
INSERT INTO pokemon VALUES (3, 'Venusaur', 'grass', 250);
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', 80);
INSERT INTO pokemon VALUES (5, 'Charmeleon', 'Fire', 120);
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', 50);
INSERT INTO pokemon VALUES (86, 'Seel', 'Ice', 80);

-- SQL Server

SELECT 
    TOP(3) WITH TIES 
    name, attr, 
    height 
FROM pokemon ORDER BY height, name;