PostgreSQL 문법 정리1 - 기초, SELECT, GROUP BY 정리

SQL 분류

• SQL문은 DDL, DML, DCL(TCL)로 분류한다.
• DDL(Data Definition Language, 데이터 정의어)
  • 데이터베이스를 정의하는 언어이며, 데이터리를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 하는 언어 이다. 데이터베이스, 테이블등을 생성하는 역할을 한다.
  • CREATE, ALTER, DROP, TRUNCATE 등이 있다.
• DML (Data Manipulation Language, 데이터 조작어)
  • 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어 이다.
  • 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
  • SELECT, INSERT, UPDATE, DELETE 등이 있다.
• DCL(Data Control Language, 데이터 제어어)
  • 데이터를 제어하는 언어 이다.
  • 데이터의 보안, 무결성, 회복, 권한 등을 정의하는데 사용한다
  • TCL (Transaction Control Language, 트랜잭션 제어 언어)과 DCL로 구분하기도 한다.
  • GRANT, REVOKE, COMMIT, ROLLBACK 등이 있다
• SQL 예약어를 대문자로 표시하고 끝에 세미콜론(선택사항)을 붙이면 코드 가독성이 좋아진다.

SELECT

• 테이블의 데이터를 조회하는 명령어
• SELECT 컬럼명 FROM 테이블명;
• 컬럼명을 *으로 쓰면 모든 컬럼을 조회한다는 의미, 컬럼명들 사이에 쉼표를 넣으면 여러 개의 컬럼을 조회할 수 있음
  
  
• DISTINCT: 중복된 데이터를 제외하고 조회할 때 사용, 어떤 컬럼에서 고유한 값을 찾고 싶을 때 유용하다.
• SELECT DISTINCT 컬럼명 FROM 테이블명;
• SELECT DISTINCT(컬럼명) FROM 테이블명; DISTINCT 바로 뒤에 컬럼명에 괄호를 씌워서 사용할 수도 있다. 괄호를 쓰나 안쓰나 결과는 같다.
• COUNT 함수: 특정 쿼리 조건에 맞는 입력 행의 개수를 구하는데 사용한다.
  • 함수이기 때문에 괄호를 사용해야 한다.
  • 특정 컬럼을 지정하거나 COUNT(*)로 모든 컬럼을 지정할 수 있다. 둘 다 결과는 동일하다.
  • SELECT COUNT(컬럼) FROM 테이블명;
  • 단순히 테이블의 행 개수를 세어서 반환하기 때문에 다른 명령어와 함께 사용하는 경우가 많다. 특히 DISTINCT와 많이 사용된다.
  • SELECT COUNT(DISTINCT 컬럼명) FROM 테이블명;
• WHERE 문: 컬럼에 조건을 지정하여 그에 맞는 행이 반환 되도록 한다.
  • 조건에 맞는 필터 기능을 수행한다.
  • FROM 절 바로 뒤에 위치한다
  • SELECT column1, column2 FROM table WHERE conditions;
  • SELECT name, choice FROM table WHERE name = ‘David' AND choice = 'RED'
• ORDER BY 문: 조회된 데이터를 기준에 맞게 정렬해준다.
  • SELECT column1, column2, … FROM table ORDER BY column_1 ASC / DESC
  • ASC: 오름차순, DESC: 내림차순, 지정하지 않으면 보통 ASC를 사용한다
  • 보통 SQL문 가장 끝에 위치하지만 LIMIT 보다는 앞에 위치한다.
  • ORDER BY를 여러 컬럼으로 지정하여 사용할 수도 있다.
  • SELECT store_id, first_name, last_name FROM customer ORDER BY store_id ASC, first_name DESC;
• LIMIT: 쿼리로 반환 되는 행의 개수를 제한하는 명령어
  • 쿼리 요청의 가장 아래로 내려가며 가장 마지막에 실행된다.
  • SELECT * FROM table LIMIT 5;
  • SELECT customer_id FROM payment ORDER BY payment_date ASC limit 10;
• BETWEEN 연산자: 값을 값 범위와 비교할 때 사용
  • WHERE문에 조건을 더하기 위해 많이 사용된다.
  • value ≥ low AND value ≤ high
  • value BETWEEN low AND high 위와 같은 의미 이다. (low이상, high이하)
  • value < low or value > high
  • value NOT BETWEEN low AND high NOT을 이용한 반대 범위 지정(low 미달, high 초과)
  • SELECT COUNT(*) FROM film WHERE rating = 'R' AND replacement_cost BETWEEN 5 AND 15;
  • BETWEEN으로 date 범위를 필터링하는 경우 시간에 유의해야 한다.
  • BETWEEN '2007-02-01' AND '2007-02-14’ 일 때 실제 시간 범위는 2007년 2월 1일 0시 ~ 2007년 2월 14일 0시 이다. 따라서 2월 14일 10시 같은 데이터는 조회 되지 않는다.
• IN 연산자: 어떤 값이 목록에 포함됐는지 확인할 때 사용한다.
  • SELECT color FROM table WHERE color IN ('red', 'blue');
  • SELECT color FROM table WHERE color NOT IN ('red', 'blue'); - NOT 연산자 사용
  • SELECT * FROM payment where amount not in (0.99, 1.98, 1.99);
• LIKE, ILIKE: 문자열 데이터에 대한 패턴 매칭을 수행하기 위한 명령어이다. LIKE는 대소문자를 구분하지만 ILIKE는 구분하지 않는다.
  • 와일드 카드 연산자를 사용할 수 있다.
  • 대문자 ‘A’로 시작하는 이름: WHERE name LIKE 'A%'
  • 소문자 ‘a’로 끝나는 이름: WHERE name LIKE '%a'
  • select * from customer where first_name like 'J%' and last_name ilike 's%';
  • 밑줄을 이용하면 문자 하나만 교체할 수 있다.
  • WHERE title LIKE ‘Mission Impossible _’
    • Mission Impossible 1, Mission Impossible 2, Mission Impossible 3 등이 모두 매칭된다.
  • 연속으로 쓸 수도 있다. WHERE value LIKE 'Version#__'
  • 조합해서 사용: WHERE name LIKE '_her%'
    • Cheryl
    • Theresa
    • Sherri 등이 모두 매칭된다

GROUP BY

• 데이터가 카테고리 별로 어떻게 분포되어 있는지 파악하기 위해 데이터를 집계하고 함수를 적용하는 SQL문이다.
• 밑의 표에서 A카테고리 값의 평균(9)이나 개수(2개)를 구해야 할 때 GROUP BY절이 유용하다.카테고리값 

  A	10
  A	8
  B	6
  B	12

• 집계함수의 종류
  • AVG(): SELECT ROUND(AVG(replacement_cost), 2) FROM film;
  • COUNT(): SELECT COUNT(*) FROM film;
  • MAX(): SELECT MAX(replacement_cost) FROM film;
  • MIN(): SELECT MIN(replacement_cost) FROM film;
  • SUM(): SELECT SUM(replacement_cost) FROM film;
• SELECT category_col, AGG(data_col) FROM table GROUP BY category_col;
• GROUP BY절은 FROM문 바로 뒤 또는 WHERE문 바로 뒤에 위치해야 한다.
• SELECT문에서 특정 컬럼을 조회한다면 그 컬럼이 GROUP BY절에 포함되야 한다. 단 집계함수는 GROUP BY절에 포함되지 않아도 된다.
• SELECT company, division, SUM(sales) FROM finance_table GROUP BY company, division;
• WHERE절에는 집계함수를 쓸 수 없다. 대신 HAVING을 사용할 수 있다.
• 집계를 기반하여 정렬하려면 전체 함수를 참조해야 한다.
• SELECT company, SUM(sales) FROM finance_table GROUP BY company ORDER BY SUM(sales);
• 가장 많은 금액을 사용한 고객ID는?
• SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC;
• 결제 날짜 별(DATE함수 사용) 결제 금액 합계를 구하고, 합계 내림차순으로 정렬하기
• SELECT DATE(payment_date), SUM(amount) FROM payment GROUP BY DATE(payment_date) ORDER BY SUM(amount) DESC;
• 직원id별로 처리한 결제 건수는 몇 건인가?
• SELECT count(*), staff_id FROM payment group by staff_id;
• 영화 등급별 평균 교체 비용은?
• SELECT rating, ROUND(AVG(replacement_cost), 2) FROM film GROUP BY rating;
• 결제 금액 합계 기준 가장 많이 지출한 고객 id 5개 찾기
• SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC LIMIT 5;

Having 절

• HAVING절은 GROUP BY절로 선택된 그룹에 대한 탐색 조건을 지정한다.
• HAVING절은 집계가 이미 수행된 이후에 자료를 필터링하기 때문에 GROUP BY 호출 뒤에 위치한다.
• SELECT company, SUM(sales) FROM finance_table WHERE company != 'Google' GROUP BY company HAVING SUM(sales) > 1000;
• WHERE 필터를 적용하고 나서 GROUP BY를 호출한 후에 HAVING 절이 적용되어 판매액 총액이 1000보다 큰 값을 조건으로 다시 필터링 된다.
• 결제 거래 건수가 40건 이상인 고객 ID 찾기
• SELECT customer_id, COUNT() FROM payment GROUP BY customer_id HAVING COUNT() >= 40
• 직원 ID 2번과의 거래 중 100을 초과하여 사용한 고객의 ID 찾기
• SELECT customer_id, SUM(amount) FROM payment WHERE staff_id = 2 GROUP BY customer_id HAVING SUM(amount) > 100

SQL문 기초 연습문제

1. ID가 2인 직원에게서 최소 110달러를 쓴 고객의 ID는?

SELECT customer_id, SUM(amount) 
FROM payment WHERE staff_id = 2 
GROUP BY customer_id 
HAVING SUM(amount) > 110;

1. J로 시작하는 영화는 몇 개인가?

SELECT COUNT(*) 
FROM film 
WHERE title LIKE 'J%';

1. 이름이 ‘E’로 시작하는 동시에 주소 ID가 500미만인 고객 중, ID 번호가 가장 높은 고객은?

SELECT first_name, last_name 
FROM customer 
WHERE first_name LIKE 'E%' AND address_id < 500 
ORDER BY customer_id DESC 
LIMIT 1;

1. 2012년 9월의 예약 건수 총합을 facid 별로 집계

SELECT facid, SUM(slots) AS total_slots
FROM cd.bookings
WHERE starttime BETWEEN '2012-09-01' AND '2012-10-01'
GROUP BY facid
ORDER BY SUM(slots)

1. facid 별로 예약 건수 총합이 1000건 이상인 것만 조회하고, facid 오름차순으로 정렬

SELECT facid, SUM(slots) AS total_slots
FROM cd.bookings
GROUP BY facid
HAVING SUM(slots) > 1000
ORDER BY facid