[8일차] python&sql

Classes More Than 5 Students

다음 테이블에서 최소 5명 이상의 학생이 듣는 과목을 찾으시오.

 

 

Courses

 

python

 

result = Courses.groupby('class').count().reset_index()
print(result[result['student']>=5][['class']].reset_index(drop=True))

• 과목별로 group by 해서 학생의 수가 5명 이상인 과목을 추출
  
  

SQL

 

select class
from Courses
group by class
having count(*) > 5

 

• 서브쿼리에서 과목별로 group by를 하고, having 절을 사용해 5명이상인 그룹만 추출

---

Customer Placing the Largest Number of Orders

다음 테이블에서 주문 횟수가 가장 많은 고객 번호를 출력하시오.

 

 

Orders

 

python

 

print(orders.groupby('customer_number')[['order_number']].count().nlargest(1, 'order_number').reset_index()[['customer_number']])

 

• 고객 번호 별로 주문 횟수를 count하고, nlargest 함수를 사용해 가장 주문 횟수가 많은 고객 번호 추출

SQL

 

select customer_number
from orders
group by customer_number
order by count(order_number) desc
limit 1

 

• 고객 번호 별로 주문 횟수를 내림차순 정렬하고, limit를 사용해 가장 주문 횟수가 많은 고객 추출

---

Group Sold Products By The Date

다음 테이블에서 날짜별로 판매되는 제품의 수와 이름을 찾으시오. (단, 판매된 제품 이름은 사전순으로 정렬되어야 하며, 결과 테이블은 주문 날짜 순서대로 정렬)

 

 

Activities

 

python

 

Activities.groupby('sell_date')['product'].agg([
        ('num_sold', 'nunique'),
        ('products', lambda x: ', '.join(sorted(x.unique())))
    ]).reset_index()

 

• agg() : 여러 집계 연산을 동시에 수행하는 함수
• ('num_sold', 'nunique') : product의 고유한 값의 수를 계산하고, 열 이름을 num_sold로 지정
• ('products', lambda x: ', '.join(sorted(x.unique()) : unique로 product의 고유한 값을 얻고, 정렬 후 join으로 정렬된 고유값들을 쉼표와 공백으로 구분된 문자열로 연결
  
  

SQL

 

# MySQL

select 
    date(sell_date) as sell_date, 
    count(distinct product) as num_sold, 
    group_concat(distinct product order by product asc separator ',') as products
from Activities
group by sell_date
order by sell_date asc;

#SQLite

with OrderedActivities as (
    select sell_date, product
    from Activities
    order by sell_date, product
)

SELECT 
    date(sell_date) as sell_date, 
    count(distinct product) as num_products, 
    group_concat(distinct product) as products
from OrderedActivities
group by date(sell_date);

 

• group_concat() : 그룹(sell_date)별로 판매된 고유한 product들의 목록을 쉼표로 구분된 문자열로 반환한다. 이 때, order by로 문자열 오름차순으로 정렬한 결과를 출력한다.
  
  
• SQLite는 MySQL과 달리 group_concat 안에서 order by를 사용할 수 없기 때문에, CTE로 테이블을 정렬한 후 동일한 결과를 출력한다.