240722 TIL group by vs partition by

SQL

 

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

# 2022년 9월에 대해 - start_date substr
# 대여기간이 30일 이상


SELECT  HISTORY_ID,
        CAR_ID,
        date_format(START_DATE,'%Y-%m-%d') START_DATE,
        date_format(END_DATE,'%Y-%m-%d') END_DATE,
        #DATEDIFF(end_date,start_date)+1,
        case when DATEDIFF(end_date,start_date)+1 >= 30 then '장기 대여'
        else '단기 대여'
        end as RENT_TYPE
from car_rental_company_rental_history
where start_date like ('2022-09%')
order by history_id desc

 

 

 

63.자동차 평균 대여 기간 구하기

 

 

나의 풀이 (오답)

-- 평균대여 기간 7일 이상
select *
from
(
SELECT  CAR_ID,
        round(avg(datediff(end_date,start_date)+1) over (partition by car_id),1) AVERAGE_DURATION
from car_rental_company_rental_history
group by 1
order by 2 desc, 1 desc
) a
where AVERAGE_DURATION > 7

 

튜터와의 질의 상담후 인지 해야할 내용

 

윈도우함수를 써주신 건 너무 좋은 발상이신데요~ 윈도우함수는 '각 행'에 적용되서 car_id별로 avg를 모두 갖게 되어 중복행이 많이 발생합니다. group by로 중복행을 없애려고 시도해주신건 너무 좋은데, 데이터가 유실되고있어요.(group by 전후 결과를 확인해보시면 알수있습니다!)  데이터를 유실하지 않고 group by로 바로 평균을 구하면 데이터를유실하지 않습니다 ㅎㅎ 대여기간도 '7일 이상'이기 때문에 >=로 고쳐주셔야 해요 ㅎㅎ

partition by로 윈도우함수를 쓸까, group by로 집계함수를 쓸까 평소에 잘 판단해야하는데요~
'그룹 안에서 매 행마다 결과'가 필요할 때는 윈도우함수.

예시
누적 합계: 각 행마다 누적된 합계를 계산할 때.
이동 평균: 각 행마다 특정 범위 내의 평균을 계산할 때.
'그룹끼리 집계한 하나의 결과'만 필요할 때는 group by와 집계함수.

이렇게 머릿속에 꼭 외워두셔야 나중에 회사에서 윈도우함수/그룹바이 구분해서 잘 쓰실거에요!//:)

 

 

'그룹 안에서 매 행마다 결과'가 필요할 때는 윈도우함수.
예시
누적 합계: 각 행마다 누적된 합계를 계산할 때.
이동 평균: 각 행마다 특정 범위 내의 평균을 계산할 때.
'그룹끼리 집계한 하나의 결과'만 필요할 때는 group by와 집계함수.

 

SELECT
    car_id,
    # start_date,
    # end_date,
    # datediff(end_date, start_date),
    round(avg(datediff(end_date, start_date)+1), 1) as AVERAGE_DURATION 
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by 1
having round(avg(datediff(end_date, start_date)+1), 1) >= 7
order by 2 DESC, 1 DESC

 

 

63.헤비 유저가 소유한 장소 -고민필요!!

select  p.id,
        p.name,
        p.host_id
from places p inner join 
(select host_id
from places
group by host_id
having count(id)>=2) b on p.host_id=b.host_id
order by 1