[5일차] python&sql

Rank Scores

다음 테이블에서 점수의 순위를 찾으시오. 단, 점수는 가장 높은 것부터 가장 낮은 것까지 순위가 매겨져야 하며, 두 점수 사이에 동점이 있으면 두 점수 모두 같은 순위를 가져야 한다. 또한 동점 다음 순위 범호는 연속되는 정수 값이어야하고, 결과를 내림차순으로 점수순으로 출력하라.

 

Scores

 

Python

 

Scores['rank'] = Scores['score'].rank(ascending=False, method='dense')
print(Scores[['score','rank']].sort_values('rank').reset_index(drop=True))

 

• rank 함수를 사용해서 score의 순위를 찾는다. 이 때, ascending = False를 사용하면 내림차순으로 출력할 수 있다. method parameter로 동일한 값의 순위를 처리할 수 있는데, 5가지 방법을 정리해보았다.
  
  
  • average(default) : 동일한 값에 평균 순위를 할당
  • min : 동일한 값에 모두 최소 순위를 할당
  • max : 동일한 값에 모두 최대 순위를 할당
  • first : 데이터에 나타나는 순서대로 순위를 할당(동일한 값에도 적용)
  • dense : min과 유사하지만 동일한 값이 있어도 순위 사이에 공백을 두지 않음
    
    
• 동점 다음 순위 범호는 연속되는 정수 값이기 때문에, 여기서는 dense를 사용한다.
  
  

SQL

 

select score,
dense_rank() over(
    order by score desc
) as rank
from Scores

 

• dense_rank() : 주어진 값에 따라 결과 세트의 각 행에 순위를 할당한다. 이 때, 동일한 값에 동일한 순위를 할당하지만 순위 사이에 간격을 두지 않는다.
• rank() : 결과 세트의 각 행에 순위를 할당한다. 기본적인 구문은 다음과 같으며, 필요에 따라 선택적으로 사용한다.
  • partition by : 행을 그룹화하고, 각 그룹 내에서 순위를 측정
  • order by : 행에서 순위를 받을 순서를 지정

---

Delete Duplicate Emails

다음 테이블에서 ID가 가장 작은 하나의 고유한 전자 메일만 보관하고, 모든 복제 메일을 삭제하시오. (단, SQL 사용자의 경우 Select 문이 아닌 delete 문을 사용해야 하며, pandas 사용자의 경우 Person table을 수정해야 한다.)

 

Person

 

python

 

print(Person.drop_duplicates(subset ='email', keep = 'first', inplace = False))

 

• drop_duplicates() : 중복된 행(row)을 제거할 때 사용
  • subset : 중복 여부를 판단할 열(column)을 지정
  • keep : 중복된 행들 중 어떤 행을 유지할지를 지정, first는 첫 번째 행을 유지하고, last는 마지막 행을 유지하고 나머지는 전부 삭제
  • inplace : 해당 연산을 원본 DataFrame에 반영할지 여부를 결정
    
    

SQL

 

# MySQL
delete p1 
from person p1, person p2 
where p1.email=p2.email and p1.id>p2.id;

# SQLite
delete from person
where id not in(
	select min(id)
    from person
    group by email
)

 

• 두 가지 쿼리 모두 각 이메일 주소에 대해서 Id 값이 가장 작은 레코드만을 남기고 나머지는 삭제
• pandasql은 데이터 조회와 관련된 SQL 문장 실행에 중점을 뒀기 때문에, 데이터 변경 작업(delete, insert, update)을 수행하기에는 부적합함
• 따라서, MySQL query를 함께 solution으로 제시

---

Rearrange Products Table

다음 테이블에서 각 행이 product_id, store, price가 되도록 테이블을 재배치 하시오. (단, 제품을 사용할 수 없는 경우에는 결과 테이블에 해당 id와 store의 행을 제외)

 

Product

 

Python

 

pd.melt(Products, id_vars = ['product_id'],
        value_vars = [col for col in Products.columns if col != 'product_id'],
        var_name = 'store', value_name = 'price').dropna(subset=['price']).sort_values(by='product_id').reset_index(drop=True)

• pd.melt() : DataFrame의 형태를 변경하여, 여러개의 열을 하나의 열로 펼치는 동작을 수행
  • id_vars : 그대로 유지할 열을 지정
  • value_vars : melt 작업을 수행할 열을 지정, 여기서는 'product_id'를 제외한 열들을 선택
  • var_name, value_name : 생성되는 열과 각각의 값에 대한 열의 이름을 지정
  • dropna(subset = ['price']) : 문제의 조건대로 price 열에서 NULL을 제거

 

설명만으로는 이해가 부족할 수 있기 때문에, 간단한 예제를 함께 만들어 보았다. A,B열을 고정시키고, C, D열을 melting 시키면 열의 이름과 value가 각각 하나의 열로 펼쳐진 것을 확인할 수 있다.

 

Example

SQL

 

with StoreNames as (
    select 'store1' AS store_name
    union
    select 'store2'
    union
    select 'store3'
)

select
    p.Product_id,
    s.store_name as store,
    case 
        when s.store_name = 'store1' then p.store1
        when s.store_name = 'store2' then p.store2
        when s.store_name = 'store3' then p.store3
    end as price
from
    Products p
cross join
    StoreNames s
where
    case
        when s.store_name = 'store1' then p.store1
        when s.store_name = 'store2' then p.store2
        when s.store_name = 'store3' then p.store3
    end is not null;

 

쿼리에서는 CTE(Common Table Expression)을 사용해서 가상의 'StoreNames' 테이블을 생성했다. 코드를 살펴보기 전에, CTE가 무엇인지부터 알아보자.

 

 

CTE(Common Table Expression)

 

CTE는 SQL에서 임시 결과 집합을 정의하는 기능으로, 만들어진 CTE를 이후의 select, insert, update, delete 문에서 참조할 수 있다. 즉, 복잡한 쿼리문을 좀 더 읽기 쉽게 만듦과 동시에 반복되는 서브쿼리의 중복을 제거하여 코드를 간결하게 만들 수 있다는 장점이 있다. 기본 구문은 다음과 같다.

 

WITH cte_name (column_name1, column_name2, ...)
AS (
    -- CTE의 본문, 여기에 서브쿼리가 들어간다.
)
-- 이후의 메인 쿼리. 여기에서 CTE를 참조한다.
SELECT ... FROM cte_name ...

 

즉, with 구문에서 정의한 CTE 이름을 이후의 메인 쿼리에서 참조해서 결과를 출력하는 것이다. CTE에 대해 살펴봤으니, 이제 코드를 확인해보자.

 

• cross join : cross join은 두 테이블의 모든 가능한 조합을 생성하는 조인 방식이다. 만약 A 테이블에 3개의 행이 있고, B 테이블에 4개의 행이 있다면, cross join의 결과는 12개(3*4)의 행이 출력된다.
• case문 : SQL에서 조건문을 수행하기 위해 사용된다. 다른 프로그래밍 언어의 if-else 문과 유사한 역할을 수행한다.
  
  
• solution에서는 case문을 사용하여 store_name의 값에 따라 Products 테이블의 적절한 store 열의 값을 선택한다. 예를 들어, store_name = 'store1'이면, 'store1'의 값을 선택하게 된다.
• where 절에서는 store_name의 값에 따라 선택된 값이 null이 아닌 행만을 결과로 추출한다.