SQL

71-73

whateveryouwish 2024. 8. 12. 19:45

71번

문제 설명
다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과
오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다.
ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며
ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는
각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

Column name		Type		Nullable
ONLINE_SALE_ID		INTEGER		FALSE
USER_ID			INTEGER		FALSE
PRODUCT_ID		INTEGER		FALSE
SALES_AMOUNT		INTEGER		FALSE
SALES_DATE		DATE		FALSE

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있으며
OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는
각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

Column name		Type	Nullable
OFFLINE_SALE_ID		INTEGER	FALSE
PRODUCT_ID		INTEGER	FALSE
SALES_AMOUNT		INTEGER	FALSE
SALES_DATE		DATE	FALSE

동일한 날짜, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

문제

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

 

 

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
	, PRODUCT_ID
       , USER_ID
       , SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
	, PRODUCT_ID
       , NULL AS USER_ID
       , SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

 

UNION ALL 과 UNION

1) UNION ALL

 

UNION ALL은 SQL에서 두 개 이상의 SELECT 쿼리의 결과를 결합하는 데 사용되는 연산자입니다. 주로 여러 테이블에서 데이터를 통합할 때 유용합니다. UNION ALL은 다음과 같은 특징을 가지고 있습니다.

특징

  1. 중복 데이터 포함: UNION ALL은 각 SELECT 쿼리에서 반환된 모든 행을 포함합니다. 즉, 중복된 행도 결과에 포함됩니다. 반면, UNION은 중복된 행을 제거합니다.
  2. 성능: UNION ALL은 중복 행을 제거하는 과정이 없기 때문에 UNION보다 성능이 더 뛰어납니다. 데이터의 양이 많을 때 성능 차이가 더욱 두드러집니다.
  3. 컬럼 수와 데이터 타입: 조합되는 모든 SELECT 쿼리는 동일한 수의 컬럼을 가져야 하며, 각 컬럼의 데이터 타입도 호환 가능해야 합니다. 즉, 첫 번째 SELECT 문에서 반환한 컬럼의 데이터 타입과 두 번째 SELECT 문에서 반환한 컬럼의 데이터 타입이 서로 호환되어야 합니다.

사용 예시

SELECT 
    PRODUCT_ID, 
    SALES_AMOUNT 
FROM 
    ONLINE_SALE 
WHERE 
    SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'

UNION ALL

SELECT 
    PRODUCT_ID, 
    SALES_AMOUNT 
FROM 
    OFFLINE_SALE 
WHERE 
    SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31';

위 예시에서, ONLINE_SALE과 OFFLINE_SALE 테이블의 2022년 3월 판매 데이터를 결합하여 두 테이블의 모든 판매 데이터를 포함한 결과를 반환합니다. 중복된 상품 ID와 판매량이 있을 수 있으며, 이러한 중복도 결과에 포함됩니다.

결론

UNION ALL은 중복을 포함하여 여러 SELECT 쿼리의 결과를 결합할 때 유용하며, 성능상의 이점을 제공하는 경우가 많습니다. 데이터 분석이나 보고서 작성 시 여러 출처에서 데이터를 통합할 때 자주 사용됩니다.

 

2)

 

UNION은 SQL에서 두 개 이상의 SELECT 쿼리의 결과를 결합하는 데 사용되는 연산자입니다. UNION의 주요 특징은 다음과 같습니다.

특징

  1. 중복 제거: UNION은 여러 SELECT 쿼리에서 반환된 결과 중 중복된 행을 자동으로 제거합니다. 따라서 결과 집합은 고유한 행만 포함됩니다. 반면, UNION ALL은 중복을 포함합니다.
  2. 컬럼 수와 데이터 타입: 결합되는 모든 SELECT 쿼리는 동일한 수의 컬럼을 가져야 하며, 각 컬럼의 데이터 타입도 호환 가능해야 합니다. 즉, 첫 번째 SELECT 문에서 반환한 컬럼의 데이터 타입과 두 번째 SELECT 문에서 반환한 컬럼의 데이터 타입이 서로 호환되어야 합니다.
  3. 정렬: UNION으로 결합된 결과는 기본적으로 정렬되지 않습니다. 결과를 정렬하려면 ORDER BY 절을 사용해야 합니다. ORDER BY는 마지막 SELECT 쿼리 다음에 위치해야 합니다.

사용 예시

SELECT 
    PRODUCT_ID, 
    SALES_AMOUNT 
FROM 
    ONLINE_SALE 
WHERE 
    SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'

UNION

SELECT 
    PRODUCT_ID, 
    SALES_AMOUNT 
FROM 
    OFFLINE_SALE 
WHERE 
    SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31';

위 예시에서, ONLINE_SALE과 OFFLINE_SALE 테이블의 2022년 3월 판매 데이터를 결합하여 두 테이블의 고유한 판매 데이터를 포함한 결과를 반환합니다. 중복된 상품 ID와 판매량은 하나만 포함됩니다.

결론

UNION은 여러 SELECT 쿼리의 결과를 통합하여 중복을 제거한 고유한 집합을 생성할 때 유용합니다. 데이터 분석이나 보고서 작성 시 여러 출처에서 데이터를 통합할 때 자주 사용됩니다. 중복을 허용하지 않으므로, 결과의 개수가 줄어들 수 있습니다.

72번

 

문제 설명
다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과
중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_REPLY 테이블입니다.
USED_GOODS_BOARD 테이블은 다음과 같으며
BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은
게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

Column name	Type	Nullable
BOARD_ID	VARCHAR(5)	FALSE
WRITER_ID	VARCHAR(50)	FALSE
TITLE		VARCHAR(100)	FALSE
CONTENTS	VARCHAR(1000)	FALSE
PRICE		NUMBER		FALSE
CREATED_DATE	DATE		FALSE
STATUS		VARCHAR(10)	FALSE
VIEWS		NUMBER		FALSE

USED_GOODS_REPLY 테이블은 다음과 같으며
REPLY_ID, BOARD_ID, WRITER_ID, CONTENTS, CREATED_DATE는
각각 댓글 ID, 게시글 ID, 작성자 ID, 댓글 내용, 작성일을 의미합니다.

Column name	Type		Nullable
REPLY_ID	VARCHAR(10)	FALSE
BOARD_ID	VARCHAR(5)	FALSE
WRITER_ID	VARCHAR(50)	FALSE
CONTENTS	VARCHAR(1000)	TRUE
CREATED_DATE	DATE		FALSE

문제
USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서
2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을
조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고,
댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

 

SELECT title, b.board_id, reply_id, r.writer_id, r.contents
       , date_format(r.created_date, '%Y-%m-%d') as created_date
from used_goods_board b
join used_goods_reply r on b.board_id = r.board_id
where b.created_date like '2022-10%'
order by 6, 1

 

70번대부터 쉬워진다고 했는데... 빛을 보는 건가 나도!?

 

하자마자 이 문제를 맞닥뜨렸다.

73번

문제 설명
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.
ANIMAL_OUTS 테이블 구조는 다음과 같으며,
ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는
각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME			TYPE		NULLABLE
ANIMAL_ID		VARCHAR(N)	FALSE
ANIMAL_TYPE		VARCHAR(N)	FALSE
DATETIME		DATETIME	FALSE
NAME			VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다.
0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.
이때 결과는 시간대 순으로 정렬해야 합니다.

 

SELECT date_format(datetime, '%H') as HOUR, count(animal_id) as COUNT
from animal_outs
group by 1
order by 1

 

이게 웬 떡이냐 하면서 풀었다.

출력 결과물을 보고 문제 정답 예시를 보는데 내 결과와는 달리 00시부터 23시까지 다 출력이 되어야 했다.

이리저리 시도를 해봤으나, 답을 찾기 힘들었다.

그러던 중 질문하기 버튼을 봤는데 197개나 있는 게 아닌가...

 

알고 보니 with cte를 써야하는 문제였다...

전 조원들이 그렇게 강조했던 관문이 여기였구나 싶다.

 

차근차근 CTE를 다시 배워보았다.

CTE란

WITH 문은 SQL에서 서브쿼리를 정의할 때 사용하는 구문으로, 일반적으로 "공통 테이블 표현식(Common Table Expression, CTE)"이라고도 합니다. WITH 문을 사용하면 복잡한 쿼리를 더 간결하게 작성하고, 쿼리의 가독성을 높일 수 있습니다.

기본 구문

WITH CTE_NAME AS (
    -- 서브쿼리 정의
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM CTE_NAME
WHERE another_condition;

특징

  1. 가독성 향상: 복잡한 쿼리를 여러 부분으로 나눌 수 있어 가독성이 높아집니다.
  2. 재사용성: 정의한 CTE를 동일한 쿼리 내에서 여러 번 사용할 수 있습니다.
  3. 재귀적 쿼리: CTE를 사용하여 재귀적인 쿼리를 작성할 수 있습니다.

사용 예시

예를 들어, 동물 보호소에서 입양된 동물의 수를 동물의 종류별로 집계하는 쿼리를 작성할 수 있습니다.

WITH AnimalCounts AS (
    SELECT 
        ANIMAL_TYPE, 
        COUNT(ANIMAL_ID) AS COUNT
    FROM 
        ANIMAL_OUTS
    GROUP BY 
        ANIMAL_TYPE
)
SELECT 
    ANIMAL_TYPE, 
    COUNT
FROM 
    AnimalCounts
ORDER BY 
    COUNT DESC;

설명

  1. CTE 정의: AnimalCounts라는 이름의 CTE를 정의하여 각 동물의 종류별로 입양된 동물의 수를 계산합니다.
  2. 주 쿼리: CTE에서 반환된 결과를 사용하여 동물 종류와 그 수를 선택하고, 수에 따라 내림차순으로 정렬합니다.

결론

WITH 문은 SQL 쿼리를 더 읽기 쉽게 만들고, 복잡한 쿼리를 관리하는 데 유용한 도구입니다. 특히 재귀적 쿼리 작성이나 반복적인 서브쿼리 사용이 필요한 경우 매우 효과적입니다.

 

WITH RECURSIVE를 사용한 재귀적인 쿼리는 주로 계층적 데이터를 처리하거나 반복적인 계산을 수행할 때 유용합니다. 기본 구문과 예시를 아래에 설명하겠습니다.

기본 구문

 
WITH RECURSIVE CTE_NAME AS (
    -- 기본 쿼리 (재귀의 시작점)
    SELECT initial_value AS column_name
    FROM source_table
    WHERE condition
    
    UNION ALL
    
    -- 재귀 쿼리
    SELECT expression
    FROM CTE_NAME
    WHERE condition
)
SELECT *
FROM CTE_NAME;

예시

예를 들어, 1부터 10까지의 숫자를 생성하는 재귀적인 CTE를 작성할 수 있습니다.

 
WITH RECURSIVE NumberCTE AS (
    -- 기본 쿼리: 1로 시작
    SELECT 1 AS num
    
    UNION ALL
    
    -- 재귀 쿼리: 이전 숫자에 1을 더함
    SELECT num + 1
    FROM NumberCTE
    WHERE num < 10
)
SELECT *
FROM NumberCTE;

설명

  1. CTE 정의 시작: WITH RECURSIVE NumberCTE AS로 재귀 CTE를 정의합니다. CTE의 이름은 NumberCTE입니다.
  2. 기본 쿼리:
    • SELECT 1 AS num에서 초기값을 1로 설정합니다. 이 값이 재귀의 시작점이 됩니다.
  3. 재귀 쿼리:
    • SELECT num + 1 FROM NumberCTE WHERE num < 10에서 num이 10보다 작을 때만 num에 1을 더한 값을 반환합니다. 이로 인해 CTE는 1부터 10까지의 숫자를 생성하게 됩니다.
  4. 메인 쿼리:
    • SELECT * FROM NumberCTE에서 생성된 숫자를 모두 선택하여 결과를 출력합니다.

결과

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

num
---
1
2
3
4
5
6
7
8
9
10

이처럼 WITH RECURSIVE를 사용하면 반복적인 계산이나 계층적 데이터를 처리하는 데 매우 효과적입니다.

 

https://kimsyoung.tistory.com/entry/SQL-CTE%EC%9D%98-%EB%AA%A8%EB%B2%94-%EC%82%AC%EB%A1%80%EB%8A%94-%EB%AC%B4%EC%97%87%EC%9D%BC%EA%B9%8C%EC%9A%94

 

SQL CTE를 잘 활용하려면?

SQL문을 작성하면서 어떤 경우에 CTE를 사용하면 좋을지 궁금한 적이 있었나요? 이 글은 언제 CTE를 사용하면 좋을지, 그리고 어떻게 사용하면 좋을지를 다뤄볼 것입니다. 만약 여러분이 SQL CTEs에

kimsyoung.tistory.com

여기에도 굉장히 잘 풀어서 설명해주셨다.

 

그래서 73번 답은

WITH RECURSIVE cte AS (
    SELECT 0 AS num

    UNION ALL

    SELECT num + 1
    FROM cte
    WHERE num < 23
), ANIMAL_OUTS_REFACTOR AS (
    SELECT HOUR(DATETIME) AS HOUR
         , COUNT(ANIMAL_ID) AS CNTS
    FROM ANIMAL_OUTS
    GROUP BY HOUR
    ORDER BY HOUR
)

SELECT cte.num
     , IFNULL(O.CNTS, 0)
FROM cte
    LEFT JOIN ANIMAL_OUTS_REFACTOR AS O ON cte.num = O.HOUR

https://school.programmers.co.kr/questions/71017

 

프로그래머스

코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.

programmers.co.kr

에서 가져왔다.

배우기 위해...

WITH RECURSIVE time AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 
    FROM time 
    WHERE hour < 23
), animal AS (
    SELECT HOUR(datetime) AS hour
         , COUNT(*) AS count
    FROM animal_outs AS a 
    GROUP BY hour
    ORDER BY hour
)

SELECT time.hour
     , CASE WHEN animal.count IS NULL THEN 0 ELSE animal.count END AS count
FROM time
    LEFT JOIN animal ON time.hour = animal.hour
GROUP BY time.hour
ORDER BY time.hour

https://school.programmers.co.kr/questions/51510

 

프로그래머스

코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.

programmers.co.kr

이 풀이도 굉장히 깔끔하다.

 

이 풀이 방법들을 참고해서 차근차근 CTE구문을 학습해야겠다..!

 

 

WITH RECURSIVE time as (
    select 0 as num
    
    union all
    
    select num + 1
    from time
    where num < 23
), animal_outs_cnts as (
    select hour(datetime) as hours
           , count(animal_id) as count
    from animal_outs
    group by 1
    order by 1
)

SELECT time.num as hour
       , IFNULL(O.count, 0) as count
from time
join animal_outs_cnts o on time.num = o.hours

 

혼자 이렇게 쿼리를 짰는데, 아무리 계속 바꿔가며 실행을 시켜봐도 여전히 count가 0인 시간대는 출력이 안된다.

정답은 left join을 쓰는 것에 있었다.

아무리 cte 재귀쿼리를 이용해서 시간대를 만들어놔도 그것을 left join을 통해 전부 다 가져오지 않으면 출력되지 않는 것이다...

join만 써도 많은 문제들이 풀리기에 이 개념이 갈라지는 문제가 나올 때 정확하게 습득해야지 했었는데

이 문제가 그런 계기가 된 것 같다.

 

더 찾아보니 COALESCE 함수를 사용하는 방법이 있었다.

 

IFNULL 대신에 COALESCE 함수를 사용하는 방법

COALESCE 함수는 주어진 인자 중 첫 번째로 NULL이 아닌 값을 반환합니다. 이 경우에도 동물이 없는 시간대에 대해 0을 표시하려면 COALESCE를 사용할 수 있습니다.

수정된 쿼리 예시

아래는 IFNULL 대신 COALESCE를 사용한 쿼리입니다:

 
WITH RECURSIVE time AS (
    SELECT 0 AS num
    
    UNION ALL
    
    SELECT num + 1
    FROM time
    WHERE num < 23
), animal_outs_cnts AS (
    SELECT HOUR(datetime) AS hours
           , COUNT(animal_id) AS count
    FROM animal_outs
    GROUP BY 1
    ORDER BY 1
)

SELECT time.num AS hour
       , COALESCE(O.count, 0) AS count
FROM time
LEFT JOIN animal_outs_cnts O ON time.num = O.hours;

설명

  1. COALESCE 사용:
    • COALESCE(O.count, 0)를 사용하여 O.count가 NULL일 경우 0을 반환하도록 설정했습니다. 즉, 동물이 없는 시간대에는 O.count가 NULL이 되므로, 이때 0이 출력됩니다.

COALESCE의 장점

  • COALESCE는 두 개 이상의 인자를 받을 수 있어서, 여러 값 중 첫 번째로 NULL이 아닌 값을 찾을 수 있습니다. 예를 들어, COALESCE(value1, value2, 0)와 같이 사용할 수 있습니다.
  • IFNULL은 두 개의 인자만 받을 수 있으므로, 더 복잡한 조건에서 유용한 COALESCE를 사용하는 것이 좋습니다.

이렇게 수정하면 동물이 없는 시간대에 대해 0으로 표시되는 결과를 동일하게 얻을 수 있습니다.