알고리즘/SQL

연도별 대장균 크기의 편차 구하기

베리영young 2024. 9. 19. 18:31

우헷

 

처음에 풀었을 때 코드 (복잡~)

with big as (
  select max(SIZE_OF_COLONY) m, YEAR(DIFFERENTIATION_DATE) y
  from ECOLI_DATA
  group by YEAR(DIFFERENTIATION_DATE)
)


-- 코드를 작성해주세요
select YEAR(DIFFERENTIATION_DATE) YEAR,
   (big.m - SIZE_OF_COLONY) YEAR_DEV, 
   ID
from ECOLI_DATA inner join big
on ECOLI_DATA.YEAR(DIFFERENTIATION_DATE) = y
order by YEAR , YEAR_DEV

 

 

두 번째 풀었을 때도 비슷;;

with A as (
  select max(SIZE_OF_COLONY) big, year(DIFFERENTIATION_DATE) yearbig
    from ECOLI_DATA 
    group by year(DIFFERENTIATION_DATE)
)
-- 코드를 작성해주세요
select year(DIFFERENTIATION_DATE) YEAR, (big - SIZE_OF_COLONY) YEAR_DEV, ID
from ECOLI_DATA inner join A
on year(DIFFERENTIATION_DATE) = yearbig
order by YEAR, YEAR_DEV

 

 

비슷한 게 아니라 똑같잖아;