DB

20240614 FRI. 집합연산,
A. 집합 연산
1. 합집합

• UNION 연산: 중복을 제거한 후에 조회

SELECT 1 num, 'abc' str
union
SELECT 1 num, 'abc' str;

• UNIALL 연산: 중복을 제거하지 않고 원래 데이터 그대로 사용.
  ex) A UNION B, A UNION ALL B

SELECT 1 num, 'abc' str
union all
select 1 num, 'abc' str;

2. 교집합

• INTERSECT 연산
  ex) A intersect B

SELECT c.first_name, c.last_name from customer c
where c.first_name like 'J%' and c.last_name like 'D%'
intersect
SELECT a.first_name, a.last_name from actor a
where a.first_name like 'J%' and a.last_name like 'D%';

3. 차집합

• EXCEPT 연산

• 똑같은 테이블 두개를 서로 순서를 변경할 경우, 결과는 다르게 나옴에 주의할 것.
  ex) A except B

SELECT c.first_name, c.last_name from customer c
where c.first_name like 'J%' and c.last_name like 'D%'
except
SELECT a.first_name, a.last_name from actor a
where a.first_name like 'J%' and a.last_name like 'D%';

4. 집합 연산시 고려사항

• 대상이 테이블이므로 칼럼의 개수, 칼럼의 데이터 타입을 고려해야 한다
• 두 데이터셋 모두 같은 수의 열을 가져야 하며, 각 열의 자료형은 서로 동일해야 한다.

-- 집합 연산의 정렬
SELECT c.first_name fname, c.last_name lname from customer c
where c.first_name like 'J%' and c.last_name like 'D%'
union all
SELECT a.first_name, a.last_name from actor a
where a.first_name like 'J%' and a.last_name like 'D%'
order by lname, fname;

B. 그룹화와 집계
SELECT 칼럼명
FROM 테이블 명
GROUP BY 칼럼
HAVING 조건

1. 집계 함수 예시

SELECT max(amount) max_amt,
       min(amount) min_amt, -- 0인경우가 존재함. 무조건 횟수 확인할 것.
       avg(amount) avg_amt,
       sum(amount) tot_amt,
       count(*) num_payments
from payment;

2. 그룹핑 함수 예시
   ! group by 절에 대한 조건은 무조건 having 절을 사용해야 함 !

SELECT customer_id, count(*) from rental r
group by customer_id
having count(*) >= 40;

C. 서브쿼리
1. in 연산자와 서브쿼리

SELECT city_id, city
from city
where country_id in (SELECT country_id
                     from country c
                     where country in ('Canada', 'Mexcio'));

2. 다중 열 서브쿼리

• 반환되는 결과가 다중 열인 서브쿼리

select fa.actor_id , fa.film_id
from film_actor fa inner join actor a
where (actor_id, film_id) in (
    /* 카테시안 프로덕트 (데카르트 곱) */
    select actor_id, film_id from actor a
           cross join film f
           where a.last_name = 'MONROE'
           and f.rating = 'PG'
    );

3. 상관 서브쿼리

• 메인 쿼리에서 사용한 데이터를 서브쿼리에서 사용하고 서브쿼리의 결과값을 다시 메인 쿼리로 반환하는 방식(성능이 낮다는 단점이 존재)
  !! 비상관 서브쿼리에는 서브쿼리가 독립적으로 실행이 됨.

  select c.customer_id, c.first_name, c.last_name
       from customer c
   where 20 = (
       select count(*)  --  > 렌탈 횟수를 where 절의 20과 비교
           from rental r
           where r.customer_id = c.customer_id
       );

				```
위 서브쿼리의 동작
1. 메인 쿼리에서 customer_id 를 모두 구함 -> 599 명의 고객의 ID 를 전부 조회
2. customer_id 를 sub query 에 제공.
   서브쿼리가 한번씩 실행이 되도록 customer_id 를 제공.
3. 서브쿼리에서 제공받은 customer_id 로 실행
4. 서브쿼리의 결과를 다시 메인 쿼리로 반환함.
5. 메인 쿼리에서 20번 대여 횟수가 동일한지 확인함. 
			```

D. all 연산자와 any 연산자
all 연산자: 서브쿼리에서 반환되는 여러개의 결과에서 모두 만족해야 함.

SELECT first_name, last_name from customer c
where customer_id <> all (SELECT customer_id
                          from payment p
                          where amount = 0);

any 연산자: 서브쿼리에서 반환되는 여러개의 결과중에서 한 가지만 만족해도 됨.

SELECT customer_id , sum(amount)
    from payment
    group by customer_id
    having sum(amount) > any(
    SELECT sum(p.amount)
    from payment p
    inner join customer c
    on p.customer_id = c.customer_id
    inner join address a
    on c.address_id = a.address_id
    inner join city ct
    on a.city_id = ct.city_id
    inner join country cy
    on ct.country_id = cy.country_id
    where cy.country in ('Bolivia', 'Paraguay' ,'Chile')
    group by cy.country) ;

E. EXIST 연산자

• exist 연산자 다음에 서브쿼리가 위치하고, 그 서브쿼리의 결과가 row의 수에 관계없이 존재 자체만 확인하고자 하는 경우에 사용.

SELECT c.customer_id,c.first_name, c.last_name
    from customer c
where exists(
    select 1
        from rental r
        where r.customer_id = c.customer_id
        and date(r.rental_date) <'2005-05-25'
);