[SQL] 프로그래머스 대장균 세트

특정 형질을 가지는 대장균 찾기 <- 문제로 이동

SELECT COUNT(*) AS COUNT FROM ECOLI_DATA
WHERE (GENOTYPE & 2)=0 AND ((GENOTYPE & 1 )=1 OR (GENOTYPE & 4)=4);

 

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

SELECT E.ID, E.GENOTYPE, P.GENOTYPE AS PARENT_GENOTYPE 
FROM ECOLI_DATA E INNER JOIN ECOLI_DATA P ON E.PARENT_ID=P.ID
WHERE P.GENOTYPE & E.GENOTYPE = P.GENOTYPE
ORDER BY ID ASC;

 

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

WITH MAX_COL AS (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA GROUP BY YEAR(DIFFERENTIATION_DATE)
)

SELECT YEAR(E.DIFFERENTIATION_DATE) AS YEAR, MAX_COL.MAX_SIZE-E.SIZE_OF_COLONY AS YEAR_DEV, E.ID 
FROM ECOLI_DATA E LEFT JOIN MAX_COL ON YEAR(DIFFERENTIATION_DATE) = MAX_COL.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

분기별 분화된 대장균의 개체 수 구하기

SELECT Q AS QUARTER, COUNT(ID) AS ECOLI_COUNT
FROM (SELECT ID, (CASE WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 AND 3 THEN '1Q'
                     WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 AND 6 THEN '2Q'
                     WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 AND 9 THEN '3Q'
                     WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 10 AND 12 THEN '4Q' END) AS Q
    FROM ECOLI_DATA) A
GROUP BY QUARTER 
ORDER BY QUARTER ASC;

 

 

대장균 크기에 따라 분류하기 1

SELECT ID, IF(SIZE_OF_COLONY <=100, 'LOW', IF(SIZE_OF_COLONY <=1000, 'MEDIUM', 'HIGH')) AS SIZE
FROM ECOLI_DATA 
ORDER BY ID ASC;

 

대장균 크기에 따라 분류하기 2

WITH SORT AS(
    SELECT ID, NTILE(4) OVER(ORDER BY SIZE_OF_COLONY) AS RANKING FROM ECOLI_DATA
)
SELECT E.ID,
(CASE WHEN S.RANKING = 1 THEN 'LOW' 
              WHEN S.RANKING = 2 THEN 'MEDIUM'
              WHEN S.RANKING = 3 THEN 'HIGH'
              WHEN S.RANKING = 4 THEN 'CRITICAL'
              END) AS COLONY_NAME
FROM SORT S LEFT JOIN ECOLI_DATA E ON S.ID=E.ID
ORDER BY E.ID ASC;

 

대장균들의 자식의 수 구하기

SELECT P.ID AS ID, COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA P LEFT JOIN ECOLI_DATA C ON P.ID=C.PARENT_ID
GROUP BY ID
ORDER BY ID ASC;

 

 

특정 세대의 대장균 찾기

WITH RECURSIVE TREE AS(
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL 
    
    SELECT E.ID, E.PARENT_ID, T.DEPTH+1
    FROM ECOLI_DATA E JOIN TREE T ON E.PARENT_ID=T.ID
    
)

SELECT ID
FROM TREE
WHERE DEPTH = 3
ORDER BY ID ASC;

 

 

멸종위기의 대장균 찾기

WITH RECURSIVE TREE AS (
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL 
    
    SELECT E.ID, E.PARENT_ID, T.DEPTH + 1
    FROM ECOLI_DATA E JOIN TREE T ON E.PARENT_ID=T.ID
)

SELECT COUNT(*) AS COUNT, DEPTH AS GENERATION
FROM ECOLI_DATA E RIGHT JOIN TREE T ON E.PARENT_ID=T.ID
WHERE E.ID IS NULL
GROUP BY DEPTH
ORDER BY DEPTH ASC;