2023년 2월 4주차

파피몬 2023. 2. 28. 15:37

2023. 2. 28. 15:37

시간별 데이터 공백 그래프를 엑셀로 그리는 작업 필요

1, 데이터베이스에 있는 데이터를 sql 문으로 긁어오는 작업

2, HeidiSQL 데이터를 CSV로 한 번에 저장할 수 있다.

https://parksrazor.tistory.com/213

MariaDB에서 HeidiSQL 데이터를 CSV로 한번에 이동하기

하이디SQL 에서 아래와 같은 쿼리를 실행합니다. 위 player 결과 테이블에서 우클릭 하면 다음 화면이 뜹니다. 격자 행 내보내기를 클릭하세요. 위와 같이 하면 csv 와 json 파일이 생성된 것을 볼 수

parksrazor.tistory.com

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초 만에 해결하는 법 - 오빠두엑셀

엑셀 날짜 시간 분리, 함수 하나로 3초 만에 해결하기 엑셀 날짜 시간 분리 목차 바로가기 영상강의 예제파일 다운로드 오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니

www.oppadu.com

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/

엑셀 FILTER 함수 사용법 및 실전예제 :: 동적배열함수 - 오빠두엑셀

엑셀 FILTER 함수 사용법 및 실전예제 총정리 :: 동적배열함수 FILTER 함수 목차 바로가기 함수 요약 엑셀 FILTER 함수는 입력한 조건을 바탕으로 데이터를 필터링하는 함수입니다. 함수 구문 = FILTER (

www.oppadu.com

또는

=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/

엑셀 VLOOKUP 함수 여러개 출력 공식 - 단계별 정리 - 오빠두엑셀

엑셀 VLOOKUP 함수 여러개 출력 공식 동작원리 - 단계별 정리 엑셀 VLOOKUP 여러개 출력 공식 목차 바로가기 영상강의 예제파일 다운로드 오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제

www.oppadu.com

으로 날짜별로 데이터 분류 완료

4, 이렇게 하면 그래프를 만드는데 적합한 데이터 구성이 아니기 때문에, 다시 조정하기로 했음.

SELECT
CASE
WHEN '조건' THEN '조건에 만족할 때 출력할 데이터'
ELSE '조건에 만족하지 않을 때 출력 데이터'
END
FROM 테이블;

https://chocoball3.tistory.com/152

[SQL] 오라클 CASE WHEN THEN 표현식 사용하기

SQL을 이용하여 데이터를 출력할 때 조건을 사용하여 표현할 수 있는 CASE문이 존재합니다. DECODE 함수와 비슷하지만 더욱 확장적입니다. 사용 방법은 그렇게 어렵지 않지만 DECODE보다는 조금 복잡

chocoball3.tistory.com

으로 조건에 맞는 데이터일 때 값을 넣어주는 case 문과

https://sksstar.tistory.com/74

[SQL] SQL 함수

기존의 프로그래밍 언어에서 함수를 사용하듯이 SQL언어에서도 다양한 종류의 SQL함수를 제공한다. SQL 함수에는 크게 단일행 함수와 복수행 함수로 나뉘어진다. 단일행 함수 : 입력 값이 단일 행

sksstar.tistory.com

위 예시처럼 월별로 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

엑셀 데이터통합 기능 사용하여 데이터 요약/계산 하기

엑셀 기능 중 데이터통합 기능에 대하여 소개해 드리겠습니다. 데이터통합 기능은 같은 시트내 또는 다른 ...

blog.naver.com

그래프에 넣을 수 있는 데이터 준비 완료

엑셀 그래프 시각화 완료

6, sql 문으로 데이터 추출 후 엑셀로 2차 가공해야 하는데 시간이 많이 잡아먹어 sql 문으로 최대한 해결해보려고 노력

6-1) before과 after로 나누고 임시 테이블의 row 개수 계산하는 작업

FROM 절에서 사용하는 서브쿼리인 인라인 뷰를 이용해서 임시 테이블을 만들고 데이터를 가공한다.
서브쿼리는 웬만하면 지양해야 하지만, 오로지 로컬에서 분류 목적으로 하기 때문에 사용한다.

https://gent.tistory.com/464

[Oracle] 오라클 서브쿼리 종류 및 사용법 (SubQuery)

오라클에서 쿼리문을 작성하다 보면 서브 쿼리(subquery)를 자주 접하게 된다. 서브 쿼리를 처음 접하면 아주 복잡하게 느껴지는데, 기능을 조금만 익히면 쿼리문을 작성할 때 아주 유용하게 사용

gent.tistory.com

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차 가공 안하게 데이터 추출 완료

부연참고)

컨트롤 + 쉬프트 + 화살표 아래쪽으로 셀 범위 선택하는 방법

수식 - 정의된 이름 - 선택 영역에서 만들기로 이름 정의 만드는 방법

https://hantip.net/379

일자별 시간별 매출현황 구하기

안녕하세요. 거래일자와 거래시간, 거래금액이 저장된 데이터가 있습니다. 이 데이터를 일자별 시간별로 나누어 거래금액 합계와 거래건수를 나타내려고 합니다. 실습 파일 다운로드 완성 파일

hantip.net

[데이터] > [데이터 도구] > [중복된 항목 제거] 에서 엑셀 Excel 중복 데이터 없애기

https://mainia.tistory.com/1555

엑셀 Excel 중복 데이터 없애기

엑셀에서는 아주 간단하게 중복을 제거할 수 있는 방법이 있습니다. 중복된 항목 제거 기능이 그것입니다. 중복된 항목 제거 기능의 특징은 중복되는 값의 행 전체를 삭제할 수 있으며, AND 조건

mainia.tistory.com

TO_CHAR에 시간 지정 형식을 통해 비교하기

https://sorrow16.tistory.com/156

날짜 및 시간 형식 변환하기(TO_CHAR)

TO_CHAR는 날짜, 숫자, 문자 값을 지정한 형식의 VARCHAR2 타입 문자열로 변환하는 함수입니다. [사용법]TO_CHAR(날짜 데이터 타입, '지정 형식') 아래와 같이 날짜 지정 형식으로 변환하여 출력할 수 있

sorrow16.tistory.com

'개발' 카테고리의 다른 글

Odroid H2+ 하드 마운트 (0)	2023.02.21
2023년 2월 2주차 (0)	2023.02.14

바보와 천재들의 행진