[내일배움캠프] 30일 차 - SQL 코드카타, 데이터 전처리&시각화 4

 

 

 

 SQL 코드카타

 

 

문제 66

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

 

☑ link

 

 

처음 답안 (통과! 하지만 비효율적)

SELECT b.FILE_PATH
FROM
(
SELECT a.FILE_PATH, 
       a.VIEWS,
       MAX(a.VIEWS) OVER() AS MAX_VIEW -- MAX()
FROM
(
SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT)FILE_PATH,
       B.VIEWS
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_FILE F
ON B.BOARD_ID = F.BOARD_ID
ORDER BY B.VIEWS DESC, F.FILE_ID DESC -- 서브쿼리에서 정렬
) a
) b
WHERE b.VIEWS = b.MAX_VIEW

 

최종 답안

SELECT a.FILE_PATH
FROM
(
SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT)FILE_PATH,
       B.VIEWS,
       F.FILE_ID,
       RANK() OVER(ORDER BY B.VIEWS DESC) AS RANK_VIEW -- RANK()
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_FILE F
ON B.BOARD_ID = F.BOARD_ID
) a
WHERE a.RANK_VIEW = 1
ORDER BY a.FILE_ID DESC -- 본 쿼리에서 정렬

 

 

개선점

 

1. 최댓값이 여러 개일 때, 최댓값만 출력되게 하기 → 윈도우 함수 RANK()

• RANK() OVER(ORDER BY 정렬기준 DESC)
  최댓값 구해야 하므로 DESC
  본 쿼리에서 WHERE a.RANK_column2 = 1 조건 사용
• 템플릿

SELECT a.column1
FROM
(
SELECT column1,
       RANK() OVER(ORDER BY column2 DESC) AS RANK_column2
FROM table
) a
WHERE a.RANK_column2 = 1

 

2. 최종 결과에 대해 정렬할 때, 본 쿼리에 ORDER BY 사용

 

 

 

 데이터 전처리 & 시각화 Pandas - 4

 

 

 

데이터 전처리 & 시각화 강의 3주차 : Pandas 위주로 정리 - 4

 

6. 데이터 전처리 - 데이터 병합

 

1) concat()

• 데이터프레임을 위아래로 연결
• 데이터프레임을 좌우로 연결
• axis
  • axis=0 → 위아래로 연결 (기본값)
  • axis=1 → 좌우로 연결
• ignore_index
  • ignore_index=False (기본값)
    연결된 데이터프레임의 기존 인덱스 유지
  • ignore_index=True
    기존 인덱스 무시하고 새로운 인덱스 설정

import pandas as pd

# 두 개의 데이터프레임 생성
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2']})
df2 = pd.DataFrame({'A': ['A3', 'A4', 'A5'], 'B': ['B3', 'B4', 'B5']})

# 위아래로 데이터프레임 연결
result_vertical = pd.concat([df1, df2], axis=0)

# 좌우로 데이터프레임 연결
result_horizontal = pd.concat([df1, df2], axis=1)

# 기존 인덱스 무시하고 새 인덱스 설정
result_new_index = pd.concat([df1, df2], axis=0).reset_index(drop=True)
result_new_index_2 = pd.concat([df1, df2], axis=0, ignore_index=True)

 

2) merge()

• 데이터 좌우로 합치기
• SQL의 JOIN 연산과 유사한 방식으로 데이터프레임 합침
  • 공통된 열이나 인덱스 기준으로 데이터 병합

• left / right
  병합의 기준이 되는 왼쪽, 오른쪽 데이터프레임
• how
  병합 방법 나타내는 매개변수
  아래의 옵션 있음
  • inner
    공통된 키(열) 기준으로 교집합 만듦
  • outer
    공통된 키 기준으로 합집합 만듦
  • left
    왼쪽 데이터프레임의 모든 행 포함하고
    오른쪽 데이터프레임은 공통된 키에 해당하는 행만 포함
  • right
    오른쪽 데이터프레임의 모든 행 포함하고
    왼쪽 데이터프레임은 공통된 키에 해당하는 행만 포함
• on
  병합 기준이 되는 열 이름(혹은 열 이름의 리스트) 지정
  • left_on / right_on
    왼쪽 데이터프레임과 오른쪽 데이터프레임에서 병합할 열 이름이 다른 경우에 사용

import pandas as pd

# 두 개의 데이터프레임 생성
left_df = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value': [1, 2, 3, 4]})
right_df = pd.DataFrame({'key': ['B', 'D', 'E', 'F'], 'value': [5, 6, 7, 8]})

# 'key' 열을 기준으로 두 데이터프레임 병합
merged_df = pd.merge(left_df, right_df, on='key', how='inner')

 

 

 

자료 출처

스파르타 코딩클럽 데이터 전처리 & 시각화 강의