하나둘셋넷

특정 세대의 대장균 찾기

출처 : 프로그래머스 https://school.programmers.co.kr/learn/courses/30/lessons/301650

데이터 준비

-- 데이터베이스 생성 --
create database if not exists programmers_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

-- 테이블 생성 --
CREATE TABLE IF NOT EXISTS ECOLI_DATA(
	ID int not null,
    PARENT_ID int,
    SIZE_OF_COLONY int not null,
    DIFFERENTIATION_DATE date not null,
    GENOTYPE int not null
);

-- 데이터 삽입 --
INSERT INTO ECOLI_DATA VALUES (1,NULL,10,'2019/01/01',5);
INSERT INTO ECOLI_DATA VALUES (2,NULL,2,'2019/01/01',3);
INSERT INTO ECOLI_DATA VALUES (3,1,100,'2020/01/01',4);
INSERT INTO ECOLI_DATA VALUES (4,2,16,'2020/01/01',4);
INSERT INTO ECOLI_DATA VALUES (5,2,17,'2020/01/01',6);
INSERT INTO ECOLI_DATA VALUES (6,4,101,'2021/01/01',22);
INSERT INTO ECOLI_DATA VALUES (7,3,101,'2022/01/01',23);
INSERT INTO ECOLI_DATA VALUES (8,6,1,'2022/01/01',27);

풀이

SELECT `origin`.ID
FROM ECOLI_DATA `origin`
WHERE `origin`.PARENT_ID IN -- 2세대 ID를 찾는다, 부모 ID가 1세대 ID인 데이터
(SELECT first.ID FROM -- 1세대 ID를 찾는다.
(
WITH parent_gen as -- 0세대 ID를 찾는다.
(SELECT ID FROM ECOLI_DATA
WHERE PARENT_ID IS NULL)
SELECT `first_gen`.`ID` 
FROM ECOLI_DATA `first_gen`
WHERE `first_gen`.PARENT_ID IN -- 부모 ID가 0세대 ID인 데이터는 1세대 데이터
	( SELECT * FROM parent_gen)  
) `first`
)
ORDER BY `origin`.ID ASC;

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

출처 : 프로그래머스 https://school.programmers.co.kr/learn/courses/30/lessons/299307

데이터 준비

-- 데이터베이스 생성 --
create database if not exists programmers_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

-- 테이블 생성 --
CREATE TABLE ECOLI_DATA(
	id int not null,
    parent_id int,
    size_of_colony int not null,
    differentiation_date date not null,
    genotype int not null
);

-- 데이터 삽입 --
insert into ECOLI_DATA VALUES ('1', NULL,'17','2019/01/01','5');
insert into ECOLI_DATA VALUES ('2', NULL,'150','2019/01/01','3');
insert into ECOLI_DATA VALUES ('3','1','4000','2020/01/01','4');

풀이

SELECT ED.id as `ID`,
	CASE WHEN size_of_colony <= 100 THEN 'LOW'
		WHEN size_of_colony > 100 and size_of_colony <= 1000 then 'MEDIUM'
                WHEN size_of_colony > 1000 then 'HIGH'
	ELSE 'ETC' END as `SIZE`
FROM ECOLI_DATA ED
ORDER BY ED.id asc;

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

출처 : 프로그래머스 https://school.programmers.co.kr/learn/courses/30/lessons/301649

데이터 준비

-- 데이터베이스 생성 --

create database if not exists programmers_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

-- 테이블 생성 --
create table ECOLI_DATA(
	id integer not null,
    parent_id int,
    size_of_colony int not null,
    differentiation_date date,
    genotype int
);

-- 데이터 삽입 --
INSERT INTO ECOLI_DATA values ('1',NULL,'10','2019/01/01','5');
INSERT INTO ECOLI_DATA values ('2',NULL,'2','2019/01/01','3');
INSERT INTO ECOLI_DATA values ('3','1','100','2020/01/01','4');
INSERT INTO ECOLI_DATA values ('4','2','16','2020/01/01','4');
INSERT INTO ECOLI_DATA values ('5','2','17','2020/01/01','6');
INSERT INTO ECOLI_DATA values ('6','4','101','2021/01/01','22');
INSERT INTO ECOLI_DATA values ('7','6','101','2022/01/01','23');
INSERT INTO ECOLI_DATA values ('8','6','1','2022/01/01','27');

문제풀이

SELECT Percent_ED.id as ID,
		CASE 
        		WHEN Percent_ED.size_rank <= 0.25 THEN 'CRITICAL'
            		WHEN (Percent_ED.size_rank > 0.25) and (Percent_ED.size_rank<=0.5) THEN 'HIGH'
            		WHEN (Percent_ED.size_rank > 0.5) and (Percent_ED.size_rank<=0.75) THEN 'MEDIUM'
		ELSE 'LOW' END AS COLONY_NAME
FROM 
(SELECT id,
		Percent_RANK() over (order by size_of_colony desc) as 'size_rank'
FROM ECOLI_DATA) as `Percent_ED`
order by Percent_ED.id;