2024_01_26 TIL

1. 날짜간의 차이를 계산할 때 유의할 점에 대해서

 

sql 코드 카타 문제 중에 날짜 간의 차이를 묻는 문항은 크게 두가지의 종류가 있다.

 

첫번째는 시작일로부터 끝나는 날까지 만으로 몇일이 경과하였는지 묻는 경우가 있고,

두번째는 시작일과 끝나는 날이 있을 때, 이 상품이 렌트된 기간을 구하는 경우가 있다.

 

첫번째의 경우는 단순히 datediff 함수를 이용하여 두 날짜간의 순수 간격을 구하면 되지만

두번째의 경우에서 보통 렌트 기간이라 함은 물건이 누군가에 의해 빌려져 다른 고객이 이용할 수 없는

총 기간을 구하는 것이 중요한 문제이다.

이 경우에는 렌트를 시작한 날짜, 끝나는 날짜가 모두 다른 사람에 의해 렌트할 수 없는 렌트 기간안에

포함되어있으므로 단순히 datediff 를 통해서 계산해준다면 실제 렌트 기간보다 1 적은 날짜가 나오게 된다.

 

따라서 렌트된 기간을 묻는 문항이 있다면, 꼭 datediff 함수를 계산해준뒤 1을 더하여,

렌트가 시작된 날짜 기간안에 포함되도록 해주어야 한다. 

## 간격을 구한뒤에 1을 더해주는 쿼리문
datediff(end_date, start_date) + 1

 

 

------------------------------------------------------------------------------------------------------------------------------

2. round 함수 복습

 

여러 값들에 대한 평균등의 연산을 하게되면 딱 떨어지지 않는 경우가 대부분이므로

이에 대한 값을 소수점 밑으로 모두 적어주기 보다는 가기성이 높도록 소수점 하나 혹은 두번째 자리에서

반올림하여 표기하는 것이 일반적이다.

 

이를 위해서 사용하는 함수가 round 함수이다.

round 함수는 다음과 같은 쿼리문으로 사용할 수 있다.

round('반올림하고 싶은 컬럼명' , 반올림할 소수점 자리수:한자리서 반올림할때 기준 0 )

 

 

참고 : https://thebook.io/006977/0129/

 

 

---------------------------------------------------------------------------------------------------------------------------

3.  having 절에 대하여

 

일반적으로 원하는 값들만 걸러주기 위해서는 where 와 조건을 적어주는 절이 사용된다

하지만 sql 쿼리문에서 where 은 from에서 select 절로 넘어가기 전 먼저 데이터를 걸러주는 절이므로

select 에서 기존 테이블의 컬럼들을 사용해 새로운 컬럼을 사용했다면

이 새로운 컬럼을 where 절을 통해서 조건으로써 적용할 수 없다. 

 

이에 불가능한 예시는 다음과 같다.

select car_id,
       round(avg((datediff(end_Date, start_date) +1)), 1) as average_duration
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where average_duration >= 7
group by car_id
order by 2 desc, 1 desc

 

이 쿼리문에서 where 절이 실행될 때는 실행 순서상 아직 average_duration 에 대한 정보가 없는 상황으로

당연히 where 절에서 이 컬럼을 조건으로 넣어줄 수 없는 것이다.

 

하지만 이 경우에 having 절을 이용하여 다음과 같이 select 에서 average_duration 을 정의해준 뒤에

이것을 조건으로서 적용하여 걸러줄 수 있다.

select car_id,
       round(avg((datediff(end_Date, start_date) +1)), 1) as average_duration
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
having average_duration >= 7
order by 2 desc, 1 desc

 

 

 

-----------------------------------------------------------------------------------------------------------------------------

4. MySQL 코드카타 문제 풀이 64번 - where 과 join 절 안에 서브 쿼리문 넣기

 

문제 : 

 

이 문제를 풀고자 기본적으로 group by 함수를 사용하고 having 절을 사용하여

host_id 가 2개 이상인 것을 count 하여 출력할 생각이었다.

 

처음에는 이를 서브 쿼리문을 사용하여 해결할려고 생각하였으나,

다음과 같은 서브 쿼리문을 사용하면, 서브 쿼리문에서 만든 데이터 테이블에선

NAME 컬럼에 값이 각각 하나씩 밖에 남지 않으므로 서브 쿼리문을 이용해서 새로운 데이터 테이블을 만들어 줄 때,

이미 없어져 버린 NAME 테이블의 다른 값들을 가져올 수 가 없었다.

 

그래서 기존 테이블을 조인에서 이용하고자 위의 쿼리문을 임시 테이블로써 생성하고

그 임시 테이블과 기존에 NAME값이 누락되지 않고 남아있는 테이블을 조인하여

문제의 정답을 구할 생각을 했다.

 

하지만 코드카타 문제의 특성상 데이터 베이스에 새로운 테이블을 만드는 것이 허용되지 않았고

따라서 테이블을 새로 생성하지 않고 이 문제를 해결하는 방법을 찾아내야만 했다.

 

이를 하나의 쿼리문으로 해결하는 방법은 크게 두가지가 있다.

첫번째는 where 절 안에 서브 쿼리문을 넣어주는 것이다

SELECT ID, NAME, HOST_ID 
FROM PLACES 
WHERE HOST_ID IN (
                    SELECT HOST_ID
                    FROM PLACES 
                    GROUP BY HOST_ID 
                    HAVING COUNT(*) >= 2
                 )
ORDER BY ID

 

위의 쿼리문을 보면 where 절 안에 새로운 테이블을 조회하는 서브쿼리문이 들어간 것을 확인할 수 있다.

where 절 안의 코드를 각각 해석하면 다음과 같다.

 

where HOST_ID in ( )

 

where 절 안에서 in 을 사용한다는 것의 의미는

특정 열이 여러 개의 값 중 하나와 일치하는지를 확인하는데 사용한다는 것이다.

여기서는 HOST_ID in () 을 통해 컬럼의 모든 HOST_ID 중

괄호 안에 들어가있는 테이블에서 select 절을 통해 결과값으로 나오는 HOST_ID 과 일치하는 HOST_ID 만을

where 절을 통해 선택해서 사용하겠다는 것을 의미한다.

 

그 뒤에 나오는 괄호 안의 서브쿼리문은

 

SELECT HOST_ID

FROM PLACES 
GROUP BY HOST_ID 
HAVING COUNT(*) >= 2

 

인데, 이 때, 주의해야 할 점은 select 절안에는 무조건 HOST_ID 하나만 남겨놔야한다는 점이다.

앞에서 기존 테이블의 HOST_ID 와 일치하는 것만을 찾기 위한 서브 쿼리문인 만큼

서브 쿼리문 안의 테이블에는 HOST_ID 딱 하나만 남겨놓아야지 오류 없이

컴퓨터가 HOST_ID 컬럼 안에서 겹치는 값만을 찾아줄 수 있다.

 

 

두번째 방법은 바로 from 쿼리문 안에 일반적으로 쓰는 join 을 하는 대신

그 join 안에 서브쿼리문을 만들어 앞에서 내가 시도하고자 한 테이블을 만들고 조인하는 것을

한번에 해결하는 방식이다.

 

조인하는 방식은 일반적인 쿼리문과 완전히 같으며, 기존에 그냥 테이블 명을 적어주었던 것을

서브 쿼리문으로 대체하는 것 뿐이다

해결한 쿼리문은 다음과 같다

SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES P
JOIN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(*) >= 2) CP
ON P.HOST_ID = CP.HOST_ID
ORDER BY P.ID

 

 

 

-----------------------------------------------------------------------------------------------------------------