[SQL] SQL과 친숙해지기 (1)

 2021.05.04 - [Machine Learning & Data] - [SQL] DB(데이터베이스)의 data를 csv로 추출하는 법에서 다뤘던 것처럼 DB의 데이터를 csv로 저장하고 2021.05.02 - [Machine Learning & Data] - [Python] Pandas 대용량 데이터 처리하기로 용량을 줄여서 평화롭게 작업하면 될 줄 알았다.

 

 하지만 이게 웬걸? 전처리를 하기 위해 수백만 건의 데이터에 groupby를 쓰자 내 노트북은 견뎌내지 못했다.

 

MemoryError

 다른 방법을 사용할 수도 있었지만, 이럴 때를 대비해 SQLD 자격증을 따며 배운 SQL 문법과 친숙해지기로 했다. 늘 Python의 DataFrame으로 작업하다가 SQL로 작업하다 보니 코드가 깔끔하지 못한 점 이해 부탁드립니다.

 Data는 아래의 Columns로 이루어져 있다.

 

column list

Mission1. 5월 4일의 데이터 추출

SELECT * FROM `WDS_ROW_PROCESS` as pro
where pro.DAY = 4 and pro.MONTH = 5;

 사실 이 작업은 너무 간단했다. Table명에 pro라는 alias(별명)을 부여하여 수행했다.

---

Mission2. 상주하는 인원 색출

SELECT pro.* FROM `WDS_ROW_PROCESS` as pro
JOIN
(SELECT DISTINCT(MAC_ADDR) FROM `WDS_ROW_PROCESS` as tem
WHERE tem.DURATION > 57600) as du_temp
ON pro.MAC_ADDR = du_temp.MAC_ADDR;

 기기적 결함으로 측정이 중단되는 경우도 간헐적으로 발생하기에 한 곳에 16시간 이상 머무를 경우 상주 인원으로 가정하였다. DataFrame이었다면 df.loc[df['DURATION' > 57600]]로 간단하게 해결할 수 있었겠지만, SQL이었기에 16시간 이상 머무른 인원의 MAC_ADDR를 가진 데이터들만 전체 데이터 중에 골라내었다. 

 굳이 JOIN으로 어렵게 쓰지 않아도 될 것 같은데 우선 떠오르는 대로 작업하였다. 몇 개월 뒤의 내가 되게 부끄러워하며 보게 될 코드가 아닐까 사료된다. 그래도 alias 덕분에 작업이 조금 더 깔끔해진다.

---

Mission3. 유동 데이터 중 AP_ADDR, MAC_ADDR, IN_TIME, DAY로 그룹핑했을 때 마지막 IDX

• 조건. GROUPING 하였을 때 DURATION의 최댓값이 0이면 삭제

SELECT x.* FROM `WDS_ROW_PROCESS` as x
JOIN (SELECT
 AP_ADDR, MAC_ADDR, IN_TIME, DAY, max(IDX) as max_idx
FROM (
	SELECT * FROM `WDS_ROW_PROCESS`
	WHERE `WDS_ROW_PROCESS`.MAC_ADDR != ( SELECT DISTINCT(MAC_ADDR)
    					FROM `WDS_ROW_PROCESS` as tem
                        		WHERE tem.DURATION > 57600)
) as pro_temp
GROUP BY pro_temp.AP_ADDR, pro_temp.MAC_ADDR, pro_temp.IN_TIME, pro_temp.DAY
HAVING max(DURATION) != 0) as y
ON x.idx = y.max_idx;

 아까 만들어뒀던 문법을 이용하여 남은 데이터를 TABLE로 표현하고 GROUP BY 문법을 사용해서 원하는 데이터를 추출하였다. 다행히 IDX가 시간에 따라 계속 커지는 변수였기에 MAX함수를 사용할 수 있었는데, LAST 함수가 없어서 아쉬웠다.

---

 얼떨결에 작업을 하며 여러 가지 문법들을 사용해보았는데 아직 배운 것을 다 활용하진 못했다. 앞으로 조금 더 능숙해질 것이다.

 

 또한 총 9백만 건의 데이터를 마지막 작업까지 끝내고 난 후에는 3만 건으로 추려냈다. 덕분에 앞으로의 작업은 수월해질 듯하다. DB 자체에서 다루니까 Memory Issue도 크지 않아서 SQL의 중요성을 한번 더 깨달았다. 

 

(P.S. 워낙 큰 데이터라 그런가? 실행할 때마다 데이터 개수가 달랐다. 데이터 유실이 발생하는 듯한데 어떻게 해결할 수 있을까?)