시간별 데이터 공백 그래프를 엑셀로 그리는 작업 필요
1, 데이터베이스에 있는 데이터를 sql 문으로 긁어오는 작업
2, HeidiSQL 데이터를 CSV로 한 번에 저장할 수 있다.
https://parksrazor.tistory.com/213
3, timestamp(m/d/yyyy h:mm)를 mdyyyy와 hmm으로 쪼갠 다음에,
일별로 속한 데이터를 분류해내기
3-1) = INT( 숫자 )로 연월일 분리 후 timestamp와 INT(timestamp)를 서로 빼서 시간 분리를 한다.
https://www.oppadu.com/%EC%97%91%EC%85%80-%EB%82%A0%EC%A7%9C-%EC%8B%9C%EA%B0%84-%EB%B6%84%EB%A6%AC/
3-2)
=FILTER(TEXT(G2:G100,"h:mm:ss AM/PM"), F2:F100=R2)
https://www.oppadu.com/%EC%97%91%EC%85%80-filter-%ED%95%A8%EC%88%98/
또는
=IFERROR(INDEX($C$2:$C$10516, SMALL(IF(H$1=$B$2:$B$10516, MATCH(ROW($B$2:$B$10516), ROW($B$2:$B$10516)),""),ROWS($A$1:A1)),COLUMNS($A$1:$A$1)),"")
https://www.oppadu.com/vlookup-%EC%97%AC%EB%9F%AC%EA%B0%9C-%EB%B6%88%EB%9F%AC%EC%98%A4%EA%B8%B0/
으로 날짜별로 데이터 분류 완료
4, 이렇게 하면 그래프를 만드는데 적합한 데이터 구성이 아니기 때문에, 다시 조정하기로 했음.
SELECT
CASE
WHEN '조건' THEN '조건에 만족할 때 출력할 데이터'
ELSE '조건에 만족하지 않을 때 출력 데이터'
END
FROM 테이블;
https://chocoball3.tistory.com/152
으로 조건에 맞는 데이터일 때 값을 넣어주는 case 문과
https://sksstar.tistory.com/74
위 예시처럼 월별로 column을 만든 후 조건에 맞는 값들을 출력하는 select 문을 이용한다.
SELECT
substr(timestamp,1,10) as dt,
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '00' THEN 24 END, "") "00",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '01' THEN 1 END, "") "01",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '02' THEN 2 END, "") "02",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '03' THEN 3 END, "") "03",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '04' THEN 4 END, "") "04",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '05' THEN 5 END, "") "05",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '06' THEN 6 END, "") "06",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '07' THEN 7 END, "") "07",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '08' THEN 8 END, "") "08",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '09' THEN 9 END, "") "09",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '10' THEN 10 END, "") "10",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '11' THEN 11 END, "") "11",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '12' THEN 12 END, "") "12",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '13' THEN 13 END, "") "13",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '14' THEN 14 END, "") "14",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '15' THEN 15 END, "") "15",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '16' THEN 16 END, "") "16",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '17' THEN 17 END, "") "17",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '18' THEN 18 END, "") "18",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '19' THEN 19 END, "") "19",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '20' THEN 20 END, "") "20",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '21' THEN 21 END, "") "21",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '22' THEN 22 END, "") "22",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '23' THEN 23 END, "") "23"
from drforest.user_activity
WHERE entity_id = 98740 AND field_name = "GPS" and timestamp between '2022-08-02 00:00:00' and '2022-11-01 23:59:59'
GROUP BY
substr(timestamp,1,10),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '00' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '01' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '02' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '03' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '04' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '05' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '06' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '07' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '08' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '09' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '10' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '11' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '12' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '13' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '14' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '15' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '16' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '17' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '18' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '19' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '20' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '21' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '22' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '23' THEN 1 END, "")
으로 특정 날짜에 몇 시에 데이터 있는지 불러오기 완료
5, 월별로 여러 row가 가지고 있는 것들을 데이터 통합 기능으로 월별로 1개 row만 가지도록 한다.
https://m.blog.naver.com/aj4370/221497137124
그래프에 넣을 수 있는 데이터 준비 완료
엑셀 그래프 시각화 완료
6, sql 문으로 데이터 추출 후 엑셀로 2차 가공해야 하는데 시간이 많이 잡아먹어 sql 문으로 최대한 해결해보려고 노력
6-1) before과 after로 나누고 임시 테이블의 row 개수 계산하는 작업
FROM 절에서 사용하는 서브쿼리인 인라인 뷰를 이용해서 임시 테이블을 만들고 데이터를 가공한다.
서브쿼리는 웬만하면 지양해야 하지만, 오로지 로컬에서 분류 목적으로 하기 때문에 사용한다.
SELECT
A.user,
case when substr(A.dt,6,2) >= '05' AND substr(A.dt,6,2) < '07' then "after"
when substr(A.dt,6,2) = '07' OR (substr(A.dt,6,2) = '08' AND substr(A.dt,9,2) <= '20') then "Before"
END as dt,
COUNT(A.hhmm) AS count
FROM (SELECT
entity_id AS user,
substr(timestamp,1,10) as dt,
case when substr(timestamp,15,2)<'10' then concat(substr(timestamp,12,2),':00')
when substr(timestamp,15,2) >='10' and substr(timestamp,15,2)<'20' then concat(substr(timestamp,12,2),':10')
when substr(timestamp,15,2) >='20' and substr(timestamp,15,2)<'30' then concat(substr(timestamp,12,2),':20')
when substr(timestamp,15,2) >='30' and substr(timestamp,15,2)<'40' then concat(substr(timestamp,12,2),':30')
when substr(timestamp,15,2) >='40' and substr(timestamp,15,2)<'50' then concat(substr(timestamp,12,2),':40')
when substr(timestamp,15,2) >='50' and substr(timestamp,15,2)<'60' then concat(substr(timestamp,12,2),':50')
END as hhmm
FROM drforest.user_activity
WHERE (entity_id = 2 AND field_name = "GPS" and timestamp between '2022-05-10 00:00:00' and '2022-08-20 23:59:59')
GROUP BY entity_id,
substr(timestamp,1,10),
case when substr(timestamp,15,2)<'10' then concat(substr(timestamp,12,2),':00')
when substr(timestamp,15,2) >='10' and substr(timestamp,15,2)<'20' then concat(substr(timestamp,12,2),':10')
when substr(timestamp,15,2) >='20' and substr(timestamp,15,2)<'30' then concat(substr(timestamp,12,2),':20')
when substr(timestamp,15,2) >='30' and substr(timestamp,15,2)<'40' then concat(substr(timestamp,12,2),':30')
when substr(timestamp,15,2) >='40' and substr(timestamp,15,2)<'50' then concat(substr(timestamp,12,2),':40')
when substr(timestamp,15,2) >='50' and substr(timestamp,15,2)<'60' then concat(substr(timestamp,12,2),':50')
END) A
GROUP BY
A.user,
A.dt
SELECT
A.dt,
case when SUM(A.00) = 0 then ""
when SUM(A.00) > 0 then SUM(A.00) END AS "00",
case when SUM(A.01) = 0 then ""
when SUM(A.01) > 0 then SUM(A.01) END AS "01",
case when SUM(A.02) = 0 then ""
when SUM(A.02) > 0 then SUM(A.02) END AS "02",
case when SUM(A.03) = 0 then ""
when SUM(A.03) > 0 then SUM(A.03) END AS "03",
case when SUM(A.04) = 0 then ""
when SUM(A.04) > 0 then SUM(A.04) END AS "04",
case when SUM(A.05) = 0 then ""
when SUM(A.05) > 0 then SUM(A.05) END AS "05",
case when SUM(A.06) = 0 then ""
when SUM(A.06) > 0 then SUM(A.06) END AS "06",
case when SUM(A.07) = 0 then ""
when SUM(A.07) > 0 then SUM(A.07) END AS "07",
case when SUM(A.08) = 0 then ""
when SUM(A.08) > 0 then SUM(A.08) END AS "08",
case when SUM(A.09) = 0 then ""
when SUM(A.09) > 0 then SUM(A.09) END AS "09",
case when SUM(A.10) = 0 then ""
when SUM(A.10) > 0 then SUM(A.10) END AS "10",
case when SUM(A.11) = 0 then ""
when SUM(A.11) > 0 then SUM(A.11) END AS "11",
case when SUM(A.12) = 0 then ""
when SUM(A.12) > 0 then SUM(A.12) END AS "12",
case when SUM(A.13) = 0 then ""
when SUM(A.13) > 0 then SUM(A.13) END AS "13",
case when SUM(A.14) = 0 then ""
when SUM(A.14) > 0 then SUM(A.14) END AS "14",
case when SUM(A.15) = 0 then ""
when SUM(A.15) > 0 then SUM(A.15) END AS "15",
case when SUM(A.16) = 0 then ""
when SUM(A.16) > 0 then SUM(A.16) END AS "16",
case when SUM(A.17) = 0 then ""
when SUM(A.17) > 0 then SUM(A.17) END AS "17",
case when SUM(A.18) = 0 then ""
when SUM(A.18) > 0 then SUM(A.18) END AS "18",
case when SUM(A.19) = 0 then ""
when SUM(A.19) > 0 then SUM(A.19) END AS "19",
case when SUM(A.20) = 0 then ""
when SUM(A.20) > 0 then SUM(A.20) END AS "20",
case when SUM(A.21) = 0 then ""
when SUM(A.21) > 0 then SUM(A.21) END AS "21",
case when SUM(A.22) = 0 then ""
when SUM(A.22) > 0 then SUM(A.22) END AS "22",
case when SUM(A.23) = 0 then ""
when SUM(A.23) > 0 then SUM(A.23) END AS "23",
case when COUNT(*) <= 18 then 0
when COUNT(*) > 18 then 1 END AS blank
FROM (SELECT
entity_id AS user,
substr(timestamp,1,10) as dt,
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '00' THEN 24 END, "") "00",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '01' THEN 1 END, "") "01",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '02' THEN 2 END, "") "02",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '03' THEN 3 END, "") "03",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '04' THEN 4 END, "") "04",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '05' THEN 5 END, "") "05",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '06' THEN 6 END, "") "06",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '07' THEN 7 END, "") "07",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '08' THEN 8 END, "") "08",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '09' THEN 9 END, "") "09",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '10' THEN 10 END, "") "10",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '11' THEN 11 END, "") "11",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '12' THEN 12 END, "") "12",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '13' THEN 13 END, "") "13",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '14' THEN 14 END, "") "14",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '15' THEN 15 END, "") "15",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '16' THEN 16 END, "") "16",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '17' THEN 17 END, "") "17",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '18' THEN 18 END, "") "18",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '19' THEN 19 END, "") "19",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '20' THEN 20 END, "") "20",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '21' THEN 21 END, "") "21",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '22' THEN 22 END, "") "22",
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '23' THEN 23 END, "") "23"
from drforest.user_activity
WHERE entity_id = 2
AND field_name = "GPS" and timestamp between '2022-05-10 00:00:00' and '2022-11-01 23:59:59'
GROUP BY
substr(timestamp,1,10),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '00' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '01' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '02' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '03' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '04' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '05' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '06' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '07' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '08' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '09' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '10' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '11' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '12' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '13' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '14' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '15' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '16' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '17' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '18' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '19' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '20' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '21' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '22' THEN 1 END, ""),
ifnull(CASE WHEN to_char(timestamp, 'HH24') = '23' THEN 1 END, "")
) A
GROUP BY
A.dt
엑셀에서 2차 가공 안하게 데이터 추출 완료
부연참고)
1)
컨트롤 + 쉬프트 + 화살표 아래쪽으로 셀 범위 선택하는 방법
수식 - 정의된 이름 - 선택 영역에서 만들기로 이름 정의 만드는 방법
2)
[데이터] > [데이터 도구] > [중복된 항목 제거] 에서 엑셀 Excel 중복 데이터 없애기
https://mainia.tistory.com/1555
3)
TO_CHAR에 시간 지정 형식을 통해 비교하기
https://sorrow16.tistory.com/156
'개발' 카테고리의 다른 글
Odroid H2+ 하드 마운트 (0) | 2023.02.21 |
---|---|
2023년 2월 2주차 (0) | 2023.02.14 |