[10일차] python&sql

Students and Examinations

다음 3개의 테이블에서 개별 학생마다 과목별 시험 응시 횟수를 구하시오. (단, 결과 테이블은 student_id와 subject_name 순으로 정렬)

 

Students, Subjects, Examinations

 

python

 

all_value = pd.merge(Students, Subjects, how = 'cross')

results = pd.merge(all_value, Examinations, on = ['student_id', 'subject_name'], how = 'left', indicator=True)
results.groupby(['student_id','student_name','subject_name']).apply(lambda x : (x['_merge'] == 'both').sum()).reset_index(name = 'attended_count').sort_values(by=['student_id','subject_name'])

 

• 시험 응시 횟수가 0번인 학생도 추출하기 위해 Students와 Subjects 데이터 프레임을 cross join
• all_value와 Examinations를 결합, 이 때 indicator = True 옵션을 사용해 병합 결과를 나타내는 _merge 열을 추가
• 학번,  학생 이름, 과목 이름으로 groupby 후 apply 함수에서 각 그룹에서 양쪽 모두에 있는 행의 수를 계산하여 학생이 특정 과목의 시험에 참석한 횟수 출력
• reset_index(), sort_values()로 그룹화를 해제하고 결과를 정렬
  
  

SQL

 

with ct as(
    select
        S.student_id,
        S.student_name,
        Sub.subject_name
    from Students S
    cross join Subjects Sub
    )

select
    ct.student_id,
    ct.student_name,
    ct.subject_name,
    count(Exam.subject_name) as attended_exams
from ct
left join Examinations Exam
on ct.student_id = Exam.student_id and ct.subject_name = Exam.subject_name
group by ct.student_id, ct.student_name, ct.subject_name
order by ct.student_id, ct.subject_name

 

• CTE로 python code에서 all_value 역할을 하는 DataFrame 생성
• 메인 query에서 서브 query의 결과와 Examinations를 join한다. 서브 query에서 cross join을 사용했기 때문에, id로만 join을 할 경우 학생이 참석한 모든 과목의 시험 기록이 해당 학생의 모든 과목과 연결된다. 따라서, 과목 이름을 함께 사용해 실제로 학생이 참석한 시험만 count하도록 한다.

---

Managers with at Least 5 Direct Reports

다음 테이블에서 report를 5개 이상 받는 Managers의 이름을 찾으시오.

 

Employee

 

python

 

results = Employee.groupby('managerId').count().reset_index()
print(pd.merge(results, Employee, left_on = 'managerId', right_on='id', how='left', suffixes=('_left','_right'))[['name_right']].rename(columns={'name_right':'name'}))

 

SQL

 

# where절 subquery

select e.name
	from Employee e
	where e.id in (
	select managerId
	from Employee
	group by managerId
	having count(*) >= 5
    
# self join

select distinct m.name
	from Employee e
	join Employee m on e.managerId = m.id
	group by m.id, m.name
	having count(e.id) >= 5

 

• where절에 서브 query를 사용해서, managerID 그룹화된 결과에서 행이 5개 이상인 id만 추출
• 같은 테이블을 사용하기 때문에, self-join을 사용해서 직원의 managerID와 매니저의 id를 매칭시키고, group by와 having을 사용해 매니저 아래에 5명 이상의 직원이 있는 매니저만 선택

---

Sales Person

다음 테이블에서 "RED"라는 이름으로 회사와 관련된 주문이 없었던 모든 영업사원의 이름을 찾으시오.

 

SalesPerson, Company, Orders

 

python

 

# 최초 코드
left_df = pd.merge(Company, Orders, on = 'com_id')
result = pd.merge(SalesPerson, left_df, on = 'sales_id', how = 'left', suffixes = ('_sales', '_company'))
red_order = result[result['name_company']=='RED']['sales_id'].unique()
result[~result['sales_id'].isin(red_order)][['name_sales']]

# 최적화된 코드
red_orders = Orders[Orders['com_id'].isin(Company[Company['name'] == 'RED']['com_id'])]
red_sales_ids = red_orders['sales_id'].unique()
SalesPerson[~SalesPerson['sales_id'].isin(red_sales_ids)][['name']]

 

• 최초에 구성된 코드와 최적화된 코드를 비교해보면, 불필요한 merge를 최소화하고 필요한 열만 병합해서 메모리 사용량을 최소화할 수 있음
• ~(부정), isin() 함수를 사용해 RED 회사에서 주문을 한 판매원의 ID를 제외하고 이름을 추출
  
  

SQL

 

# not in

select name
from SalesPerson
where sales_id not in(
  select distinct Orders.sales_id
  from Orders
  join Company on Orders.com_id = Company.com_id
  where Company.name = "RED")
  
# not exists

select name
from SalesPerson sp
where not exists (
    select 1
    from Orders o
    join Company c on o.com_id = c.com_id
    where c.name = "RED" and o.sales_id = sp.sales_id

 

• not in() : 서브쿼리에서 반환된 목록과 일치하지 않는 값을 선택, NULL 값에 영향을 받음
• not exists() : 해당 조건을 만족하는 행이 존재하지 않는 경우 참을 반환, NULL 값에 영향을 받지 않음
• select 1 : 단순한 상수 값을 반환, 결과에 대한 정보를 가져오는 것이 아니라 어떤 조건이 참 혹은 거짓인지를 확인

---

오늘까지 10일간 LeetCode의 30 Days of Pandas를 python과 sql로 리뷰해보았다. 코드 구현에 있어서 효율적인 구성 및 적절한 함수를 선택하는 부분이 부족했다는 것을 확인했으니, 차후에도 계속 비슷한 문제를 리뷰하면서 부족한 부분을 채워보도록 하자.