12/12 일지 - join 함수(세개 병합, 테이블 위치, 병합 기준 칼럼), datediff 및 날짜차이 구하기 주의점

SQL 카타

문제 58:

PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.

 

 

select b.apnt_no,
       a.pt_name,
       a.pt_no,
       b.mcdp_cd,
       b.dr_name,
       b.apnt_cncl_ymd
from patient a join (SELECT a.apnt_no apnt_no,
       a.pt_no pt_no,
       a.mcdp_cd mcdp_cd,
       b.dr_name dr_name,
       a.apnt_cncl_ymd apnt_cncl_ymd,
       a.apnt_cncl_yn apnt_cncl_yn,
       a.apnt_ymd apnt_ymd
from appointment a join doctor b on a.mcdp_cd=b.mcdp_cd) b on a.pt_no=b.pt_no
where b.apnt_cncl_ymd like '2022-04-13%' and b.apnt_cncl_yn = 'n'

 

세 개 이상의 테이블을 병합한 적이 없어서 서브쿼리를 써봤는데 너무 복잡하다보니 중간에 실수가 생긴건지 답이 제대로 나오질 않았다. '질문하기'에 올라온 글들에도 위와 같은 답변을 한 사람은 없었고, 답변들을 쭉 살펴보니 확실히 지난번에 공부한 대로 join 함수를 쓸 때 기준이 되는 테이블이 중요하다는 것을 알 수 있었다.

 

select a.apnt_no,
       p.pt_name,
       p.pt_no,
       a.mcdp_cd,
       d.dr_name,
       a.apnt_ymd
from appointment a inner join patient p on a.pt_no=p.pt_no inner join doctor d on d.tlno=p.tlno
where date_format(a.apnt_ymd, '%Y-%m-%d') = '2022-04-13'
and apnt_cncl_yn = 'N'
and a.mcdp_cd = 'cs'
order by a.apnt_ymd asc

이렇게 했더니 dr_name이 출력되지 않았다. doctor와 patient에만 있는 칼럼을 기준으로 patient를 추가 병합하려하니 doctor에 있는 dr_name이 조회되지 않았던 것.

추가로 더 검색하면서 알아보고 생각을 해보니 기준이 되는 테이블과 함께 병합 기준 칼럼 또한 중요하다는 것을 깨달았다. 깨달은 것을 토대로 아래와 같이 입력하니 드디어 정답이 나왔다

덧붙여서, 복잡하긴 하더라도 처음 했던 서브쿼리 활용 방식으로도 답을 도출해낼 수는 있을듯(복잡해서 굳이 하진 않았다)

 

select a.apnt_no,
       p.pt_name,
       p.pt_no,
       a.mcdp_cd,
       d.dr_name,
       a.apnt_ymd
from appointment a inner join patient p on a.pt_no=p.pt_no inner join doctor d on a.mddr_id=d.dr_id
where date_format(a.apnt_ymd, '%Y-%m-%d') = '2022-04-13'
and apnt_cncl_yn = 'N'
and a.mcdp_cd = 'cs'
order by a.apnt_ymd asc

 

결론 : 테이블 병합시 기준이 되는 테이블, 기준이 되는 칼럼 모두 중요하다!

 

 

 

문제60 :

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

SELECT year(o.sales_date) year,
       month(o.sales_date) month,
       u.gender,
       count(*) users
from online_sale o left join user_info u on o.user_id=u.user_id
where u.gender is not null
group by 1, 2, 3
order by year asc, month asc, u.gender asc

 

크게 어려울 건 없지만 자주 실수해왔고, 기록하지 않으면 앞으로도 자주 실수할 것 같아서 기록하는 문제.

처음에 위처럼 쓰고서 아무리 생각해봐도 전체적인 쿼리 구조에 이상이 없는 것 같아서 '질문하기'를 들어가서 보니

distinct로 user_id 중복값 처리를 누락했었다.

distinct 하나만 입력하니 그대로 문제 해결.

다른거 다 맞게 작성해놓고 이거 하나로 틀리는 일이 없게 해야한다....

SELECT year(o.sales_date) year,
       month(o.sales_date) month,
       u.gender,
       count(distinct(o.user_id)) users
from online_sale o left join user_info u on o.user_id=u.user_id
where u.gender is not null
group by 1, 2, 3
order by year asc, month asc, u.gender asc

 

 

 

문제 62. 자동차 대여 기록에서 장기/단기 대여 구분하기

datediff함수, 그리고 날짜 차이 구하기 주의점

SELECT history_id,
       car_id,
       date_format(start_date, '%Y-%m-%d') start_date,
       date_format(end_date, '%Y-%m-%d') end_date,
       if((cast(end_date as decimal)-cast(start_date as decimal))>=30, '장기 대여', '단기 대여') rent_type
from car_rental_company_rental_history
where start_date like '2022-09%'
order by history_id desc

 

처음엔 위와 같이 풀었는데 생각해보니, 그리고 '질문하기'에 올라온 답변들을 찾아보니 굳이 cast 함수를 통해 날짜를 일반 숫자로 바꿔줄 필요는 없는 것 같다. 또한 단순 숫자로 변환한 날짜끼리 빼면 날짜끼리 뺀 것과 오차가 발생할 수도 있겠단 생각이 들었다.

SELECT history_id,
       car_id,
       date_format(start_date, '%Y-%m-%d') start_date,
       date_format(end_date, '%Y-%m-%d') end_date,
       if(end_date-start_date>=29, '장기 대여', '단기 대여') rent_type
from car_rental_company_rental_history
where start_date like '2022-09%'
order by history_id desc

그래서 위와 같이 써봤는데 또 오답. 시작일을 기준으로 하루를 카운트해야하기에 날짜를 뺀 것에 대한 비교값을 30으로 해주었다가 '질문하기'에 달린 답변을 통해 29로 바꿔주었지만 여전히 오답이 나왔다. 날짜끼리 단순 빼준 것은 확실히 오차가 있는 듯 하다.

그래서 더 검색해보다가 나온 datediff 함수.

 

datediff('기준 날짜', '빼줄 날짜)

 

아무런 어려운 것 없이 날짜끼리 차이, 즉 뺀 값을 산출해내는 함수였다.

그에 따라 아래처럼 해주니 바로 답이 도출됐다.

SELECT history_id,
       car_id,
       date_format(start_date, '%Y-%m-%d') start_date,
       date_format(end_date, '%Y-%m-%d') end_date,
       if(datediff(end_date, start_date) >=29, '장기 대여', '단기 대여') rent_type
from car_rental_company_rental_history
where start_date like '2022-09%'
order by history_id desc

 

 

 

---

 

SQLD 5주차

 

대량 데이터에 따른 성능

-성능 저하현상 : (1)로우 체이닝 : 행 데이터가 길어서 2개 이상 블록에 저장되는 경우
                         (2)로우 마이그레이션 : 블록에서 수정이 발생할시 해당 블록 아닌 다른 블록 빈 공간에 저장되는 경우
1)수직분할 : 한 테이블에 많은 칼럼 있을 시 수행
2)수평분할(파티셔닝)
 (1)범위분할 : 기간 중심으로 분할 ex)통신요금 월별로 분할
 (2)목록분할 : 구역, 지역 등으로 분할 ex)고객리스트 거주지 별로 분할
 (3)해시분할 : 관리보단 성능 향상에 중점을 둠
 (4)합성분할 : 위의 3개 중 2개 이상 활용
3)테이블 분할 절차 : (1)DB모델링
                               (2)DB테이블 용량 산정
                               (3)트랜젝션 처리 패턴 분석
                               (4)치리과정 칼럼/로우 중 집중 부분 분석 및 파티셔닝
  

데이터베이스의 구조와 성능

-PK 순서와 성능 사이의 관계 : 복합 PK에서 칼럼의 순서와 SQL 내 조건문 간 관계 중요
 ex)테이블은 A-B-C 칼럼의 순서로 되어 있는데 조건문에서 B칼럼의 조건만 걸어줄 경우 A 전체 조회한 뒤 B조건 찾음
     ->B칼럼을 맨 앞으로 하면 조건에 맞는 칼럼 찾는 속도 향상됨
 

 분산DB : 

논리적으로 동일하나 네트워크 통해 물리적으로 분산된 데이터의 모임