마침 메인 프로젝트에 잦은 디버깅으로 지쳐가고, 메인 프로젝트의 새로운 스프린트에서 도망치고 싶은 이때, 재밌는 먹잇감쓸데없는 짓이 나타났다. 배터리로 구동되는 제품을 필드 테스트를 내보냈는데, 잘 돌아가고 있는지 확인해봐야하지 않을까 하는 생각이 들었다. "이번 기회에 SQL 문을 짜보자. 기본 문법들은 알고 있으니 잘 할수 있을꺼야!" 라고 생각했다. 그러고 메인 프로젝트에서 도망쳤다.
참고로 변명거리를 만들자면 나는 SQL에 대해서 제대로 공부해본 적이 없다. 처음 사용했던 DB가 mognoDB여서 DB하면 먼저 떠오르는 게 NoSQL이다. 또한, 현재 회사에서는 Python flask에서 DB를 다루기 위해 ORM(sqlAlchemy)으로 쓰고 있기 때문에, SQL 문을 쓸 일이 없었다. 오히려 때때로 서버 코드 중간 중간에 삽입되어있는 row_query 문을 보며 ORM 코드로 바꾸고 싶은 욕망을 느끼곤 했다.
그렇게 오늘 퇴근 이후의 자유시간은 사라졌다.
데이터 정재의 목적은 기기의 구동 시간에 대해서 평균, MIN, MAX 등과 같은 정보를 구하는 것이다.
TABLE은 간략히 다음과 같은 정보를 포함하고 있다.
- IDX: auto_increment
- DEUI: 기기별로 고유한 값
- HEX_STRING: subString으로 시간 부분만 추출해야 한다. 시간은 지금까지의 동작시간이 누산되어 있다.
- DATETIME: updateTime
먼저 HEX_STRING에서 원하는 시간값만 추출하고 읽기 쉽게 십진수로 변환해 준다.
- 문자열 추출: SUBSTRING(col_name, cur, len)
- 진수 변환: CONV(number, from_base, to_base)
- CONV(SUBSTRING(col_name, 41, 8), 16, 10)
이제 누산된 값에서 전에 누산된 값을 빼면, 현재 구동시간을...? "어라? 어떻게 누산된 값에서 전에 누산된 값을 빼지?" (동공지진)
"같은 table에서 다른 row와 어떻게 비교하지?" 라는 문제에 봉착했다. 열심히 구글링을 해보았찌만, 까막눈에게 자비없는 코드들이었다. 이 코드를 복붙하자니 동일한 DEUI에 대해서만 연산을 해야되므로 IDX가 선형으로 증가하지도 않고(where t1.IDX = t2.IDX-1).. 이건 이래서 안되고, 저건 저래서 안되고.. 모든 코드가 안될 것만 같았다. "하지말까?" "왜 굳이 내가 익숙하지도 않은 SQL 문을 투닥거리고 있지?" 하지만 뭔가 지금까지 들인 시간이 아까웠다. 뭔가 남들은 쉽게 할 수 있을것 같고, 조금만 더 생각해보면 될 수 있을 것 같았다. 먼저 다음 행을 찾는 조건을 찾아야 했다. "현재 값보다 작은 첫번째 놈을 찾으면 그게 이전 값이 아닐까?". 그렇다. 다행히 SQL은 (잘 이해는 되지 않지만) 변수를 지원했고, query를 반복하면서 대입도 가능했다.
(뭔가 중간에 select 문을 한 번 줄일 수 있을 것 같다)
SET @deui = 'some-key';
SET @theDay = '2020-03-11 18:00:00';
SELECT @deui AS deui, COUNT(q.diff), AVG(q.diff), STD(q.diff), MAX(q.diff), MIN(q.diff)
FROM
(SELECT t0.DEUI AS DEUI, t0.runtimeAcc - t0.beforeRuntimeAcc AS diff, t0.runtimeAcc AS cur, t0.beforeRuntimeAcc AS befo, t0.DATETIME AS DATETIME
FROM
(SELECT @now:=t1.IDX AS IDX, CONV(SUBSTRING(t1.HEX_STRING, 41, 8), 16, 10) AS runtimeAcc, t1.DATETIME AS DATETIME,
(SELECT CONV(SUBSTRING(CON, 41, 8), 16, 10) FROM `RAW_TB` WHERE DEUI = @deui AND IDX < @now ORDER BY IDX DESC LIMIT 1) AS beforeRuntimeAcc
FROM
(SELECT *
FROM `some_table`
WHERE DEUI = @deui
AND DATETIME > @theDay
ORDER BY DATETIME DESC
) AS t1
ORDER BY IDX DESC
) t0
) q예쁘게 나온 결과를 보고 10분간 만족하며 작성한 SQL 문법에 대해서 보고 있는데, 문득 드는 생각이 "이러면 DEUI를 일일히 넣어줘야 되잖아?" 라고 생각이 들면서, 나 자신과 타협을 시작했다.
하지만 스스로도 비합리적이다라는 생각이 들어 어쩔수 없이 개선해야 된다고 생각을 했다.
array 변수도 가능하지 않을까라는 생각에 찾아보니 쓰기 위해선 더 깊은 세상으로 들어가야하는 것 같았다. SQL문으로 loop라니.. 상상도 하기 싫었다.
왠지 group by라면 해줄수 있을것 같다는 기대를 하고(위 문제를 해결하기 위해 검색하면서 얼핏 본것만 같다) 다시 시도를 해보기로 마음 먹는다. 코드가 조금은 더 복잡해지기 시작한다.
뭔가 인터넷을 뒤지면서 중간 테이블을 만들 수 있는 것 같았고, 중간 테이블이 있으면 척척 갔다쓰고 편하지 않을까하고 만들어보기로 마음 먹었다. 검색하던 중 temporary table을 찾았고, 이거라면 왠지 나의 문제를 해결해 줄수 있을 것 같았다.
CREATE TEMPORARY TABLE IF NOT EXISTS tempTable
AS (
SELECT a.*,
(CASE @vDEUI WHEN a.DEUI THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@vDEUI:=a.DEUI) vDEUI
FROM `some_table` a, (SELECT @vDEUI:='', @rownum:=0 FROM DUAL) b
WHERE a.DATETIME > @theDay
ORDER BY a.DEUI, a.DATETIME
);
SELECT @now:=rnum, DEUI, CT, CONV(SUBSTRING(CON, 41, 8),16, 10) AS runtime, IDX,
(SELECT rnum FROM tempTable WHERE rnum < @now ORDER BY rnum DESC LIMIT 1) AS beforeRnum
FROM tempTable
ORDER BY DEUI, rnum DESC;하지만 이게 왠걸 한번 from 문에서 temporary table 참조하고, 또 다른 from 문에서 temporary table 참조하려고 했더니, can't reopen table 이란다.
좌절하며 갓 구글님께 읊조려보니 링크를 주셨다. 이 링크에 따르면 "You cannot refer to a TEMPORARY table more than once in the same query." 정확히 내가 하고자 하는 일이 되지 않는다고 친절히 설명해주었다. (좌절)
"후... 이제 때려치고 책이나 볼까?" 라고 생각이 들었지만 친절히도 그 밑에 바로 대안을 제시해주었다.
To avoid the error, use a WITH clause that defines a CTE, rather than the TEMPORARY table:
WITH cte AS (SELECT 1 AS col_a, 2 AS col_b)
SELECT * FROM cte AS t1 JOIN cte AS t2;cte가 뭔지는 모르겠지만, 대신 쓰라고 한다. 한번 또 투닥거려보자!
MySQL은 8.0부터 지원이라고 한다. 지금은 새벽 2시 과감히 포기한다. (책이나 볼껄)
-참고 링크
그룹별 번호 매기기
