[6일차] python&sql

The Number of Rich Customers

다음 테이블에서 총 구매금액이 500을 초과하는 금액의 청구서를 하나 이상 가진 고객의 수를 찾으시오.

 

Store

 

python

 

result_df = Store.groupby('customer_id').max('amount')
print(pd.DataFrame([result_df[result_df['amount']>500]['amount'].count()], columns=['rich_count']))

 

SQL

 

select count(*) as rich_count
from (select max(amount) 
from Store
group by customer_id
having amount > 500)

 

• having 절을 사용하여 group by로 그룹화된 결과에 조건을 적용하고, 바깥쪽 쿼리문에서 그룹화된 결과를 count

---

The Number of Rich Customers

다음 테이블에서 총 구매금액이 500을 초과하는 금액의 청구서를 하나 이상 가진 고객의 수를 찾으시오.

 

 

Delivery

 

python

 

round(len(Delivery[Delivery['customer_pref_delivery_date'] == Delivery['order_date']])/len(Delivery)*100,2)

 

• 전체 길이에서 date의 날짜가 동일한 길이를 나누고, round 함수로 소숫점 2자리에서 반올림
  
  

SQL

 

with immediate_orders as(
    select count(*) as immediate_count
    from Delivery
    where customer_pref_delivery_date = order_date
),
total_orders as (
    select count(*) as total_count
    from Delivery
)

select round((cast(immediate_count as float) / cast(total_count as float) * 100),2) as immediate_percentage
from immediate_orders
cross join total_orders

 

• CTE를 사용해서 immediate_orders와 전체 order의 길이를 count
• cast() : 데이터 타입을 변환하는 함수로, 정수 타입의 개수를 실수형으로 변환한다.  solution에서 소숫점이 나올 수 있는 연산을 수행하기 때문에, 타입을 실수로 바꿔주지 않으면 결과가 자동으로 정수로 반올림되거나 버림될 수 있다.

---

Count Salary Categories

다음 테이블에서 총 구매금액이 500을 초과하는 금액의 청구서를 하나 이상 가진 고객의 수를 찾으시오.

 

Accounts

 

python

 

print(pd.DataFrame({
    'category' : ['Low Salary', 'Average Salary', 'High Salary'],
    'accounts_count' : [
        accounts[accounts.income < 20000].shape[0],
        accounts[(accounts.income >= 20000)&(accounts.income < 50000)].shape[0],
        accounts[accounts.income > 50000].shape[0]
    ]
}))

 

• category column을 임의로 생성하고, 전체 dataframe에서 조건에 맞는 데이터를 가져와 행의 길이를 값으로 사용
  
  

SQL

 

select category, accounts_count
from(
	select "Low Salary" as category, sum(income < 20000) as accounts_count
	from Accounts
	union
	select "Average Salary" as category, sum(income between 20000 and 50000) as accounts_count
	from Accounts
	union
	select "High Salary" as category, sum(income >= 50000) as accounts_count
	from Accounts) as subquery

order by case 
when category = 'Low Salary' then 1 
when category = 'Average Salary' then 2
else 3
end

 

• subquery에서 조건에 맞게 category와 value를 구한다. 이 때, sum(income < 20000)이 20000보다 작은 income을 전부 더하라고 해석할 수도 있지만, 여기서는 income < 20000의 값은 boolean으로 return 된다. 즉, True or False로 값을 가지기 때문에, SQL문에서 sum(income < 20000)은 20000보다 작은 income 값을 가진 행의 개수를 구하라고 해석할 수 있다.
• order by sorting을 위해 결과값을 subquery로 만들고, low부터 high까지 case문을 사용해서 정렬