CodeKata

SQL

56. 특정 옵션이 포함된 자동차 리스트 구하기

• 작성 쿼리

SELECT
  *
FROM
  car_rental_company_car
WHERE
  options LIKE '%네비게이션%'
ORDER BY
  car_id DESC
;

• LIKE 대신 REGEXP 써서 options REGEXP '네비게이션'이라고 해도 됨

57. 조건에 부합하는 중고거래 상태 조회하기

• 작성 쿼리

SELECT
  board_id
  , writer_id
  , title
  , price
  , CASE
    WHEN `status` = 'SALE' THEN "판매중"
    WHEN `status` = 'RESERVED' THEN "예약중"
    WHEN `status` = 'DONE' THEN "거래완료"
    ELSE "미분류"
  END AS "STATUS"
FROM
  used_goods_board
WHERE
  DATE(created_date) = '2022-10-05'
ORDER BY
  board_id DESC
;

• WHEN status = 'DONE' THEN "거래완료" 대신 ELSE "거래완료"로 작성해도 됨
• DATE(created_date) = '2022-10-05' 대신 created_date = '2022-10-05'이나 DATE_FORMAT(created_date, '%Y-%m-%d') = '2022-10-05 써도 됨

참고할 만한 내용

• IF를 중첩해서 쓴 풀이도 있었음
  IF (STATUS = 'SALE', "판매중", IF(STATUS = 'RESERVED', "예약중", "거래완료")) AS STATUS

• CASE문을 이런 식으로 써도 된다고 함

    CASE `STATUS`
        WHEN 'SALE' THEN '판매중'
        WHEN 'RESERVED' THEN '예약중'
        ELSE '거래완료'
    END AS `STATUS`

58. 취소되지 않은 진료 예약 조회하기

• 문제
  • PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.
    • '2022-04-13'
    • 취소되지 않은: apnt_cncl_yn = 'N'
      ※ 해당 컬럼 공란 존재 → 취소로 간주?
    • CS(흉부외과)

appointment	patient	doctor
진료예약일시, 진료예약번호, 환자번호, 진료과코드, 의사ID, 예약취소여부, 예약취소날짜	환자이름, 환자번호	의사ID, 의사이름

SELECT
  a.apnt_no
  , p.pt_name
  , a.pt_no
  , a.mcdp_cd
  , d.dr_name
  , a.apnt_ymd
FROM
  appointment a
  JOIN patient p
  JOIN doctor d
  ON a.pt_no = p.pt_no
  AND a.mddr_id = d.dr_id
WHERE
  DATE(a.apnt_ymd) = '2022-04-13'
  AND a.apnt_cncl_yn = 'N'
  AND a.mcdp_cd = 'cs'
ORDER BY
  a.apnt_ymd
;

• WITH 이용

WITH apnt AS (
  SELECT
    *
  FROM
    appointment
  WHERE
    DATE_FORMAT(apnt_ymd, '%Y-%m-%d') = '2022-04-13'
    AND mcdp_cd = 'cs'
    AND apnt_cncl_yn = 'N'
)
SELECT
  apnt_no
  , pt_name
  , apnt.pt_no
  , apnt.mcdp_cd
  , dr_name
  , apnt_ymd
FROM
  apnt
  JOIN patient p
  ON apnt.pt_no = p.pt_no
  JOIN doctor d
  ON apnt.mddr_id = d.dr_id
ORDER BY
  apnt_ymd
;

59. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

• 작성 쿼리

SELECT
  car_id
  , IF(MAX('2022-10-16' BETWEEN start_date AND end_date), '대여중', '대여 가능') AS availability
FROM
  car_rental_company_rental_history
GROUP BY
  car_id
ORDER BY
  car_id DESC
;

🡆 '2022-10-16' BETWEEN start_date AND end_date는 start_date와 end_date 사이에 2022년 10월 16일이 있으면 1(true), 없으면 0(false) 반환

🡆 car_id별로 그룹화(GROUP BY) 한 후 MAX 집계함수를 사용하면 한 번이라도 대여 기간에 2022년 10월 16일이 포함되어 있었던 차는 1, 그렇지 않은 차는 0으로 표시됨

그룹화 시 MAX() 함수 사용 이유

• MAX() 함수는 그룹화 된 데이터에서 가장 큰 값을 반환
  • 조건이 참인 값이 존재하하는지 확인하는 데 사용
  • 그룹 내에서 해당 조건이 한 법이라도 만족되는 경우가 있는지 확인할 수 있음
• 각 자동차는 여러 번 대여될 수 있어 대여 기록이 여러 개임 → car_id를 기준으로 그룹화하여 각 자동차에 대한 여러 기록을 하나로 묶어 처리

다른 풀이

WITH cte AS (
  SELECT
    DISTINCT car_id
  FROM
    car_rental_company_rental_history
  WHERE
    car_id NOT IN (
      SELECT
        DISTINCT car_id
      FROM
        car_rental_company_rental_history
      WHERE
        start_date <= '2022-10-16'
        AND '2022-10-16' <= end_date
    )
)
SELECT
  h.car_id
  , CASE
    WHEN c.car_id IS NULL THEN '대여중'
    ELSE '대여 가능'
  END AS availability
FROM
  car_rental_company_rental_history h
  LEFT JOIN cte c
  ON h.car_id = c.car_id
GROUP BY
  h.car_id
ORDER BY
  h.car_id DESC
;

WITH RENTAL_STATUS AS (
    SELECT DISTINCT CAR_ID,
           CASE 
               WHEN EXISTS (
                   SELECT 1
                   FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                   WHERE CAR_ID = CRCRH.CAR_ID
                   AND '2022-10-16' BETWEEN START_DATE AND END_DATE
               ) THEN '대여중'
               ELSE '대여 가능'
           END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
)
SELECT CAR_ID, AVAILABILITY
FROM RENTAL_STATUS
ORDER BY CAR_ID DESC;

SELECT CAR_ID, IF(SUM(IF (START_DATE <= "2022-10-16" AND END_DATE >= "2022-10-16", 1, 0)), "대여중", "대여 가능") AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

• 문자열에 MAX() 사용하는 게 잘 이해가 안돼서 IF SUM IF 중첩시켜 작성하셨다고 함
  • 가장 안쪽 IF에서 10월 16일 대여 중인 경우 1, 아닌 경우 0으로 표시
  • SUM에서 그룹 별로 내부 IF 결과 값 합산
  • 만약 0인 경우, 해당 ID의 차량의 대여 기록에서 10월 16일 대여 기록이 없음
  • 1인 경우, 해당 ID의 차량의 대여 기록에서 10월 16일 대여 기록이 존재

Python

12. 평균 구하기

• 작성 코드

def solution(arr):
    return sum(arr)/len(arr)

참고할 만한 내용

• 문제 제한사항에 arr은 길이 1 이상, 100 이하인 배열이라고 명시되어 있어 간단히 작성할 수 있지만 이와 같은 조건이 없을 경우 ZeroDivisionError를 피하기 위해 아래와 같이 코드를 작성하면 좋음

def solution(arr):
	if len(arr) == 0:
    	return 0
    else:
    	return sum(arr) / len(arr)
        
 # 같은 풀이 다른 작성법
 def solution(arr):
   retirm sum(arr) / len(arr) if arr else 0

• statistics 모듈의 mean() 함수를 사용

from statistics import mean
def solution(arr):
	return mean(arr)

데이터 리터러시

1-3 데이터의 유형

SQL 라이브 세션

7회차

데이터 분석 파이썬 종합반

2주차
3주차