SQL) 실습 내용 정리(데이터 조회와 필터링)

 본 게시글은 패스트캠퍼스의 SQL/DB 올인원 패키지 강좌를 수강하며 제가 기억할 수 있도록 작성한 것으로, 아주 기초적인 내용으로 구성되어 있습니다.

---

1. payment 테이블에서 단일 거래의 amount 액수가 가장 많은 고객들의 customer_id를 추출하라. 단, customer_id의 값은 유일해야한다.

 

내가 처음 생각했던 답(amount 액수가 가장 많은 단일 레코드의 customer_id) : 오답

SELECT customer_id
FROM payment p 
ORDER BY amount DESC LIMIT 1;

 

문제가 요구하는 답(amount 액수가 가장 많은 레코드들의 customer_id 다수)

SELECT DISTINCT customer_id
FROM payment p1
WHERE p1.amount IN 
  (
    SELECT p2.amount
    FROM payment p2
    ORDER BY p2.amount DESC LIMIT 1
  )
;

 

1. 전체 거래 중 가장 큰 amount를 구한다.

2. payment 테이블에서 가장 큰 amount를 가진 customer_id들을 조회하되, 중복을 제거한다.

 

 실제로 가장 높은 amount는 11.99인데, 이 amount값이 11.99인 결제 내역 들의 customer_id를 중복없이 조회해야 한다. IN을 사용하지 않고 '='를 사용해도 상관없다.

 

---

2. 고객들에게 단체 이메일을 전송하고자 한다. customer 테이블에서 고객의 email 주소를 추출하고, 이메일 형식에 맞지 않는 이메일 주소는 제외시켜라. (이메일 형식은 '@'가 존재해야 하고 '@'로 시작하지 말아야 하고 '@'로 끝나지 말아야 한다.)

 

내가 작성한 답안(email의 형식을 조건으로 걸 때, 앞뒤로 최소 한 글자씩 존재하도록 함)

SELECT email
FROM customer
WHERE email LIKE '_%@_%';

 

문제가 요구하는 답(순차적으로 조건을 두어 형식에 맞지 않는 이메일 거르기)

SELECT email
FROM customer
WHERE email NOT LIKE '@%' -- @로 시작하면 안됨
	AND email NOT LIKE '%@' -- @로 끝나서도 안됨
	AND email LIKE '%@%'; -- 위의 조건들을 제외하고 @를 포함하는 문자열

 

둘 다 599건의 데이터 조회. 아래의 쿼리를 통해 두 쿼리의 결과값을 교집합해본 결과 599건의 데이터가 추출되고 차집합해본 결과 0건이 나오는 것을 보아 같은 결과가 추출되는 것으로 생각된다. 다만 어떤 쪽이 성능적으로 더 좋은 쿼리일까?

 

 집합 연산에 대해서는 추후에 해당 내용을 수강한 뒤 올릴 계획이다. 다만 결과를 비교해보기 위해 서치하는 과정에서 집합 연산이 이루어지는 SELECT 절에는 COUNT 함수를 사용할 수 없단 점을 알게 됐다!

 

 두 집합이 비교되기 위해서는 반드시 컬럼 수와 컬럼 데이터 타입이 같아야 하며, SELECT 절에서 조회가능한 해당 컬럼들 혹은 '*'만 올 수 있는 듯하다. 때문에 두 집합 연산의 결과인 레코드들의 개수를 알기 위해서는 한번 더 SELECT절로 묶어야 한다.

 

 또한 서브 쿼리가 FROM절에 사용될 경우에는 반드시 ALIAS를 통해 테이블명이 명시되어야 한다는 점을 다시 한번 상기하게 되는 계기가 되었다.

-- 내 답안 ∩ 문제 답안 개수 : 599건의 데이터 출력
SELECT count(*)
FROM(
	SELECT *
	FROM (
		SELECT email
		FROM customer
		WHERE email LIKE '_%@_%'
		) AS a
	INTERSECT 
		(
		SELECT email
		FROM customer
		WHERE email NOT LIKE '@%'
			AND email NOT LIKE '%@'
			AND email LIKE '%@%'
		)
	) AS count_union
;

-- 내 답안－문제 답안 개수 : 0건의 데이터 출력
SELECT count(*)
FROM(
	SELECT *
	FROM (
		SELECT email
		FROM customer
		WHERE email LIKE '_%@_%'
		) AS a
	EXCEPT 
		(
		SELECT email
		FROM customer
		WHERE email NOT LIKE '@%'
			AND email NOT LIKE '%@'
			AND email LIKE '%@%'
		)
	) AS count_except1
;

 -- 문제 답안－내 답안 개수 : 0건의 데이터 출력
SELECT count(*)
FROM(
	SELECT *
	FROM (
		SELECT email
		FROM customer
		WHERE email NOT LIKE '@%'
			AND email NOT LIKE '%@'
			AND email LIKE '%@%'
		) AS a
	EXCEPT 
		(
		SELECT email
		FROM customer
		WHERE email LIKE '_%@_%'
		)
	) AS count_except2
;

 

 *차집합 연산자로 PostgreSQL에서는 EXCEPT를 Oracle에서는 MINUS를 사용한다.