CodeKata

SQL

프로그래머스: 대장균의 크기에 따라 분류하기 2

• 작성한 쿼리

with ecoli_percent as (
  select
    id
    , percent_rank() over (order by size_of_colony desc) as p_rank
  from
    ecoli_data
)
select
  id
  , case
    when p_rank <= 0.25 then 'CRITICAL'
    when p_rank <= 0.50 then 'HIGH'
    when p_rank <= 0.75 then 'MEDIUM'
    else 'LOW'
  end as colony_name
from
  ecoli_percent
order by
  id
;

참고할 만한 다른 풀이

SELECT
    id,
    CASE
        WHEN ntile_rank = 1 THEN 'CRITICAL'
        WHEN ntile_rank = 2 THEN 'HIGH'
        WHEN ntile_rank = 3 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS colony_name
FROM (
    SELECT 
        id,
        NTILE(4) OVER (ORDER BY size_of_colony DESC) AS ntile_rank
    FROM 
        ecoli_data
) ranked_data
ORDER BY
    id

SELECT E1.ID,
    CASE
        WHEN (NUM/(SELECT COUNT(*) FROM ECOLI_DATA))*100 <= 25
        THEN 'CRITICAL'
        WHEN (NUM/(SELECT COUNT(*) FROM ECOLI_DATA))*100 <= 50
        THEN 'HIGH'
        WHEN (NUM/(SELECT COUNT(*) FROM ECOLI_DATA))*100 <= 75
        THEN 'MEDIUM'
        ELSE 'LOW'
    END AS 'COLONY_NAME'
FROM ECOLI_DATA E1
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS NUM,
      ID, SIZE_OF_COLONY
      FROM ECOLI_DATA) AS E2
ON E1.ID = E2.ID
ORDER BY E1.ID;

프로그래머스: 특정 세대의 대장균 찾기

• 작성한 쿼리

select
  e1.id
from
  ecoli_data e1
  left join ecoli_data e2
  on e1.parent_id = e2.id
  left join ecoli_data e3
  on e2.parent_id = e3.id
where
  e3.id is not null
  and e3.parent_id is null
order by
  e1.id
;

참고할 만한 다른 풀이

WITH RECURSIVE ECOLI_TREE AS (
    -- Non-Recursive
    SELECT 
        ID
        , PARENT_ID
        , 1 GENERATION
    FROM 
        ECOLI_DATA
    WHERE 
        PARENT_ID IS NULL

    UNION ALL

    -- Recursive
    SELECT 
        A.ID
        , A.PARENT_ID
        , B.GENERATION + 1
    FROM 
        ECOLI_DATA A
        INNER JOIN ECOLI_TREE B 
            ON A.PARENT_ID = B.ID
)

SELECT
    ID
FROM
    ECOLI_TREE
WHERE
    GENERATION = 3
ORDER BY 
    ID

SELECT ID
FROM
(
    SELECT ID, PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID 
                        IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))
) AS aa
ORDER BY 1;

프로그래머스: 멸종위기의 대장균 찾기

• 처음 작성한 쿼리

with ecoli_generation as (
  select
    e1.id
    , case
      when e1.parent_id is null then 1
      when e2.parent_id is null then 2
      when e3.parent_id is null then 3
      else 4
    end as 'GENERATION'
  from
    ecoli_data e1
    left join ecoli_data e2
    on e1.parent_id = e2.id
    left join ecoli_data e3
    on e2.parent_id = e3.id
  order by
    e1.id
)
, ecoli_parent as (
  select
    distinct parent_id
    , id
  from
    ecoli_data
  where
    parent_id is not null
)
select
  count(id) as 'COUNT'
  , generation
from
  ecoli_generation
where
  id not in (select distinct parent_id from ecoli_parent)
group by
  generation
order by
  generation
;

→ 테스트는 잘 되는데 제출하면 다 실패함

테스트에서는 4세대까지 있지만 채점에서 주어지는 테이블에서는 4세대를 넘어갈 수도 있습니다.

라는 조언을 보고 방법을 바꿔야 한다는 걸 알았음
→ 자기 자신을 계속 참조하게 만들어야 한다!
🡆 recursive cte

• 최종 작성한 쿼리

with recursive cte as (
  select
    parent.id as id
    , child.id as child_id
    , 1 as cnt
  from
    ecoli_data parent
    left join ecoli_data child
    on parent.id = child.parent_id
  where
    parent.parent_id is null

  union all
  
  select
    c.child_id as id
    , e.id as child_id
    , cnt+1 as cnt
  from
    cte c
    left join ecoli_data e
    on c.child_id = e.parent_id
  where
    c.child_id is not null
)
select
  count(distinct id) as 'COUNT'
  , cnt as 'GENERATION'
from
  cte
where
  child_id is null
group by
  cnt
;

참고할 만한 다른 풀이

WITH RECURSIVE CTE (GENERATION, ID) AS
(
    SELECT 1, ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT GENERATION+1, ECOLI_DATA.ID FROM CTE
    JOIN ECOLI_DATA
    ON ECOLI_DATA.PARENT_ID = CTE.ID
)

SELECT COUNT(ID) AS COUNT, GENERATION FROM CTE
WHERE ID NOT IN
(SELECT DISTINCT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)
GROUP BY GENERATION
ORDER BY GENERATION ASC;

Aliases->CTE: table name, GENERATION: col0 of table, ID: col1 of table
Boilerplate->with recursive CTE (..) as (select .. union all select .. from CTE ..)

2. WHERE 절에 서브쿼리 사용

-- 1
WITH RECURSIVE rc AS (
    SELECT id, parent_id, 1 AS gen
    FROM ecoli_data
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.parent_id, rc.gen + 1 AS gen
    FROM ecoli_data AS e
    JOIN rc ON rc.id = e.parent_id
) 
SELECT COUNT(id) AS COUNT , gen AS generation
FROM rc
WHERE id NOT IN ( # NOT IN 자식이 있는 id들이면 -> 자식이 없는 id들임
    SELECT parent_id # 자식이 있는 id들 선택
    FROM rc
    WHERE parent_id IS NOT NULL # 일단 parent_id null 아니면 자식이 있다는 뜻임
)
GROUP BY gen;

-- 2
/*계층 구조를 가지는 데이터를 처리하기 위한 재귀 CTE 문*/
WITH RECURSIVE CTE
AS
(
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT CHILD.ID, CHILD.PARENT_ID, CTE.DEPTH + 1
    FROM ECOLI_DATA CHILD
    INNER JOIN CTE
        ON CHILD.PARENT_ID = CTE.ID
)

/*생성된 CTE 에 대해, 문제의 조건에 맞는 데이터를 추출하는 쿼리 부분*/
SELECT COUNT(*) AS 'COUNT'
    , DEPTH AS GENERATION 
FROM CTE
WHERE ID NOT IN (SELECT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL) -- 자식이 없는 데이터 필터링
GROUP BY GENERATION
ORDER BY GENERATION

다른 부분은, 자식을 가지고 있지 않은 데이터를 찾는 부분인데, 이 때 유의하실 점은
WHERE ID NOT IN (SELECT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)
위 쿼리에서 NOT IN 문 안에 들어있는 SELECT PARENT_ID... 조건에, 반드시 NULL 값을 대상에서 제외해주어야 합니다.
그렇지 않으면, NULL 값의 존재 때문에 정상적인 NOT IN 처리가 되지 않습니다.

-- 방법 1)
WHERE id NOT IN (SELECT parent_id FROM gen WHERE parent_id IS NOT NULL)
-- 방법 2)
WHERE id NOT IN (SELECT parent_id FROM gen) IS NOT FALSE
-- 방법 3)
WHERE NOT EXISTS (SELECT * FROM gen B WHERE gen.id = B.parent_id)
not in, not exists, null

WITH RECURSIVE rc AS (
    SELECT id, parent_id, 1 AS gen
    FROM ecoli_data
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.parent_id, rc.gen + 1 AS gen
    FROM ecoli_data AS e
    JOIN rc ON rc.id = e.parent_id
) 
SELECT COUNT(rc1.id) AS count, rc1.gen AS generation
FROM rc rc1
LEFT JOIN rc rc2 ON rc2.parent_id = rc1.id
WHERE rc2.parent_id IS NULL
GROUP BY rc1.gen
ORDER BY rc1.gen;

4. exists

WITH RECURSIVE CTE AS (
    SELECT
        ID, 1 AS GENERATION
    FROM
        ECOLI_DATA
    WHERE
        PARENT_ID IS NULL
    UNION ALL
    SELECT
        A.ID, GENERATION + 1 AS GENERATION
    FROM
        ECOLI_DATA A LEFT JOIN CTE B ON A.PARENT_ID = B.ID
    WHERE
        A.PARENT_ID = B.ID
)
SELECT COUNT(*) COUNT, GENERATION FROM CTE AS A
WHERE NOT EXISTS(SELECT 1 FROM ECOLI_DATA WHERE PARENT_ID = A.ID)
GROUP BY GENERATION

WITH RECURSIVE ECOLI_DATA_GENERATION AS ( -- 세대 추가 
    SELECT ID, PARENT_ID, 1 GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL 
    
    UNION ALL
    
    SELECT D.ID, D.PARENT_ID, GENERATION + 1
    FROM ECOLI_DATA D JOIN ECOLI_DATA_GENERATION G 
    ON G.ID = D.PARENT_ID 
),
ECOLI_DATA_GENERATION2 AS ( -- 자식 개수 추가 
    SELECT ID, PARENT_ID, GENERATION, (SELECT COUNT(*)
                                       FROM ECOLI_DATA_GENERATION  D
                                       WHERE G.ID = D.PARENT_ID) CHILD_COUNT
    FROM ECOLI_DATA_GENERATION G
)
SELECT COUNT(*) COUNT, GENERATION 
FROM ECOLI_DATA_GENERATION2
WHERE CHILD_COUNT = 0
GROUP BY GENERATION 
ORDER BY 2

최종 프로젝트

• 리스트 형태의 문자열, 딕셔너리 형태의 문자열, 리스트를 포함한 딕셔너리 형태의 문자열 처리
• EDA

고민 & 처리해야 할 내용

• serverName에서 asia만 써야 할까?
  • 다른 서버도 섞여 있는 상태임
• moveSpeed와 outOfcombatMoveSpeed가 다른 경우가 있나 찾아보기

전처리 관련

쪼개야 하는 컬럼

1. 리스트 형태의 문자열을 가진 컬럼

   traitFirstSub
   traitSecondSub
   airSupplyOpenCount
   foodCraftCount
   beverageCraftCount
   totalVFCredits
   usedVFCredits
   itemTransferredConsole
   itemTransferredDrone
   collectItemForLog

2. 딕셔너리 형태의 문자열을 가진 컬럼

   masteryLevel
   equipment
   skillLevelInfo
   skillOrderInfo
   eventMissionResult
   killMonsters
   killDetails
   deathDetails
   creditSource
   boughtInfusion
   equipFirstItemForLog
   activeInstallation
   hackAttemptLog
   equipmentGrade

전처리 시 삭제해도 될 것 같은 컬럼

1. 코발트 전용이고 실제로 값이 없음 → 삭제

   totalTurbineTakeOver
   usedNormalHealPack
   usedReinforcedHealPack
   usedNormalShieldPack
   usedReinforceShieldPack
   scoredPoint
   boughtInfusion
   scoredPoint
   killTurretGainVFCredit
   itemShredderGainVFCredit

2. Deprecated → 삭제

   gainedNormalMmrKFactor
   battleZone2AreaCode
   battleZone2BattleMark
   battleZone2ItemCode
   battleZone3AreaCode
   battleZone3BattleMark
   battleZone3ItemCode
   battleZonePlayerKill
   battleZoneDeaths
   battleZone2Winner
   battleZone3Winner
   battleZone2BattleMarkCount
   battleZone3BattleMarkCount
   infusionReRollUseVFCredit
   infusionTraitUseVFCredit
   infusionRelicUseVFCredit
   infusionStoreUseVFCredit

3. 중복 → 삭제

   IsLeavingBeforeCreditRevivalTerminate

※ 혹시 몰라서 개발자 디스코드에서 답변도 받음

보통 대소문자만 다르면 값은 동일합니다
해당 키는 동일 값으로 보입니다
이런 경우는 대문자 쪽이 추후에 Deprecated되기 때문에 소문자로 쓰시는 것을 권장드립니다.

회의 결과 삭제해도 되겠다 판단한 컬럼

killPlayerGainVFCredit
killChickenGainVFCredit
killBoarGainVFCredit
killWildDogGainVFCredit
killWolfGainVFCredit
killBearGainVFCredit
killOmegaGainVFCredit
killBatGainVFCredit
killWicklineGainVFCredit
killAlphaGainVFCredit
killItemBountyGainVFCredit

crGetAnimal
crGetMutant
crGetPhaseStart
crGetKill
crGetAssist
crGetTimeElapsed
crGetCreditBonus

→ 두 개 모두 creditSource_{key} 컬럼 내용이랑 겹침

전처리 고민 필요

1. 문서에는 코발트 전용이라는데 값이 들어가 있음 → keep

   killsPhaseOne
   killsPhaseTwo
   killsPhaseThree
   deathsPhaseOne
   deathsPhaseTwo
   deathsPhaseThree
   killGammaGainVFCredit

2. 문서에 설명이 있지만 현재까지 값이 0뿐인 컬럼

   skillLifeSteal → Stats이니까 일단 keep
   botAdded → 랭크 게임에는 봇 없다고는 하는데 혹시 모르니까 keep
   botRemain → 랭크 게임에는 봇 없다고는 하는데 혹시 모르니까 keep
   restrictedAreaAccelerated
   addSurveillanceCamera
   removeSurveillanceCamera
   battleZone1AreaCode
   battleZone1BattleMark
   battleZone1ItemCode
   battleZone1Winner
   battleZone1BattleMarkCount
   tacticalSkillUpgradeUseVFCredit
   teamBattleZoneDown
   useGuideRobot
   guideRobotRadial
   guideRobotFlagShip
   guideRobotSignature

3. 문서에 설명이 없고 현재까지 값이 0뿐인 컬럼

   bonusCoin → 크레딧 관련인 것 같으니까 일단 보류(keep)
   damageToPlayer_uniqueSkill
   damageFromPlayer_uniqueSkill
   damageToMonster_uniqueSkill
   damageToPlayer_Shield

4. 문서에 설명이 없고 현재까지 값이 null뿐인 컬럼 → 시즌 5도 전부 null이니까 삭제해도 될까?

   startingItems
   finalInfusion

🡆 확실한 것만 지우자('삭제해도 될 것 같은 컬럼', '회의 결과…'에 있는 것만 지우기)

공부한 내용 정리

• NOT IN 작동이 안 될 때

• API

• 가독성을 높이기 위해 긴 리스트 컴프리헨션을 여러 줄로 나누기

result = [
    expression
    for item in iterable
    if condition
]

→ 이렇게 작성해도 Python 인터프리터는 이를 하나의 리스트 컴프리헨션으로 인식하여 동일하게 처리함

리스트 컴프리헨션을 여러 줄로 나누어 작성해도 동일하게 작동
아래와 같은 이점 있음
1. 가독성 향상: 긴 표현식을 여러 줄로 나누어 각 부분을 쉽게 이해할 수 있습니다.
2. 유지보수 용이성: 코드를 수정하거나 디버깅할 때 더 쉽게 작업할 수 있습니다.
3. PEP 8 준수: Python의 공식 스타일 가이드인 PEP 8은 긴 줄을 여러 줄로 나누는 것을 권장합니다.

• 파이프라인 과제

회고

• 프로그래머스 SQL 문제 다 풀었다!
  • 내일부터는 leetcode나 hackerrank 문제 풀어야지