문제를 하루에 하나씩 풀기 보다는, 하루에 3문제씩 10일에 걸쳐서 solution을 review 해보는 것이 좋다고 판단했다. 이전에 설명했던 부분을 제외하고, 새롭게 알게되는 부분을 추가적으로 포스팅하며 진행하도록 하겠다.
Article Views 1
다음 테이블에서 자신의 기사 중 하나라도 본 작성자의 id를 출력하시오. (단, 결과는 id 순으로 오름차순으로 출력)

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자를 초과하는 경우 유효하지 않다고 간주한다.)

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원이다.)

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의 문법을 반영하고 있다.
'코딩 테스트 > 30 Days of Pandas' 카테고리의 다른 글
| [6일차] python&sql (0) | 2023.08.22 |
|---|---|
| [5일차] python&sql (0) | 2023.08.21 |
| [4일차] python&sql (0) | 2023.08.20 |
| [3일차] python&sql (0) | 2023.08.19 |
| [1일차] python&sql (0) | 2023.08.17 |