20260116 TIL

Today I Learn

: SQL 코드카타, 데이터 전처리/시각화 세션

---

[  SQL 코드카타 ]

 

- 문제 1 Leetcode (#1193) Monthly Transactions I

1. 문제 링크: https://leetcode.com/problems/monthly-transactions-i/

2. 정답 코드:

select
    date_format(trans_date,'%Y-%m') as month,
    country,
    count(*) as trans_count,
    count(case when state = 'approved' then 1 end) as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount end) as approved_total_amount
from transactions
group by date_format(trans_date,'%Y-%m'), country

 

3. 오류 상황: 'approved' 행이 하나도 없다면 approved_total_amount에서 sum(NULL)이므로 NULL이 출력됨

4. 해결 방안: case when에서 else 0이란 조건도 추가함

select 
    date_format(trans_date,'%Y-%m') as month, 
    country,
    count(*) as trans_count,
    ifnull(count(case when state = 'approved' then 1 end),0) as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from transactions 
group by date_format(trans_date,'%Y-%m'), country

• 사실, count는 ifnull 할 필요가 없음!
  • count의 특성
    • count(컬럼/조건) 함수는 집계할 대상이 없으면 기본적으로 0을 반환함
    • sum은 대상이 없으면 NULL은 반환하지만,
    • count는 존재 개수를 세는 것이기 때문!
• count(case when ... then 1 end) 보다는 sum(state='approved') 로 수정하는 것이 더 간결함!

select 
    date_format(trans_date,'%Y-%m') as month, 
    country,
    count(*) as trans_count,
    sum(state = 'approved') as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from transactions 
group by date_format(trans_date,'%Y-%m'), country

 

- 문제 2 Leetcode (#2356) Number of Unique Subjects Taught by Each Teacher

1. 문제 링크: https://leetcode.com/problems/number-of-unique-subjects-taught-by-each-teacher/description/

2. 정답 코드:

select teacher_id, count(distinct subject_id) as cnt
from teacher
group by teacher_id

 

• 선생님별로 가장 많은 과목을 가르치는 학과(dept_id) 하나만 뽑아서 보고싶다면?

with selected as (
select teacher_id, dept_id, rank() over (partition by teacher_id order by count(*) desc) as cnt
from teacher
group by teacher_id, dept_id
) 
select teacher_id, dept_id
from selected 
where cnt = 1

• 선생님이 학과별로 가르치는 과목의 이름(subject_id)들을 한 줄로 나열해서 보고싶다면?
  • group_concat()함수 활용!
    • seperator: 기본 구분자는 ',' 이지만 줄바꿈이나 다른 기호로 바꿀 수 있음

select 
    teacher_id, 
    dept_id, 
    group_concat(distinct subject_id order by subject_id seperator ', ') as subjects
from teacher
group by teacher_id, dept_id

 

- 문제 3 Leetcode (#1141) User Activity for the Past 30 Days I

1. 문제 링크: https://leetcode.com/problems/user-activity-for-the-past-30-days-i/description/

2. 정답 코드:

select 
    activity_date as day,
    count(distinct user_id) as active_users
from activity
where datediff('2019-07-27',activity_date)+1 <= 30
group by activity_date

 

3. 오류 상황: 한쪽 범위로만 조건을 달아주면 datediff 값이 음수값이 나오는 경우도 조회를 하게 됨 

4. 해결 방안: 양쪽이 닫힌 범위로 작성해줘야 함

select 
    activity_date as day,
    count(distinct user_id) as active_users
from activity
where activity_date > subdate('2019-07-27',30) and activity_date <= '2019-07-27'
group by activity_date

• subdate(date,interval) : date로부터 interval만큼 뺀 날짜 리턴 (2019-07-27일을 포함해서 30일이므로 등호만 사용)
• 2019-07-27일 이전 날짜만 포함해야하므로 뒷 조건 추가

select 
    activity_date as day,
    count(distinct user_id) as active_users
from activity
where datediff('2019-07-27', activity_date) + 1 <= 30 and activity_date <= '2019-07-27'
group by activity_date

• datediff(이후날짜, 이전날짜): 이후날짜에서 이전날짜 뺀 날짜 차이 (2019-07-27일 포함해야하므로 +1)

select 
    activity_date as day,
    count(distinct user_id) as active_users
from activity
where activity_date between date_sub('2019-07-27', interval 29 day) and '2019-07-27'
group by activity_date

• 제일 정확한 방법은 between으로 양쪽을 딱 막아주는 것.
  • date_sub: subdate 함수와 같은 기능을 수행함
    • 29day 전인 날짜 포함해서 그 날짜부터 2019-07-27까지 포함하는 범위 지정

 

---

[데이터 전처리 / 시각화 1-1]

- Series와 DataFrame 기본 구조 + dtype 이해

- 문제 있는 데이터를 분석 가능하게 고치기 (전처리 핵심)

- 데이터 불러오기 및 저장 (CSV, Excel, JSON)

https://myun0506.tistory.com/66

dtype, 결측치, CSV/Excel/JSON 다루기