[데이터분석 부트캠프] SQL session #4~5. UNION, JOIN

* UNION

   - UNION: 중복 제거

   - UNION ALL: 중복 = 모두 표기

   - 주의점

      1) 열 개수 동일

      2) 열 순서 동일

      3) 데이터 형식 동일  

SELECT ID, Name, ... 
FROM User
UNION (ALL) #수직결합
SELECT ID, Name, ...
FROM Order

 

* 숙제

문제
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서
1) 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. 
2) OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
3) 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
       PRODUCT_ID,
       USER_ID,
       SALES_AMOUNT
FROM(    SELECT SALES_DATE,
                PRODUCT_ID,
                USER_ID,
                SALES_AMOUNT
         FROM ONLINE_SALE 
       UNION ALL
         SELECT SALES_DATE,
                PRODUCT_ID,
                NULL AS USER_ID,
                SALES_AMOUNT
         FROM OFFLINE_SALE
    ) AS a
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

 

* JOIN

- 이름이 달라도 조인 가능

- 컬럼의 순서가 달라도 조인 가능

- 각 테이블의 데이터 값 개수가 달라도 조인 가능

- 두 테이블에서 같은 내용을 포함하고 있는 컬럼이 1개 이상 존재하면 조인 가능

 

* PK: 기본키

   - NULL 불가

   - 데이터 식별 기준

   - 테이블 당 하나만! 

   - 공통 컬럼일수도 아닐수도..!

 

* FK: 외래키

   - 테이블간 관계 알림

   - PK와 조인하기 위한 연결컬럼

 

* 조인 종류

- INNER JOIN: 두 테이블에서 일치하는 값

select 컬럼1, 컬럼2... 
from 테이블명1
inner join 테이블명2   
on a.공통컬럼=b.공통컬럼

 

- LEFT JOIN: 왼쪽테이블의 모든 값 + 왼쪽테이블 값이 있는 오른쪽 값 (오른쪽 값이 없을 경우 NULL로 표현)

select 컬럼1, 컬럼2... 
from 테이블1 as a # <<<< left에 위치한 테이블 = 기준 
left join basic.theglory2 as b
on a.공통컬럼=b.공통컬럼

 

* 숙제

1. 오랜 기간 보호한 동물(1)

문제:
1) 아직 입양을 못 간 동물 중,
2) 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요.
3) 이때 결과는 보호 시작일 순으로 조회해야 합니다.

SELECT i.NAME,
       i.DATETIME
FROM ANIMAL_INS i LEFT JOIN ANIMAL_OUTS o ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE o.ANIMAL_ID IS NULL 
ORDER BY i.DATETIME
LIMIT 3
;

 

2. 조건에 맞는 사용자와 총 거래금액 조회하기

문제: USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서
1) 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요.
2) 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

SELECT u.USER_ID,
       u.NICKNAME,
       SUM(b.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD b INNER JOIN USED_GOODS_USER u ON b.WRITER_ID = u.USER_ID
WHERE b.STATUS = 'DONE'
GROUP BY u.USER_ID
HAVING SUM(b.PRICE) >= 700000
ORDER BY SUM(b.PRICE)
;

 

3. 보호소에서 중성화한 동물

문제: 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다.
1) 보호소에 들어올 당시에는 중성화되지 않았지만,
2) 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

SELECT o.ANIMAL_ID,
       o.ANIMAL_TYPE,
       o.NAME
FROM ANIMAL_INS i INNER JOIN ANIMAL_OUTS o 
ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE i.SEX_UPON_INTAKE LIKE 'Intact%' 
      AND (o.SEX_UPON_OUTCOME LIKE 'Neutered%' 
           OR o.SEX_UPON_OUTCOME LIKE 'Spayed%')
ORDER BY o.ANIMAL_ID, o.ANIMAL_TYPE, o.NAME
;

 

4. 결제 여부에 따른 고객 수

문제: 아래 조건을 만족하여, 알맞은 조인방식으로 users 테이블과 payment 테이블을 조인해주세요.
1) case when 구문을 사용하여 결제를 한 게임계정과 결제를 하지 않은 게임계정을 구분해주시고,
    컬럼이름을 gb로 지정해주세요.
2) gb를 기준으로 게임계정수를 중복값없이 추출해주세요. 컬럼 이름은 usercnt로 지정해주세요.
3) payment 테이블에 게임계정이 있으면 결제한 고객으로 생각해주세요.

SELECT CASE WHEN p.game_account_id IS NOT NULL THEN '결제함'
            ELSE '결제안함'
       END AS gb,
       COUNT(DISTINCT u.game_account_id) AS usercnt
FROM users u LEFT JOIN payment p
ON u.game_account_id = p.game_account_id
GROUP BY gb 
;

 

5. 게임계정별 보유캐릭터 수 및 평균 결제금액

문제: 아래 조건을 만족하여,
1) 알맞은 조인방식으로 users 테이블과 payment 테이블을 조인해주시고,
2) 게임계정별 보유캐릭터수 및 평균 결제금액을 구해주세요.

조건1) users 테이블에서 서버번호가 2 이상인 데이터를 구해주세요.
          그리고, payment 테이블에서 결제방식이 CARD 인 경우의 게임계정별 결제금액 합계를 구하고 이름을 total_amt 로 지정해주세요. 그 다음,이 조건 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요.

조건2) 조인한 결과를 바탕으로 users 테이블의 게임계정을 기준으로 게임캐릭터수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 total_amt 값의 평균을 구해주시고, 컬럼 이름을 avg_amount로 지정해주세요.

조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요.
           그리고 avg_amount를 기준으로 내림차순 정렬해주세요. 

SELECT a.game_account_id,
       a.actor_cnt,
       AVG(b.total_amt) AS avg_amount
FROM 
     (     SELECT game_account_id, 
                  COUNT(DISTINCT game_actor_id) AS actor_cnt 
           FROM users 
           WHERE serverno >= 2 
           GROUP BY game_account_id
      ) a 
  INNER JOIN 
     (     SELECT game_account_id, 
                  SUM(pay_amount) AS total_amt 
           FROM payment 
           WHERE pay_type = 'CARD' 
           GROUP BY game_account_id
      ) b
  ON a.game_account_id = b.game_account_id
GROUP BY a.game_account_id 
ORDER BY avg_amount DESC
;

 

6. 서버별 마지막 접속일자 - 첫 접속일자의 평균

문제: 아래 조건을 만족하여, 서버별 마지막 접속일자-첫 접속일자 평균을 구해주세요.

조건1) user 테이블에서 게임계정, 첫 접속일자, 서버번호를 추출해주세요. 단, ip 주소가 70으로 시작하는 경우만 필터링해주세요. 그리고, 계정별로 첫 접속일자가 여러개인 경우는 알맞은 서브쿼리를 사용하여 계정당 가장 작은 날짜 1개만 반환해주세요.

조건2) 그 다음, payment 테이블에서 게임계정별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.

조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.

조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.

조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!

SELECT c.serverno,
       ROUND(AVG(c.diffdate), 0) AS avgdiffdate
FROM 
   (   SELECT a.serverno,
              a.date1,
              b.date2,
              DATEDIFF(b.date2, a.date1) AS diffdate
       FROM (   SELECT game_account_id,
                serverno,
                MIN(first_login_date) AS date1
                FROM basic.users
                WHERE ip_addr LIKE '70%'
                GROUP BY game_account_id, serverno
             ) AS a
             INNER JOIN 
             (   SELECT game_account_id,
                        MAX(approved_at) AS date2
                 FROM basic.payment p 
                 GROUP BY game_account_id
             ) AS b
             ON a.game_account_id  = b.game_account_id 
       WHERE b.date2 > a.date1        
   ) AS c
WHERE c.diffdate >= 10
GROUP BY c.serverno
ORDER BY c.serverno DESC
;