[2일차] python&sql

문제를 하루에 하나씩 풀기 보다는, 하루에 3문제씩 10일에 걸쳐서 solution을 review 해보는 것이 좋다고 판단했다. 이전에 설명했던 부분을 제외하고, 새롭게 알게되는 부분을 추가적으로 포스팅하며 진행하도록 하겠다.

 

---

Article Views 1

다음 테이블에서 자신의 기사 중 하나라도 본 작성자의 id를 출력하시오. (단, 결과는 id 순으로 오름차순으로 출력)

 

Views

Python

 

print(pd.DataFrame(Views[Views['author_id'] == Views['viewer_id']]['author_id']).drop_duplicates().rename(columns={'author_id':'id'}).sort_values('id').reset_index(drop=True))

 

• drop_duplicates() :  중복되는 행을 제거
• sort_values('id') : 'id' 열을 기준으로 값을 정렬
  
  

SQL

 

select distinct(author_id) as id
from Views
where author_id = viewer_id
order by id

 

• select distinct(author_id) as id: 'author_id' 열에서 중복 없이 값을 선택하며, 결과 출력에서는 'id'라는 이름으로 표시
• order by id: 선택된 결과를 'id' 열을 기준으로 정렬

---

Invalid Tweets

유효하지 않은 Tweet의 ID를 작성하시오.  (단, 트윗 내용에 사용된 문자 수가 15자를 초과하는 경우 유효하지 않다고 간주한다.)

 

Tweets

 

Python

 

Tweets[Tweets['content'].str.len()>15][['tweet_id']]

 

• Tweets['content'].str.len()>15: Tweets['content'].str.len()는 각 'content'의 문자열 길이를 계산하고, 15보다 길이가 큰 조건을 통해 길이가 15보다 큰 행만 선택
  
  

SQL

 

select tweet_id
from Tweets
where length(content) > 15

 

• where length(content) > 15: SQL의 length 함수를 사용하여 'content' 열의 문자열 길이를 계산하고, 그 길이가 15보다 큰 행만 선택하라는 조건

---

Calculate Special Bonus

다음 테이블에서 직원의 상여금을 계산하시오. (단, 직원의 아이디가 홀수이고 이름이 'M'자로 시작하지 않는 경우 직원의 상여금은 급여의 100%이고, 그렇지 않은 경우는 0원이다.)

 

 

Employees

 

Python

 

Employees.loc[(Employees['employee_id']%2 == 1)&(~Employees['name'].str.startswith('M')), 'bonus'] = Employees['salary']
Employees['bonus'] = Employees['bonus'].fillna(0)
print(Employees[['employee_id','bonus']])

 

• (Employees['employee_id']%2 == 1): employee_id가 홀수인 행만 선택
• (~Employees['name'].str.startswith('M')): 이름이 'M'으로 시작하지 않는 행만 선택한다. 맨앞의 '~'은 부정을 뜻하고, ~이 없으면 이름이 M으로 시작하는 행만 선택한다.
• .loc[조건, 'bonus']: 위의 두 조건을 만족하는 행에서 'bonus' 열의 값을 수정
• 'bonus' 열에서 NaN 값들을 0으로 채워서 보너스를 받지 않은 직원들에게 0을 할당
  
  

SQL

 

# mysql
select employee_id, 
if(employee_id % 2 != 0 and left(name,1) <> 'M', salary, 0 ) as bonus
from Employees
order by employee_id

# sqlite
SELECT employee_id, 
CASE WHEN employee_id % 2 != 0 AND SUBSTR(name, 1, 1) <> 'M' THEN salary ELSE 0 END AS bonus
FROM Employees
ORDER BY employee_id

 

• if(condition, value_if_true, value_if_false): 조건을 검사하고 참인 경우 첫 번째 값을 반환하고, 거짓인 경우 두 번째 값을 반환
• left(name,1) <> 'M': 'name'의 첫 문자가 'M'이 아닌 행을 선택
• order by employee_id: 결과를 'employee_id'로 정렬
  
  
• CASE WHEN: 조건을 검사하고, 해당 조건이 참이면 지정된 값을 반환
• SUBSTR(name, 1, 1) <> 'M': 'name'의 첫 문자가 'M'이 아닌 행을 선택

 

SQL문은 DBMS의 종류에 따라서 문법이 다르게 사용되는 경우가 있다. 위의 두 query를 비교해보면, if() 함수와 case when(), left() 함수와 substr() 함수가 같은 역할을 하지만, 서로 다르게 쓰이고 있음을 확인할 수 있다. 첫 번째 쿼리는 MySQL의 특징을 보이며, 두 번째 쿼리는 SQLite의 문법을 반영하고 있다.