DAY 5.

 LV 2

1. 부모의 형질 모두 가지는 대장균 찾기

 

문제 부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 ID에 대해 오름차순 정렬

select child.id,
       child.genotype,
       parent.genotype as parent_genotype
from ecoli_data child join ecoli_data parent on child.parent_id = parent.id
where (child.genotype & parent.genotype) = parent.genotype
order by child.id asc

 

 

오답노트

★ self join을 이해하자

1. 활용 : 동일 테이블 내에서 행 간의 관계를 분석할 때 사용

2. 주요 사용 사례

-- 1) 계층적 데이터 처리 조직도(상사-부하 관계), 가계도(부모-자식 관계) 같은 트리 구조 분석

-- 2) 동일 테이블 내 데이터 비교 특정 조건을 만족하는 행 간의 관계 탐색 (EX) 같은 지역에 위치한 경쟁업체 찾기

-- 3) 시계역 데이터 분석 과거 vs 현재 데이터 비교 (EX) 작년 대비 매출 변화

3. 쓰는 방법

-- 1) 테이블 별칭(alias) 사용 동일 테이블을 논리적으로 두 개의 테이블로 취급

        from ecoli_data child join ecoli_data parent

-- 2) 조인 조건 설정 

         on child.parent_id = parent.id

-- 3) 결과 필터링

         where (child.genotype & parent.genotype) = parent.genotype ★ 비트 연산 적용

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

문제 분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬.

-- 서브쿼리
select year(differentiation_date) as year,
       (max_size - ed.size_of_colony) as year_dev,
       ed.id
from ecoli_data ed inner join 
(
select year(differentiation_date) as year, 
       max(size_of_colony) as max_size
from ecoli_data
group by year(differentiation_date)
) a
on year(ed.differentiation_date) = a.year
order by year asc, year_dev asc

-- max(컬럼) over ()
select year(differentiation_date) as year,
       (max(size_of_colony) over (partition by year(differentiation_date)) - size_of_colony) as year_dev,
       id
from ecoli_data
order by year asc, year_dev asc

 

오답노트

1. inner join 과 서브쿼리

- 1) 서브쿼리를 통해 max_table 생성

         year(differentitation_date)로 연도 추출 후 max(size_of_colony) 계산, 연도별 최대 크기 테이블 생성

         중요) group by 해줘야 함

- 2) inner join

         원본 테이블 ecoli_data ed 랑 1)을 연도 기준으로 조인 

         on year(ed.differentiation_date) = max_table.year

- 3) 문제에서 요구한 편차 계산

         (max_size - ed.size_of_colony) 

- 4) 결과 정렬

 

2. max(컬럼) over (partition by) 로 연도별 최대값 계산

- 1) 기본 구문

MAX(컬럼) OVER (
    [PARTITION BY 그룹_컬럼]
    [ORDER BY 정렬_컬럼]
    [ROWS/RANGE 프레임_범위]
)

절	설명
partition by	그룹을 나눌 기준 (생략 시 전체 데이터를 하나의 그룹으로 처리)
order by	그룹 내 정렬 기준 (프레임 범위와 함께 사용 시 중요)
rows/range	계산 범위 지정 (예. rows between 1 preceeding and current row)

 

- 2) 활용 : 그룹 내 최대값 표시(예시 문제) // 누적 최대값 표시(아래 예시)

-- 현재 행까지의 최대 크기 누적
SELECT 
    ID,
    SIZE_OF_COLONY,
    MAX(SIZE_OF_COLONY) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_MAX
FROM ECOLI_DATA;

 

- 3) 문제풀이

- 편차: ① 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기

① max(size_of_colony) over (partition by year(differentiation_date)

② size_of_colony

따라서, 편차: (max(size_of_colony) over (partition by year(differentiation_date) - size_of_colony) as year_dev