1661. Average Time of Process per Machine
▶ 문제 POINT : 시작 - 끝 시간 간의 소요시간 구하기
[문제] 각 머신의 프로세스 완료 소요 시간의 평균 구하기
There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
[조건]
- 프로세스 아이디별 소요시간 조회
- 머신 아이디별 프로세스 소요시간의 평균 값 구하기
- 출력 양식에 맞춰서 숫자 변환
▶ 문제 해결 & 피드백 POINT : 그룹핑 된 테이블의 select 절에는 그룹바이 컬럼 & 집계값으로만 조회
[풀이 과정]
- 서브쿼리 : 각 머신의 프로세스별 소요 시간 구하기 - group by & max & if 조건으로 시작-끝 지점으로 소요시간 구하기
- 소요 시간(diff컬럼) : start 지점 , end 지점일때 각각 조회후 차이값을 구해줌 ( abs 로 절대값 씌워줌)
- 아우터쿼리 : 소요시간의 평균값 구한 후 소수점 3째까지 반올림 - avg & round
WITH diff_t AS (
SELECT machine_id
,process_id
,abs(max(if(activity_type='start',timestamp,0))-max(if(activity_type='end',timestamp,0))) AS diff
FROM activity
GROUP BY machine_id,process_id
)
SELECT machine_id
,round(avg(diff),3) AS processing_time
FROM diff_t
GROUP BY 1
특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
▶ 문제 POINT :
[문제]
테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
[조건]
- CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 타입 '세단' OR 'SUV' 필터링
- CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 기록이 없는 car_id 조회
- CAR_RENTAL_COMPANY_DISCOUNT_PLAN테이블에서 대여기간 '30일이상' 필터링
- 할인 금액 적용한 대여금액 계산 50만원이상 200만원 미만 필터링
- 정렬 조건 만족
▶ 문제 해결 & 피드백 POINT : 대여 가능 = 대여기록 테이블에 대여기록이 없는 not in 으로 where 절 서브쿼리 필터링
[풀이 과정]
- 조인 : 출력 컬럼 `fee` 에 해당하는 연산에 필요한 컬럼 조인
- fee: daily_fee *30*(1-dicount_rate*0.01) : `CAR_RENTAL_COMPANY_CAR & CAR_RENTAL_COMPANY_DISCOUNT_PLAN` inner join
- on 절로 조인 연결시 선 필터링 : 30일 이상, 자동차 타입 세단/suv
- where 절 서브쿼리 : 히스토리 테이블에서 11월 한달간 대여 기록이 없는 자동차만 필터링
- 2022-11-01~2022-11-30 기간 동안 대여 시작/끝 지점이 포함되어있는 경우 제외 `not in`
- 시작시점이 11-30일 보다 작거나 같고, 반납시점이 11-01보다 크거나 작을때 제외
- `ex` 시작 시점이 11-30보다 작은 11-28일 이라면 11-29~11-30 2일간 대여 기록이 있을수 있음. 끝 시점이 11/1일 보다 큰 11-28일 이라면 11-1~11-28 28일간 대여 기록이 있을 수 있음.
- 아우터쿼리 : 출력 컬럼 그룹바이 후 대여금액 조건 필터링 & 정렬 조건 만족
- fee >=50만원, fee <200만원
SELECT c.CAR_ID,c.CAR_TYPE
,ROUND(c.daily_fee * 30 * (1-discount_rate*0.01),0) as FEE
FROM CAR_RENTAL_COMPANY_CAR c
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type=d.car_type and d.duration_type='30일 이상' AND c.car_type IN ('세단','SUV')
WHERE car_id NOT IN (SELECT car_id
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE start_date >='2022-11-30' or end_date >='2022-11-01')
GROUP BY 1,2,3
HAVING FEE>=500000 AND FEE<2000000
ORDER BY FEE DESC,CAR_TYPE,CAR_ID DESC