5분마다 들어오는 값에 대한 데이터베이스 테이블 질문입니다.
`ProjNodeId` INT(11) NULL DEFAULT NULL,
`TagName` VARCHAR(32) NULL DEFAULT NULL,
`LogDate` VARCHAR(12) NULL DEFAULT NULL,
`LogTime` VARCHAR(12) NULL DEFAULT NULL,
`MaxValue` DOUBLE NULL DEFAULT NULL,
`AvgValue` DOUBLE NULL DEFAULT NULL,
`MinValue` DOUBLE NULL DEFAULT NULL,
`LastValue` DOUBLE NULL DEFAULT NULL,
`Alarm` INT(11) NULL DEFAULT NULL,
UNIQUE INDEX `Idx_AnalogTable_1` (`LogDate`, `LogTime`, `TagName`, `ProjNodeId`)
이 구조로 되어 있는 테이블을 트리거 걸어서 hour, day, month, year 로 빼고 있습니다.
실질적으로 select 시 조회하는 테이블은 위 4개 테이블인데요..
인덱스를 어떻게 태울지에 대한 고민이 큽니다. 두세달정도면 이 테이블에 500만개 데이터가 쌓이는데요..
1. DateTime 속성+TagName 속성을 PK로 둘지
2. TagName+Date 속성+Time속성을 PK로 둘지
3. TagName+Timestamp 속성
위 세가지 중 한가지로 하려 합니다.. 일반적으로 where절 조건은 date를 많이 사용하구요
Tagname은 날짜가 있어야 유일하게 식별할 수 있는 구조에요..
어떻게 쓰는게 좋을까요ㅠㅠ
안녕하세요 마농님...그저 빛이십니다...
본문 내용에 덧붙이자면..
질문 드린 테이블 구조는 변경할 수 없는 테이블 구조인 관계로
저기로 데이터가 들어오게 되면 별도 테이블을 구성하여 계산을 합니다.
계산이 필요한 이유는 저 테이블에선 누적값이 들어오기 때문에 사용량 계산이 필요한데 예를들어서
"2" "A" "19/10/18" "09:00:00" "100" "100" "100" "100" "0"
"2" "A" "19/10/18" "10:00:00" "150" "150" "150" "150" "0"
이런식으로 들어오면
09시00분부터 10시까지 사용량은 50이다 라는 처리를 위해 별도 소스 테이블을 구성하였습니다.
09시 시점에 HS 10시 시점에 HE (별도 테이블에서 구성)
HS는 09시 00분에 100이라는 데이터가 들어오면 넣어두고
HE 가 들어오면 HE 값에서 HS에 들어온 값을 빼줍니다. (50이라는 결과 도출)
그럼 한시간에 사용량은 50이라는 데이터가 나오는데 이 데이터는 시간별 테이블에 기록됩니다.
누적값이 나오기 때문에 별도 산출식이 필요하여 이렇게 하고 있는데 질문의 요지는
시간별 테이블에 한시간에 100개 정도 들어올 경우 하루에 2400개, 한달에 7만2천개인데요
이게 또 15분 단위도 있습니다..
그래서 select하는 테이블은 hour,day,month,year 테이블 인데 실질적으로 where 조건에 date와 tagname이 주를 이루고 있는 상황이라 이번에 테이블 구조 변경을 하려합니다...
속성을 많이넣어서 (hour,month,dayofweek 등) 선택도를 높일지 datetime, tagname,value 만 넣을지 고민입니다
원본테이블은 구조 변경이 불가능하여 집계 테이블을 개선하고싶습니다.
집계테이블 hour,day,month,year 모두 동일하게 아래처럼 되어 있습니다.
`TagName` VARCHAR(32) NOT NULL COMMENT '태그명',
`TagType` VARCHAR(50) NOT NULL,
`Identifier` VARCHAR(15) NOT NULL,
`TagUse` VARCHAR(10) NOT NULL DEFAULT '',
`UserID` VARCHAR(50) NULL DEFAULT NULL COMMENT '사용자 ID',
`DayOfWeek` VARCHAR(6) NOT NULL COMMENT '요일',
`LogDateTime` DATETIME NOT NULL COMMENT '로깅 날짜.시간',
`LogDate` DATE NOT NULL,
`LogYear` SMALLINT(6) NOT NULL DEFAULT 0 COMMENT '로깅 연도',
`LogMonth` TINYINT(4) NOT NULL COMMENT '로깅 월',
`LogDay` TINYINT(4) NOT NULL COMMENT '로깅 일',
`LogValue` DOUBLE NOT NULL COMMENT '로깅 값',
PRIMARY KEY (`TagName`, `TagType`, `Identifier`)
위에 되어 있는 구조를
TagName,TagType,LogDateTime,LogValue,UserID 로 변경할 생각인데
그냥 둘지, 고민입니다.
앞서 말씀해주신 주기적인 작업이 트리거로 계산하는걸 말씀하시는건가요 ~???
개별 컬럼으로 하는 것은 기간 검색 조건 주기가 어렵습니다.
하나의 항목으로 관리하는 것이 좋을 듯 합니다.
datetime 으로 할 경우 2019년 이라는 정보를 어떻게 저장할지 모호합니다.
2019-01-01 00:00:00 으로 저장할지?
2019-12-31 00:00:00 으로 저장할지?
2019-12-31 23:59:59 으로 저장할지?
년단위 집계일 때는 차라리 datetime 보다는 varchar(4) 로만 관리하는게 깔끔할 것 같습니다.
각 집계 단위에 띠라 VARCHAR 로 다르게 선언하는 건 어떤가요?
- 년단위 집계 : yyyy
- 월단위 집계 : yyyy-mm
- 일단위 집계 : yyyy-mm-dd
- 시간단위 집계 : yyyy-mm-dd hh24
- 분단위 집계 : yyyy-mm-dd hh24:mi
위에 적혀 있는 집계 테이블 구조라고 되어 있는 테이블이 histroy_hour, history_day, history_month, history_year 테이블이고,
트리거로 사용량 계산(끝값-시작값) 한 값을 넣어주고 있습니다.
말씀해주신대로 Identifier 가 그 역할을 해주고 있습니다.
요지는
1. 속성을 다수 추가해서 선택도를 높이느냐 or TagName,로깅시간(DateTime 혹은 말씀해주신 varchar), 값만 두느냐
2. 트리거 사용을 하지말라구 하셨는데 주기적인 작업이 어떤걸 말씀하시는건지요??
3. 사용량 계산을 해야해서 테이블을 나누고 트리거를 사용하여 저장하고 있는데 사용량 계산을 집계함수로만 처리하는 방안이라면, 위에서 나눈 테이블은 원본 테이블에 있는 값을 그대로 복사만 하라는 말씀이신지요??
1. 속성을 다수 추가해서 선택도를 높인다는 말의 의미를 제가 이해하지 못하겠습니다.
- 선택도를 높인다는게 어떤 의미인지?
2. 트리거는
- 트리거를 어떻게 이용하고 있는지 모르겠지만.
- 트리거는 부담되는 작업입니다.
- 집계 테이블이란게 실시간 정보가 아니라 특정 시점의 집계자료인 만큼 주기별 작업이 좋지 않을까요?
- 예) 시간단위 집계는 1시간에 한번만 작업하면 되겠지요?
- 제가 알고 있는 트리거와 사용하신 트리거가 다른 의미인가요?
- 전반적으로 용어의 의미가 서로 다를게 사용되어 혼란이 오는게 아닐까?. 생각됩니다.
3. 사용량 계산은
- 실제 트리거가 어떻게 적용되고 있는지 알고 싶네요.
- 오라클에서는 트리거로 힘든 작업인데...이게 가능한지도 의문입니다.
- 오라클에서는 트리거에서 자기 자신을 참조할 수 없습니다.
1. 선택도를 높인다는 말은 취소 하겠습니다... 본래의 의미를 제가 잘못 해석 시켜드렸습니다.
2. 트리거는 마농님과 같은 의미가 맞습니다. 트리거는 원본테이블에 누적값이 들어올 경우 사용량 계산을 위해 사용하였습니다. 아래에 설명될 HE값은 5분에 한번씩 들어옵니다..
원본테이블 -> 소스테이블 -> 계산된 테이블(history_hour,day,month,year)
1. 원본테이블에 데이터가 insert 되면 (after insert)
2. 이 데이터에 "분" 이 00분이면 HS와 HE를 변수에 넣고 소스테이블에 insert 합니다.
3. 소스테이블이 insert되면 (after inser) 들어온 HE값에서 HS값을(트리거 내부에서 select 시 최근 HS값을 가져옴) 뺀 후 계산된 테이블에 insert 합니다.
이렇게 한 이유는 원본테이블에 들어오는 데이터는 지속적으로 누적 사용량이 나옵니다
18:00, 100
18:05, 200
18:10, 300...
그래서 별도로 history_hour,month,year 테이블로 나눴던 겁니다..
말씀해주신대로 집계함수나 분석함수를 사용해서 select 시 사용량 계산을 하라고 조언해주신걸 감안하여
history_hour, history_month, history_year 테이블은 산출된 값이 아닌 원본 데이터(누적량)를 넣으면 되는건가요?
원본 테이블에는 HE 만 존재할 듯 하구요.
HS 는 원본테이블에서 다시 조건주고 조회해서 가져오게 되나요?
이게 트리거로 가능한가요? 오라클에서는 불가능.
입력될 때마다 돌아야 할 트리거는 아닌 듯합니다.
트리거의 과정도 좀 복잡하구요.
그냥 쿼리 한방이면 가져올 수 있는 내용인 것 같습니다.
1시간에 한번 시간단위 집계 쿼리를 날려서 집계테이블에 저장하는 방식이 좋을 듯합니다.
일단위 집계는 하루에 한번
원본 테이블 자료 샘플과 그에 따른 집계 결과 테이블 샘플 자료 보여주시면
쿼리 작성 도와드리겠습니다.
그런데 원본에 데이터가 들어올때 5분단위라고 해서 정확하게 00시 05분 00초 로 들어오나요?
보통 1,2초 정도 오차 나게 들어오지 않나요?
중간에 장애가 나서 안들어오는 자료도 있을 듯하구요.
아래가 원본 테이블인데 필요없는 컬럼은 삭제하였습니다. 본문과 좀 다르구요...MariaDB 10.4 버전입니다.
CREATE TABLE `anlogtable` (
`TagName` VARCHAR(32) NULL DEFAULT NULL,
`LogDate` DATE NULL DEFAULT NULL,
`LogTime` TIME NULL DEFAULT NULL,
`LogValue` DOUBLE NULL DEFAULT NULL,
UNIQUE INDEX `Idx_AnalogTable_1` (`LogDate`, `LogTime`, `TagName`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
전날 00시~01시 값은 1시데이터입니다. 이 구조로 봤을때 01시~02시는 2시 데이터 ... 23시~00시 데이터는 그날의 24시 데이터 입니다. 여기 부분이 쪼금 헷갈리실 텐데요...
27일 00시~01시 값은 27일의 1시 데이터고
27일 23시~28일 00시 값은 27일 24시 데이터 입니다.
일 기준은 27일 00시~28일 00시이며 월 기준은 1일 00시~ 그 다음달 1일 00시 입니다.
1년은 1월1일 00시~ 그 다음해 1월1일 00시 입니다.
조회 조건은 15분, 1시간이며, 일 단위(월 기준으로 1일부터 말일까지), 월 단위(연 기준으로 1월부터 12월까지) 연 단위(연도별 합산) 입니다.
위 내용을 엑셀로 뽑은 겁니다.
시간 | 기준 시간 | 시 | |
2020-04-27 | 0:00 | 1:00 | 1 |
2020-04-27 | 1:00 | 2:00 | 2 |
2020-04-27 | 2:00 | 3:00 | 3 |
2020-04-27 | 3:00 | 4:00 | 4 |
2020-04-27 | 4:00 | 5:00 | 5 |
2020-04-27 | 5:00 | 6:00 | 6 |
2020-04-27 | 6:00 | 7:00 | 7 |
2020-04-27 | 7:00 | 8:00 | 8 |
2020-04-27 | 8:00 | 9:00 | 9 |
2020-04-27 | 9:00 | 10:00 | 10 |
2020-04-27 | 10:00 | 11:00 | 11 |
2020-04-27 | 11:00 | 12:00 | 12 |
2020-04-27 | 12:00 | 13:00 | 13 |
2020-04-27 | 13:00 | 14:00 | 14 |
2020-04-27 | 14:00 | 15:00 | 15 |
2020-04-27 | 15:00 | 16:00 | 16 |
2020-04-27 | 16:00 | 17:00 | 17 |
2020-04-27 | 17:00 | 18:00 | 18 |
2020-04-27 | 18:00 | 19:00 | 19 |
2020-04-27 | 19:00 | 20:00 | 20 |
2020-04-27 | 20:00 | 21:00 | 21 |
2020-04-27 | 21:00 | 22:00 | 22 |
2020-04-27 | 22:00 | 23:00 | 23 |
2020-04-27 | 23:00 | 0:00 | 24 |
2020-04-28 | 0:00 |
트리거에서 계산된 집계 값입니다.
H: 00시00분~01시00분
H15: 00시00분~00시15분
H30: 00시15분~00시30분
H45: 00시30분~00시45분
H00: 00시45분~01시00분
집계 테이블 | ||||
SAMPLE | 2019101901 | H | 2019-10-19 1:00 | 35 |
SAMPLE | 2019101902 | H | 2019-10-19 2:00 | 34 |
SAMPLE | 2019101903 | H | 2019-10-19 3:00 | 33 |
SAMPLE | 2019101904 | H | 2019-10-19 4:00 | 34 |
SAMPLE | 2019101905 | H | 2019-10-19 5:00 | 30 |
SAMPLE | 2019101906 | H | 2019-10-19 6:00 | 29 |
SAMPLE | 2019101907 | H | 2019-10-19 7:00 | 31 |
SAMPLE | 2019101908 | H | 2019-10-19 8:00 | 32 |
SAMPLE | 2019101909 | H | 2019-10-19 9:00 | 41 |
SAMPLE | 2019101910 | H | 2019-10-19 10:00 | 46 |
SAMPLE | 2019101911 | H | 2019-10-19 11:00 | 42 |
SAMPLE | 2019101912 | H | 2019-10-19 12:00 | 37 |
SAMPLE | 2019101913 | H | 2019-10-19 13:00 | 45 |
SAMPLE | 2019101914 | H | 2019-10-19 14:00 | 44 |
SAMPLE | 2019101915 | H | 2019-10-19 15:00 | 34 |
SAMPLE | 2019101916 | H | 2019-10-19 16:00 | 34 |
SAMPLE | 2019101917 | H | 2019-10-19 17:00 | 29 |
SAMPLE | 2019101918 | H | 2019-10-19 18:00 | 28 |
SAMPLE | 2019101919 | H | 2019-10-19 19:00 | 28 |
SAMPLE | 2019101920 | H | 2019-10-19 20:00 | 27 |
SAMPLE | 2019101921 | H | 2019-10-19 21:00 | 25 |
SAMPLE | 2019101922 | H | 2019-10-19 22:00 | 29 |
SAMPLE | 2019101923 | H | 2019-10-19 23:00 | 28 |
SAMPLE | 2019101924 | H | 2019-10-20 0:00 | 26 |
SAMPLE | 2019102001 | H | 2019-10-20 1:00 | 27 |
SAMPLE | 2019102002 | H | 2019-10-20 2:00 | 27 |
SAMPLE | 2019102003 | H | 2019-10-20 3:00 | 25 |
SAMPLE | 2019102004 | H | 2019-10-20 4:00 | 26 |
SAMPLE | 2019102005 | H | 2019-10-20 5:00 | 25 |
SAMPLE | 2019102006 | H | 2019-10-20 6:00 | 23 |
SAMPLE | 2019102007 | H | 2019-10-20 7:00 | 24 |
SAMPLE | 2019102008 | H | 2019-10-20 8:00 | 26 |
SAMPLE | 2019102009 | H | 2019-10-20 9:00 | 24 |
SAMPLE | 2019102010 | H | 2019-10-20 10:00 | 27 |
SAMPLE | 2019102011 | H | 2019-10-20 11:00 | 28 |
SAMPLE | 2019102012 | H | 2019-10-20 12:00 | 25 |
SAMPLE | 2019102013 | H | 2019-10-20 13:00 | 26 |
SAMPLE | 2019102014 | H | 2019-10-20 14:00 | 27 |
SAMPLE | 2019102015 | H | 2019-10-20 15:00 | 24 |
SAMPLE | 2019102016 | H | 2019-10-20 16:00 | 27 |
SAMPLE | 2019102017 | H | 2019-10-20 17:00 | 26 |
SAMPLE | 2019102018 | H | 2019-10-20 18:00 | 23 |
SAMPLE | 2019102019 | H | 2019-10-20 19:00 | 24 |
SAMPLE | 2019102020 | H | 2019-10-20 20:00 | 24 |
SAMPLE | 2019102021 | H | 2019-10-20 21:00 | 23 |
SAMPLE | 2019102022 | H | 2019-10-20 22:00 | 25 |
SAMPLE | 2019102023 | H | 2019-10-20 23:00 | 25 |
SAMPLE | 2019102024 | H | 2019-10-21 0:00 | 23 |
SAMPLE | 2019101901 | H00 | 2019-10-19 1:00 | 9 |
SAMPLE | 2019101902 | H00 | 2019-10-19 2:00 | 8 |
SAMPLE | 2019101903 | H00 | 2019-10-19 3:00 | 8 |
SAMPLE | 2019101904 | H00 | 2019-10-19 4:00 | 8 |
SAMPLE | 2019101905 | H00 | 2019-10-19 5:00 | 8 |
SAMPLE | 2019101906 | H00 | 2019-10-19 6:00 | 8 |
SAMPLE | 2019101907 | H00 | 2019-10-19 7:00 | 7 |
SAMPLE | 2019101908 | H00 | 2019-10-19 8:00 | 10 |
SAMPLE | 2019101909 | H00 | 2019-10-19 9:00 | 10 |
SAMPLE | 2019101910 | H00 | 2019-10-19 10:00 | 12 |
SAMPLE | 2019101911 | H00 | 2019-10-19 11:00 | 9 |
SAMPLE | 2019101912 | H00 | 2019-10-19 12:00 | 9 |
SAMPLE | 2019101913 | H00 | 2019-10-19 13:00 | 11 |
SAMPLE | 2019101914 | H00 | 2019-10-19 14:00 | 10 |
SAMPLE | 2019101915 | H00 | 2019-10-19 15:00 | 7 |
SAMPLE | 2019101916 | H00 | 2019-10-19 16:00 | 8 |
SAMPLE | 2019101917 | H00 | 2019-10-19 17:00 | 7 |
SAMPLE | 2019101918 | H00 | 2019-10-19 18:00 | 6 |
SAMPLE | 2019101919 | H00 | 2019-10-19 19:00 | 8 |
SAMPLE | 2019101920 | H00 | 2019-10-19 20:00 | 7 |
SAMPLE | 2019101921 | H00 | 2019-10-19 21:00 | 6 |
SAMPLE | 2019101922 | H00 | 2019-10-19 22:00 | 7 |
SAMPLE | 2019101923 | H00 | 2019-10-19 23:00 | 6 |
SAMPLE | 2019101924 | H00 | 2019-10-20 0:00 | 7 |
SAMPLE | 2019102001 | H00 | 2019-10-20 1:00 | 7 |
SAMPLE | 2019102002 | H00 | 2019-10-20 2:00 | 6 |
SAMPLE | 2019102003 | H00 | 2019-10-20 3:00 | 6 |
SAMPLE | 2019102004 | H00 | 2019-10-20 4:00 | 6 |
SAMPLE | 2019102005 | H00 | 2019-10-20 5:00 | 5 |
SAMPLE | 2019102006 | H00 | 2019-10-20 6:00 | 6 |
SAMPLE | 2019102007 | H00 | 2019-10-20 7:00 | 6 |
SAMPLE | 2019102008 | H00 | 2019-10-20 8:00 | 6 |
SAMPLE | 2019102009 | H00 | 2019-10-20 9:00 | 6 |
SAMPLE | 2019102010 | H00 | 2019-10-20 10:00 | 7 |
SAMPLE | 2019102011 | H00 | 2019-10-20 11:00 | 6 |
SAMPLE | 2019102012 | H00 | 2019-10-20 12:00 | 7 |
SAMPLE | 2019102013 | H00 | 2019-10-20 13:00 | 7 |
SAMPLE | 2019102014 | H00 | 2019-10-20 14:00 | 6 |
SAMPLE | 2019102015 | H00 | 2019-10-20 15:00 | 6 |
SAMPLE | 2019102016 | H00 | 2019-10-20 16:00 | 7 |
SAMPLE | 2019102017 | H00 | 2019-10-20 17:00 | 6 |
SAMPLE | 2019102018 | H00 | 2019-10-20 18:00 | 6 |
SAMPLE | 2019102019 | H00 | 2019-10-20 19:00 | 6 |
SAMPLE | 2019102020 | H00 | 2019-10-20 20:00 | 5 |
SAMPLE | 2019102021 | H00 | 2019-10-20 21:00 | 6 |
SAMPLE | 2019102022 | H00 | 2019-10-20 22:00 | 6 |
SAMPLE | 2019102023 | H00 | 2019-10-20 23:00 | 6 |
SAMPLE | 2019102024 | H00 | 2019-10-21 0:00 | 7 |
SAMPLE | 2019101901 | H15 | 2019-10-19 0:15 | 8 |
SAMPLE | 2019101902 | H15 | 2019-10-19 1:15 | 9 |
SAMPLE | 2019101903 | H15 | 2019-10-19 2:15 | 8 |
SAMPLE | 2019101904 | H15 | 2019-10-19 3:15 | 9 |
SAMPLE | 2019101905 | H15 | 2019-10-19 4:15 | 8 |
SAMPLE | 2019101906 | H15 | 2019-10-19 5:15 | 7 |
SAMPLE | 2019101907 | H15 | 2019-10-19 6:15 | 9 |
SAMPLE | 2019101908 | H15 | 2019-10-19 7:15 | 8 |
SAMPLE | 2019101909 | H15 | 2019-10-19 8:15 | 9 |
SAMPLE | 2019101910 | H15 | 2019-10-19 9:15 | 12 |
SAMPLE | 2019101911 | H15 | 2019-10-19 10:15 | 11 |
SAMPLE | 2019101912 | H15 | 2019-10-19 11:15 | 10 |
SAMPLE | 2019101913 | H15 | 2019-10-19 12:15 | 11 |
SAMPLE | 2019101914 | H15 | 2019-10-19 13:15 | 12 |
SAMPLE | 2019101915 | H15 | 2019-10-19 14:15 | 10 |
SAMPLE | 2019101916 | H15 | 2019-10-19 15:15 | 9 |
SAMPLE | 2019101917 | H15 | 2019-10-19 16:15 | 8 |
SAMPLE | 2019101918 | H15 | 2019-10-19 17:15 | 9 |
SAMPLE | 2019101919 | H15 | 2019-10-19 18:15 | 7 |
SAMPLE | 2019101920 | H15 | 2019-10-19 19:15 | 8 |
SAMPLE | 2019101921 | H15 | 2019-10-19 20:15 | 6 |
SAMPLE | 2019101922 | H15 | 2019-10-19 21:15 | 8 |
SAMPLE | 2019101923 | H15 | 2019-10-19 22:15 | 8 |
SAMPLE | 2019101924 | H15 | 2019-10-19 23:15 | 6 |
SAMPLE | 2019102001 | H15 | 2019-10-20 0:15 | 6 |
SAMPLE | 2019102002 | H15 | 2019-10-20 1:15 | 7 |
SAMPLE | 2019102003 | H15 | 2019-10-20 2:15 | 6 |
SAMPLE | 2019102004 | H15 | 2019-10-20 3:15 | 7 |
SAMPLE | 2019102005 | H15 | 2019-10-20 4:15 | 7 |
SAMPLE | 2019102006 | H15 | 2019-10-20 5:15 | 6 |
SAMPLE | 2019102007 | H15 | 2019-10-20 6:15 | 6 |
SAMPLE | 2019102008 | H15 | 2019-10-20 7:15 | 7 |
SAMPLE | 2019102009 | H15 | 2019-10-20 8:15 | 6 |
SAMPLE | 2019102010 | H15 | 2019-10-20 9:15 | 7 |
SAMPLE | 2019102011 | H15 | 2019-10-20 10:15 | 8 |
SAMPLE | 2019102012 | H15 | 2019-10-20 11:15 | 7 |
SAMPLE | 2019102013 | H15 | 2019-10-20 12:15 | 6 |
SAMPLE | 2019102014 | H15 | 2019-10-20 13:15 | 7 |
SAMPLE | 2019102015 | H15 | 2019-10-20 14:15 | 6 |
SAMPLE | 2019102016 | H15 | 2019-10-20 15:15 | 7 |
SAMPLE | 2019102017 | H15 | 2019-10-20 16:15 | 7 |
SAMPLE | 2019102018 | H15 | 2019-10-20 17:15 | 6 |
SAMPLE | 2019102019 | H15 | 2019-10-20 18:15 | 6 |
SAMPLE | 2019102020 | H15 | 2019-10-20 19:15 | 7 |
SAMPLE | 2019102021 | H15 | 2019-10-20 20:15 | 6 |
SAMPLE | 2019102022 | H15 | 2019-10-20 21:15 | 6 |
SAMPLE | 2019102023 | H15 | 2019-10-20 22:15 | 6 |
SAMPLE | 2019102024 | H15 | 2019-10-20 23:15 | 5 |
SAMPLE | 2019101901 | H30 | 2019-10-19 0:30 | 9 |
SAMPLE | 2019101902 | H30 | 2019-10-19 1:30 | 9 |
SAMPLE | 2019101903 | H30 | 2019-10-19 2:30 | 8 |
SAMPLE | 2019101904 | H30 | 2019-10-19 3:30 | 9 |
SAMPLE | 2019101905 | H30 | 2019-10-19 4:30 | 7 |
SAMPLE | 2019101906 | H30 | 2019-10-19 5:30 | 7 |
SAMPLE | 2019101907 | H30 | 2019-10-19 6:30 | 6 |
SAMPLE | 2019101908 | H30 | 2019-10-19 7:30 | 8 |
SAMPLE | 2019101909 | H30 | 2019-10-19 8:30 | 11 |
SAMPLE | 2019101910 | H30 | 2019-10-19 9:30 | 10 |
SAMPLE | 2019101911 | H30 | 2019-10-19 10:30 | 11 |
SAMPLE | 2019101912 | H30 | 2019-10-19 11:30 | 9 |
SAMPLE | 2019101913 | H30 | 2019-10-19 12:30 | 12 |
SAMPLE | 2019101914 | H30 | 2019-10-19 13:30 | 11 |
SAMPLE | 2019101915 | H30 | 2019-10-19 14:30 | 9 |
SAMPLE | 2019101916 | H30 | 2019-10-19 15:30 | 9 |
SAMPLE | 2019101917 | H30 | 2019-10-19 16:30 | 7 |
SAMPLE | 2019101918 | H30 | 2019-10-19 17:30 | 6 |
SAMPLE | 2019101919 | H30 | 2019-10-19 18:30 | 7 |
SAMPLE | 2019101920 | H30 | 2019-10-19 19:30 | 6 |
SAMPLE | 2019101921 | H30 | 2019-10-19 20:30 | 6 |
SAMPLE | 2019101922 | H30 | 2019-10-19 21:30 | 7 |
SAMPLE | 2019101923 | H30 | 2019-10-19 22:30 | 7 |
SAMPLE | 2019101924 | H30 | 2019-10-19 23:30 | 7 |
SAMPLE | 2019102001 | H30 | 2019-10-20 0:30 | 7 |
SAMPLE | 2019102002 | H30 | 2019-10-20 1:30 | 7 |
SAMPLE | 2019102003 | H30 | 2019-10-20 2:30 | 6 |
SAMPLE | 2019102004 | H30 | 2019-10-20 3:30 | 7 |
SAMPLE | 2019102005 | H30 | 2019-10-20 4:30 | 6 |
SAMPLE | 2019102006 | H30 | 2019-10-20 5:30 | 5 |
SAMPLE | 2019102007 | H30 | 2019-10-20 6:30 | 6 |
SAMPLE | 2019102008 | H30 | 2019-10-20 7:30 | 7 |
SAMPLE | 2019102009 | H30 | 2019-10-20 8:30 | 6 |
SAMPLE | 2019102010 | H30 | 2019-10-20 9:30 | 7 |
SAMPLE | 2019102011 | H30 | 2019-10-20 10:30 | 8 |
SAMPLE | 2019102012 | H30 | 2019-10-20 11:30 | 5 |
SAMPLE | 2019102013 | H30 | 2019-10-20 12:30 | 6 |
SAMPLE | 2019102014 | H30 | 2019-10-20 13:30 | 6 |
SAMPLE | 2019102015 | H30 | 2019-10-20 14:30 | 6 |
SAMPLE | 2019102016 | H30 | 2019-10-20 15:30 | 6 |
SAMPLE | 2019102017 | H30 | 2019-10-20 16:30 | 6 |
SAMPLE | 2019102018 | H30 | 2019-10-20 17:30 | 5 |
SAMPLE | 2019102019 | H30 | 2019-10-20 18:30 | 6 |
SAMPLE | 2019102020 | H30 | 2019-10-20 19:30 | 6 |
SAMPLE | 2019102021 | H30 | 2019-10-20 20:30 | 6 |
SAMPLE | 2019102022 | H30 | 2019-10-20 21:30 | 6 |
SAMPLE | 2019102023 | H30 | 2019-10-20 22:30 | 6 |
SAMPLE | 2019102024 | H30 | 2019-10-20 23:30 | 6 |
SAMPLE | 2019101901 | H45 | 2019-10-19 0:45 | 9 |
SAMPLE | 2019101902 | H45 | 2019-10-19 1:45 | 8 |
SAMPLE | 2019101903 | H45 | 2019-10-19 2:45 | 9 |
SAMPLE | 2019101904 | H45 | 2019-10-19 3:45 | 8 |
SAMPLE | 2019101905 | H45 | 2019-10-19 4:45 | 7 |
SAMPLE | 2019101906 | H45 | 2019-10-19 5:45 | 7 |
SAMPLE | 2019101907 | H45 | 2019-10-19 6:45 | 9 |
SAMPLE | 2019101908 | H45 | 2019-10-19 7:45 | 6 |
SAMPLE | 2019101909 | H45 | 2019-10-19 8:45 | 11 |
SAMPLE | 2019101910 | H45 | 2019-10-19 9:45 | 12 |
SAMPLE | 2019101911 | H45 | 2019-10-19 10:45 | 11 |
SAMPLE | 2019101912 | H45 | 2019-10-19 11:45 | 9 |
SAMPLE | 2019101913 | H45 | 2019-10-19 12:45 | 11 |
SAMPLE | 2019101914 | H45 | 2019-10-19 13:45 | 11 |
SAMPLE | 2019101915 | H45 | 2019-10-19 14:45 | 8 |
SAMPLE | 2019101916 | H45 | 2019-10-19 15:45 | 8 |
SAMPLE | 2019101917 | H45 | 2019-10-19 16:45 | 7 |
SAMPLE | 2019101918 | H45 | 2019-10-19 17:45 | 7 |
SAMPLE | 2019101919 | H45 | 2019-10-19 18:45 | 6 |
SAMPLE | 2019101920 | H45 | 2019-10-19 19:45 | 6 |
SAMPLE | 2019101921 | H45 | 2019-10-19 20:45 | 7 |
SAMPLE | 2019101922 | H45 | 2019-10-19 21:45 | 7 |
SAMPLE | 2019101923 | H45 | 2019-10-19 22:45 | 7 |
SAMPLE | 2019101924 | H45 | 2019-10-19 23:45 | 6 |
SAMPLE | 2019102001 | H45 | 2019-10-20 0:45 | 7 |
SAMPLE | 2019102002 | H45 | 2019-10-20 1:45 | 7 |
SAMPLE | 2019102003 | H45 | 2019-10-20 2:45 | 7 |
SAMPLE | 2019102004 | H45 | 2019-10-20 3:45 | 6 |
SAMPLE | 2019102005 | H45 | 2019-10-20 4:45 | 7 |
SAMPLE | 2019102006 | H45 | 2019-10-20 5:45 | 6 |
SAMPLE | 2019102007 | H45 | 2019-10-20 6:45 | 6 |
SAMPLE | 2019102008 | H45 | 2019-10-20 7:45 | 6 |
SAMPLE | 2019102009 | H45 | 2019-10-20 8:45 | 6 |
SAMPLE | 2019102010 | H45 | 2019-10-20 9:45 | 6 |
SAMPLE | 2019102011 | H45 | 2019-10-20 10:45 | 6 |
SAMPLE | 2019102012 | H45 | 2019-10-20 11:45 | 6 |
SAMPLE | 2019102013 | H45 | 2019-10-20 12:45 | 7 |
SAMPLE | 2019102014 | H45 | 2019-10-20 13:45 | 8 |
SAMPLE | 2019102015 | H45 | 2019-10-20 14:45 | 6 |
SAMPLE | 2019102016 | H45 | 2019-10-20 15:45 | 7 |
SAMPLE | 2019102017 | H45 | 2019-10-20 16:45 | 7 |
SAMPLE | 2019102018 | H45 | 2019-10-20 17:45 | 6 |
SAMPLE | 2019102019 | H45 | 2019-10-20 18:45 | 6 |
SAMPLE | 2019102020 | H45 | 2019-10-20 19:45 | 6 |
SAMPLE | 2019102021 | H45 | 2019-10-20 20:45 | 5 |
SAMPLE | 2019102022 | H45 | 2019-10-20 21:45 | 7 |
SAMPLE | 2019102023 | H45 | 2019-10-20 22:45 | 7 |
SAMPLE | 2019102024 | H45 | 2019-10-20 23:45 | 5 |
원본 데이터 입니다.
원본 테이블 | |||
SAMPLE | 2019-10-19 | 0:00:00 | 88759 |
SAMPLE | 2019-10-19 | 0:05:00 | 88762 |
SAMPLE | 2019-10-19 | 0:10:00 | 88764 |
SAMPLE | 2019-10-19 | 0:15:00 | 88767 |
SAMPLE | 2019-10-19 | 0:20:00 | 88770 |
SAMPLE | 2019-10-19 | 0:25:00 | 88773 |
SAMPLE | 2019-10-19 | 0:30:00 | 88776 |
SAMPLE | 2019-10-19 | 0:35:00 | 88779 |
SAMPLE | 2019-10-19 | 0:40:00 | 88782 |
SAMPLE | 2019-10-19 | 0:45:00 | 88785 |
SAMPLE | 2019-10-19 | 0:50:00 | 88788 |
SAMPLE | 2019-10-19 | 0:55:00 | 88791 |
SAMPLE | 2019-10-19 | 1:00:00 | 88794 |
SAMPLE | 2019-10-19 | 1:05:00 | 88797 |
SAMPLE | 2019-10-19 | 1:10:00 | 88800 |
SAMPLE | 2019-10-19 | 1:15:00 | 88803 |
SAMPLE | 2019-10-19 | 1:20:00 | 88806 |
SAMPLE | 2019-10-19 | 1:25:00 | 88809 |
SAMPLE | 2019-10-19 | 1:30:00 | 88812 |
SAMPLE | 2019-10-19 | 1:35:00 | 88814 |
SAMPLE | 2019-10-19 | 1:40:00 | 88817 |
SAMPLE | 2019-10-19 | 1:45:00 | 88820 |
SAMPLE | 2019-10-19 | 1:50:00 | 88822 |
SAMPLE | 2019-10-19 | 1:55:00 | 88825 |
SAMPLE | 2019-10-19 | 2:00:00 | 88828 |
SAMPLE | 2019-10-19 | 2:05:00 | 88831 |
SAMPLE | 2019-10-19 | 2:10:00 | 88833 |
SAMPLE | 2019-10-19 | 2:15:00 | 88836 |
SAMPLE | 2019-10-19 | 2:20:00 | 88838 |
SAMPLE | 2019-10-19 | 2:25:00 | 88841 |
SAMPLE | 2019-10-19 | 2:30:00 | 88844 |
SAMPLE | 2019-10-19 | 2:35:00 | 88847 |
SAMPLE | 2019-10-19 | 2:40:00 | 88850 |
SAMPLE | 2019-10-19 | 2:45:00 | 88852 |
SAMPLE | 2019-10-19 | 2:50:00 | 88855 |
SAMPLE | 2019-10-19 | 2:55:00 | 88858 |
SAMPLE | 2019-10-19 | 3:00:00 | 88861 |
SAMPLE | 2019-10-19 | 3:05:00 | 88864 |
SAMPLE | 2019-10-19 | 3:10:00 | 88867 |
SAMPLE | 2019-10-19 | 3:15:00 | 88870 |
SAMPLE | 2019-10-19 | 3:20:00 | 88873 |
SAMPLE | 2019-10-19 | 3:25:00 | 88876 |
SAMPLE | 2019-10-19 | 3:30:00 | 88878 |
SAMPLE | 2019-10-19 | 3:35:00 | 88881 |
SAMPLE | 2019-10-19 | 3:40:00 | 88884 |
SAMPLE | 2019-10-19 | 3:45:00 | 88887 |
SAMPLE | 2019-10-19 | 3:50:00 | 88890 |
SAMPLE | 2019-10-19 | 3:55:00 | 88892 |
SAMPLE | 2019-10-19 | 4:00:00 | 88894 |
SAMPLE | 2019-10-19 | 4:05:00 | 88897 |
SAMPLE | 2019-10-19 | 4:10:00 | 88900 |
SAMPLE | 2019-10-19 | 4:15:00 | 88902 |
SAMPLE | 2019-10-19 | 4:20:00 | 88905 |
SAMPLE | 2019-10-19 | 4:25:00 | 88907 |
SAMPLE | 2019-10-19 | 4:30:00 | 88909 |
SAMPLE | 2019-10-19 | 4:35:00 | 88911 |
SAMPLE | 2019-10-19 | 4:40:00 | 88914 |
SAMPLE | 2019-10-19 | 4:45:00 | 88917 |
SAMPLE | 2019-10-19 | 4:50:00 | 88919 |
SAMPLE | 2019-10-19 | 4:55:00 | 88922 |
SAMPLE | 2019-10-19 | 5:00:00 | 88924 |
SAMPLE | 2019-10-19 | 5:05:00 | 88926 |
SAMPLE | 2019-10-19 | 5:10:00 | 88929 |
SAMPLE | 2019-10-19 | 5:15:00 | 88931 |
SAMPLE | 2019-10-19 | 5:20:00 | 88934 |
SAMPLE | 2019-10-19 | 5:25:00 | 88936 |
SAMPLE | 2019-10-19 | 5:30:00 | 88938 |
SAMPLE | 2019-10-19 | 5:35:00 | 88940 |
SAMPLE | 2019-10-19 | 5:40:00 | 88943 |
SAMPLE | 2019-10-19 | 5:45:00 | 88946 |
SAMPLE | 2019-10-19 | 5:50:00 | 88948 |
SAMPLE | 2019-10-19 | 5:55:00 | 88951 |
SAMPLE | 2019-10-19 | 6:00:00 | 88953 |
SAMPLE | 2019-10-19 | 6:05:00 | 88956 |
SAMPLE | 2019-10-19 | 6:10:00 | 88960 |
SAMPLE | 2019-10-19 | 6:15:00 | 88962 |
SAMPLE | 2019-10-19 | 6:20:00 | 88964 |
SAMPLE | 2019-10-19 | 6:25:00 | 88966 |
SAMPLE | 2019-10-19 | 6:30:00 | 88969 |
SAMPLE | 2019-10-19 | 6:35:00 | 88972 |
SAMPLE | 2019-10-19 | 6:40:00 | 88975 |
SAMPLE | 2019-10-19 | 6:45:00 | 88977 |
SAMPLE | 2019-10-19 | 6:50:00 | 88979 |
SAMPLE | 2019-10-19 | 6:55:00 | 88982 |
SAMPLE | 2019-10-19 | 7:00:00 | 88985 |
SAMPLE | 2019-10-19 | 7:05:00 | 88987 |
SAMPLE | 2019-10-19 | 7:10:00 | 88990 |
SAMPLE | 2019-10-19 | 7:15:00 | 88992 |
SAMPLE | 2019-10-19 | 7:20:00 | 88995 |
SAMPLE | 2019-10-19 | 7:25:00 | 88998 |
SAMPLE | 2019-10-19 | 7:30:00 | 89000 |
SAMPLE | 2019-10-19 | 7:35:00 | 89002 |
SAMPLE | 2019-10-19 | 7:40:00 | 89004 |
SAMPLE | 2019-10-19 | 7:45:00 | 89007 |
SAMPLE | 2019-10-19 | 7:50:00 | 89011 |
SAMPLE | 2019-10-19 | 7:55:00 | 89014 |
SAMPLE | 2019-10-19 | 8:00:00 | 89016 |
SAMPLE | 2019-10-19 | 8:05:00 | 89019 |
SAMPLE | 2019-10-19 | 8:10:00 | 89023 |
SAMPLE | 2019-10-19 | 8:15:00 | 89027 |
SAMPLE | 2019-10-19 | 8:20:00 | 89030 |
SAMPLE | 2019-10-19 | 8:25:00 | 89034 |
SAMPLE | 2019-10-19 | 8:30:00 | 89038 |
SAMPLE | 2019-10-19 | 8:35:00 | 89042 |
SAMPLE | 2019-10-19 | 8:40:00 | 89045 |
SAMPLE | 2019-10-19 | 8:45:00 | 89048 |
SAMPLE | 2019-10-19 | 8:50:00 | 89051 |
SAMPLE | 2019-10-19 | 8:55:00 | 89055 |
SAMPLE | 2019-10-19 | 9:00:00 | 89059 |
SAMPLE | 2019-10-19 | 9:05:00 | 89063 |
SAMPLE | 2019-10-19 | 9:10:00 | 89067 |
SAMPLE | 2019-10-19 | 9:15:00 | 89070 |
SAMPLE | 2019-10-19 | 9:20:00 | 89074 |
SAMPLE | 2019-10-19 | 9:25:00 | 89077 |
SAMPLE | 2019-10-19 | 9:30:00 | 89081 |
SAMPLE | 2019-10-19 | 9:35:00 | 89085 |
SAMPLE | 2019-10-19 | 9:40:00 | 89089 |
SAMPLE | 2019-10-19 | 9:45:00 | 89093 |
SAMPLE | 2019-10-19 | 9:50:00 | 89097 |
SAMPLE | 2019-10-19 | 9:55:00 | 89101 |
SAMPLE | 2019-10-19 | 10:00:00 | 89105 |
SAMPLE | 2019-10-19 | 10:05:00 | 89108 |
SAMPLE | 2019-10-19 | 10:10:00 | 89112 |
SAMPLE | 2019-10-19 | 10:15:00 | 89116 |
SAMPLE | 2019-10-19 | 10:20:00 | 89119 |
SAMPLE | 2019-10-19 | 10:25:00 | 89123 |
SAMPLE | 2019-10-19 | 10:30:00 | 89126 |
SAMPLE | 2019-10-19 | 10:35:00 | 89130 |
SAMPLE | 2019-10-19 | 10:40:00 | 89134 |
SAMPLE | 2019-10-19 | 10:45:00 | 89137 |
SAMPLE | 2019-10-19 | 10:50:00 | 89140 |
SAMPLE | 2019-10-19 | 10:55:00 | 89143 |
SAMPLE | 2019-10-19 | 11:00:00 | 89146 |
SAMPLE | 2019-10-19 | 11:05:00 | 89150 |
SAMPLE | 2019-10-19 | 11:10:00 | 89153 |
SAMPLE | 2019-10-19 | 11:15:00 | 89157 |
SAMPLE | 2019-10-19 | 11:20:00 | 89159 |
SAMPLE | 2019-10-19 | 11:25:00 | 89162 |
SAMPLE | 2019-10-19 | 11:30:00 | 89165 |
SAMPLE | 2019-10-19 | 11:35:00 | 89168 |
SAMPLE | 2019-10-19 | 11:40:00 | 89171 |
SAMPLE | 2019-10-19 | 11:45:00 | 89174 |
SAMPLE | 2019-10-19 | 11:50:00 | 89177 |
SAMPLE | 2019-10-19 | 11:55:00 | 89180 |
SAMPLE | 2019-10-19 | 12:00:00 | 89183 |
SAMPLE | 2019-10-19 | 12:05:00 | 89187 |
SAMPLE | 2019-10-19 | 12:10:00 | 89191 |
SAMPLE | 2019-10-19 | 12:15:00 | 89195 |
SAMPLE | 2019-10-19 | 12:20:00 | 89199 |
SAMPLE | 2019-10-19 | 12:25:00 | 89203 |
SAMPLE | 2019-10-19 | 12:30:00 | 89207 |
SAMPLE | 2019-10-19 | 12:35:00 | 89211 |
SAMPLE | 2019-10-19 | 12:40:00 | 89214 |
SAMPLE | 2019-10-19 | 12:45:00 | 89218 |
SAMPLE | 2019-10-19 | 12:50:00 | 89222 |
SAMPLE | 2019-10-19 | 12:55:00 | 89225 |
SAMPLE | 2019-10-19 | 13:00:00 | 89229 |
SAMPLE | 2019-10-19 | 13:05:00 | 89233 |
SAMPLE | 2019-10-19 | 13:10:00 | 89237 |
SAMPLE | 2019-10-19 | 13:15:00 | 89241 |
SAMPLE | 2019-10-19 | 13:20:00 | 89244 |
SAMPLE | 2019-10-19 | 13:25:00 | 89248 |
SAMPLE | 2019-10-19 | 13:30:00 | 89252 |
SAMPLE | 2019-10-19 | 13:35:00 | 89256 |
SAMPLE | 2019-10-19 | 13:40:00 | 89259 |
SAMPLE | 2019-10-19 | 13:45:00 | 89263 |
SAMPLE | 2019-10-19 | 13:50:00 | 89266 |
SAMPLE | 2019-10-19 | 13:55:00 | 89269 |
SAMPLE | 2019-10-19 | 14:00:00 | 89272 |
SAMPLE | 2019-10-19 | 14:05:00 | 89276 |
SAMPLE | 2019-10-19 | 14:10:00 | 89279 |
SAMPLE | 2019-10-19 | 14:15:00 | 89283 |
SAMPLE | 2019-10-19 | 14:20:00 | 89285 |
SAMPLE | 2019-10-19 | 14:25:00 | 89288 |
SAMPLE | 2019-10-19 | 14:30:00 | 89290 |
SAMPLE | 2019-10-19 | 14:35:00 | 89293 |
SAMPLE | 2019-10-19 | 14:40:00 | 89296 |
SAMPLE | 2019-10-19 | 14:45:00 | 89298 |
SAMPLE | 2019-10-19 | 14:50:00 | 89300 |
SAMPLE | 2019-10-19 | 14:55:00 | 89303 |
SAMPLE | 2019-10-19 | 15:00:00 | 89305 |
SAMPLE | 2019-10-19 | 15:05:00 | 89308 |
SAMPLE | 2019-10-19 | 15:10:00 | 89312 |
SAMPLE | 2019-10-19 | 15:15:00 | 89315 |
SAMPLE | 2019-10-19 | 15:20:00 | 89319 |
SAMPLE | 2019-10-19 | 15:25:00 | 89321 |
SAMPLE | 2019-10-19 | 15:30:00 | 89324 |
SAMPLE | 2019-10-19 | 15:35:00 | 89326 |
SAMPLE | 2019-10-19 | 15:40:00 | 89329 |
SAMPLE | 2019-10-19 | 15:45:00 | 89332 |
SAMPLE | 2019-10-19 | 15:50:00 | 89335 |
SAMPLE | 2019-10-19 | 15:55:00 | 89337 |
SAMPLE | 2019-10-19 | 16:00:00 | 89340 |
SAMPLE | 2019-10-19 | 16:05:00 | 89342 |
SAMPLE | 2019-10-19 | 16:10:00 | 89345 |
SAMPLE | 2019-10-19 | 16:15:00 | 89348 |
SAMPLE | 2019-10-19 | 16:20:00 | 89350 |
SAMPLE | 2019-10-19 | 16:25:00 | 89352 |
SAMPLE | 2019-10-19 | 16:30:00 | 89354 |
SAMPLE | 2019-10-19 | 16:35:00 | 89357 |
SAMPLE | 2019-10-19 | 16:40:00 | 89359 |
SAMPLE | 2019-10-19 | 16:45:00 | 89361 |
SAMPLE | 2019-10-19 | 16:50:00 | 89364 |
SAMPLE | 2019-10-19 | 16:55:00 | 89366 |
SAMPLE | 2019-10-19 | 17:00:00 | 89369 |
SAMPLE | 2019-10-19 | 17:05:00 | 89372 |
SAMPLE | 2019-10-19 | 17:10:00 | 89375 |
SAMPLE | 2019-10-19 | 17:15:00 | 89377 |
SAMPLE | 2019-10-19 | 17:20:00 | 89380 |
SAMPLE | 2019-10-19 | 17:25:00 | 89381 |
SAMPLE | 2019-10-19 | 17:30:00 | 89383 |
SAMPLE | 2019-10-19 | 17:35:00 | 89385 |
SAMPLE | 2019-10-19 | 17:40:00 | 89388 |
SAMPLE | 2019-10-19 | 17:45:00 | 89390 |
SAMPLE | 2019-10-19 | 17:50:00 | 89392 |
SAMPLE | 2019-10-19 | 17:55:00 | 89394 |
SAMPLE | 2019-10-19 | 18:00:00 | 89396 |
SAMPLE | 2019-10-19 | 18:05:00 | 89399 |
SAMPLE | 2019-10-19 | 18:10:00 | 89401 |
SAMPLE | 2019-10-19 | 18:15:00 | 89403 |
SAMPLE | 2019-10-19 | 18:20:00 | 89406 |
SAMPLE | 2019-10-19 | 18:25:00 | 89408 |
SAMPLE | 2019-10-19 | 18:30:00 | 89410 |
SAMPLE | 2019-10-19 | 18:35:00 | 89412 |
SAMPLE | 2019-10-19 | 18:40:00 | 89414 |
SAMPLE | 2019-10-19 | 18:45:00 | 89417 |
SAMPLE | 2019-10-19 | 18:50:00 | 89419 |
SAMPLE | 2019-10-19 | 18:55:00 | 89422 |
SAMPLE | 2019-10-19 | 19:00:00 | 89424 |
SAMPLE | 2019-10-19 | 19:05:00 | 89427 |
SAMPLE | 2019-10-19 | 19:10:00 | 89430 |
SAMPLE | 2019-10-19 | 19:15:00 | 89432 |
SAMPLE | 2019-10-19 | 19:20:00 | 89434 |
SAMPLE | 2019-10-19 | 19:25:00 | 89436 |
SAMPLE | 2019-10-19 | 19:30:00 | 89438 |
SAMPLE | 2019-10-19 | 19:35:00 | 89440 |
SAMPLE | 2019-10-19 | 19:40:00 | 89442 |
SAMPLE | 2019-10-19 | 19:45:00 | 89444 |
SAMPLE | 2019-10-19 | 19:50:00 | 89447 |
SAMPLE | 2019-10-19 | 19:55:00 | 89449 |
ㅠㅠ 혼란드려서 죄송해요 불필요한거 지워드린다는게 ^^;;
`ProjNodeId` INT(11) NULL DEFAULT NULL,
`TagName` VARCHAR(32) NULL DEFAULT NULL,
`LogDate` VARCHAR(12) NULL DEFAULT NULL,
`LogTime` VARCHAR(12) NULL DEFAULT NULL,
`MaxValue` DOUBLE NULL DEFAULT NULL,
`AvgValue` DOUBLE NULL DEFAULT NULL,
`MinValue` DOUBLE NULL DEFAULT NULL,
`LastValue` DOUBLE NULL DEFAULT NULL,
`Alarm` INT(11) NULL DEFAULT NULL,
UNIQUE INDEX `Idx_AnalogTable_1` (`LogDate`, `LogTime`, `TagName`, `ProjNodeId`)
이게 원본 테이블이 맞습니다.
주기적인 집계는 15분에 한번 하고 싶습니다.
-- 1. 15분 단위 집계 SELECT b.TagName , a.gb , a.e , b.LogDate , b.LogTime , b.LogValue - c.LogValue LogValue FROM (-- 현재시간 구하기 SELECT 'M15' gb , '2019-10-19 19:00:00' e , '2019-10-19 18:45:00' s -- SELECT 'M15' gb -- , CONCAT(DATE_FORMAT(d1, '%Y-%m-%d %H:'), LPAD(FLOOR(MINUTE(d1) / 15) * 15, 2, '0'), ':00') e -- , CONCAT(DATE_FORMAT(d2, '%Y-%m-%d %H:'), LPAD(FLOOR(MINUTE(d2) / 15) * 15, 2, '0'), ':00') s -- FROM (SELECT NOW() d1, DATE_ADD(NOW(), INTERVAL -15 MINUTE) d2) a ) a INNER JOIN anlogtable b ON b.LogDate = SUBSTR(a.e, 1, 10) AND b.LogTime = SUBSTR(a.e, 12, 8) LEFT OUTER JOIN anlogtable c ON c.LogDate = SUBSTR(a.s, 1, 10) AND c.LogTime = SUBSTR(a.s, 12, 8) AND c.TagName = b.TagName ; -- 2. 1시간 단위 집계 SELECT b.TagName , a.gb , a.e , b.LogDate , b.LogTime , b.LogValue - c.LogValue LogValue FROM (-- 현재시간 구하기 SELECT 'H' gb , '2019-10-19 19:00:00' e , '2019-10-19 18:00:00' s -- SELECT 'H' gb -- , DATE_FORMAT(d1, '%Y-%m-%d %H:00:00') e -- , DATE_FORMAT(d2, '%Y-%m-%d %H:00:00') s -- FROM (SELECT NOW() d1, DATE_ADD(NOW(), INTERVAL -1 HOUR) d2) a ) a INNER JOIN anlogtable b ON b.LogDate = SUBSTR(a.e, 1, 10) AND b.LogTime = SUBSTR(a.e, 12, 8) LEFT OUTER JOIN anlogtable c ON c.LogDate = SUBSTR(a.s, 1, 10) AND c.LogTime = SUBSTR(a.s, 12, 8) AND c.TagName = b.TagName ;