layout	title	author	date	comments
single	CCTV_ver_4.1. 데이터 분석 시스템	seohyun-kim	2021-08-05 18:00	true

➡ 위 이미지를 클릭하면 Repository로 이동합니다.

과제 목표

리눅스 서버 환경 구축
데이터 베이스 분석 시스템(분석 table생성, 이벤트 생성, 이상값 탐지)
영상팀에서 호출할 수 있는 API 생성(인증절차 추후 적용 예정)

1. 리눅스 서버 환경 구축

리눅스 서버에 mysql설치 및 접속설정

mysql 설치 완료 , DB생성
사용자 계정 생성 및 DB권한 부여
외부 접속 허용 3306 포트 allow

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cctv               |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

mysql> show grants;
+-------------------------------------------------+
| Grants for malab@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `malab`@`%`               |
| GRANT ALL PRIVILEGES ON `cctv`.* TO `malab`@`%` |
+-------------------------------------------------+
2 rows in set (0.01 sec)

Node.js 설치

PPA 로 node.js와 npm설치

$ node -v
v14.17.4
$ npm -v
6.14.14

MySQL table 생성

외래키를 설정하여 rid로 참조될 수 있도록 함

mysql> show tables;
+------------------------+
| Tables_in_cctv         |
+------------------------+
| bicycle_pos            |
| bicycle_speed          |
| bus_pos                |
| bus_speed              |
| car_pos                |
| car_speed              |
| cctv_bbox              | 
| cctv_data              | < --- 기본 전체 데이터 (1분당 1 row)
| electric_scooter_pos   |
| electric_scooter_speed |
| motocycle_pos          |
| motocycle_speed        |
| person_pos             |
| person_speed           |
| truck_pos              |
| truck_speed            |
+------------------------+
16 rows in set (0.00 sec)

mysql> DESC cctv_data;
+--------------------+------------+------+-----+---------+----------------+
| Field              | Type       | Null | Key | Default | Extra          |
+--------------------+------------+------+-----+---------+----------------+
| rid                | int        | NO   | PRI | NULL    | auto_increment |
| date_time          | timestamp  | YES  |     | NULL    |                |
| n_person           | int        | YES  |     | NULL    |                |
| n_car              | int        | YES  |     | NULL    |                |
| n_truck            | int        | YES  |     | NULL    |                |
| n_bus              | int        | YES  |     | NULL    |                |
| n_bicycle          | int        | YES  |     | NULL    |                |
| n_motorcycle       | int        | YES  |     | NULL    |                |
| n_electric_scooter | int        | YES  |     | NULL    |                |
| image              | mediumblob | YES  |     | NULL    |                |
+--------------------+------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> desc person_pos;
+-----------+-----------+------+-----+---------+-------+
| Field     | Type      | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| rid       | int       | NO   | PRI | NULL    |       |
| date_time | timestamp | YES  |     | NULL    |       |
| loc_0     | int       | YES  |     | NULL    |       |
| loc_1     | int       | YES  |     | NULL    |       |
| loc_2     | int       | YES  |     | NULL    |       |
| loc_3     | int       | YES  |     | NULL    |       |
| loc_4     | int       | YES  |     | NULL    |       |
| loc_5     | int       | YES  |     | NULL    |       |
| loc_6     | int       | YES  |     | NULL    |       |
| loc_7     | int       | YES  |     | NULL    |       |
| loc_8     | int       | YES  |     | NULL    |       |
+-----------+-----------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql> desc person_speed;
+-----------+-----------+------+-----+---------+-------+
| Field     | Type      | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| rid       | int       | NO   | PRI | NULL    |       |
| date_time | timestamp | YES  |     | NULL    |       |
| loc_0     | float     | YES  |     | NULL    |       |
| loc_1     | float     | YES  |     | NULL    |       |
| loc_2     | float     | YES  |     | NULL    |       |
| loc_3     | float     | YES  |     | NULL    |       |
| loc_4     | float     | YES  |     | NULL    |       |
| loc_5     | float     | YES  |     | NULL    |       |
| loc_6     | float     | YES  |     | NULL    |       |
| loc_7     | float     | YES  |     | NULL    |       |
| loc_8     | float     | YES  |     | NULL    |       |
+-----------+-----------+------+-----+---------+-------+
11 rows in set (0.00 sec)

Table 생성 코드

create table cctv_data(
   rid int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   date_time TIMESTAMP ,
   n_person int ,
   n_car int,
   n_truck int,
   n_bus int,
   n_bicycle int,
   n_motorcycle int,
   n_electric_scooter int
);    

create table person_pos(
   rid int NOT NULL PRIMARY KEY,
   date_time TIMESTAMP ,
   loc_0 int,
   loc_1 int,
   loc_2 int,
   loc_3 int,
   loc_4 int,
   loc_5 int,
   loc_6 int,
   loc_7 int,
   loc_8 int,
   foreign key (rid) references cctv_data (rid)
);  

  create table person_speed(
   rid int NOT NULL primary key,
   date_time TIMESTAMP ,
   loc_0  float,
   loc_1  float,
   loc_2  float,
   loc_3  float,
   loc_4  float,
   loc_5  float,
   loc_6  float,
   loc_7  float,
   loc_8  float,
   foreign key (rid) references cctv_data (rid)
);

타임존 설정 및 확인

이상하게 UTC시간이 현재 한국시간으로 나오고, 서버의 KST시간이 현재한국시간보다 +9 시간으로 나온다.....
그래서 다시 UTC 기준으로 변경하였다.

우분투 서버

  ~$ timedatectl
               Local time: 토 2021-08-21 22:38:11 UTC --------현재 한국시간
           Universal time: 토 2021-08-21 22:38:11 UTC
                 RTC time: 토 2021-08-21 22:38:11
                Time zone: UTC (UTC, +0000)
System clock synchronized: no
              NTP service: active
          RTC in local TZ: no

mysql
mysql도 마찬가지로 +9 시간으로 나와서 SYSTEM시간으로 돌려주었다.

mysql> select @@global.time_zone, @@session.time_zone,@@system_time_zone;
+--------------------+---------------------+--------------------+
| @@global.time_zone | @@session.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM             | SYSTEM              | UTC                |
+--------------------+---------------------+--------------------+
1 row in set (0.04 sec)

2. 데이터 베이스 분석 시스템

by. 서현, 가희

1. 데이터베이스 분석 테이블 생성 및 이벤트 스케줄러 생성

- 데이터베이스 테이블 생성

Table 생성 코드

  # 일 별 테이블 생성
  create table day_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _date date,
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
   desc day_stats;

  # 주 별 테이블 생성
  create table week_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date,
      end_date date,
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월 별 테이블 생성
  create table month_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
   
 # 연도별 테이블 생성
  create table year_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, #년도
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  

  # 월간 요일별 테이블 생성 (월요일)
  create table monthly_mon_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성(화요일)
  create table monthly_tue_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성(수요일)
  create table monthly_wed_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성(목요일)
  create table monthly_thu_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성 (금요일)
  create table monthly_fri_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성 (토요일)
  create table monthly_sat_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성 (일요일)
  create table monthly_sun_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  show tables;

  # 연간 요일별 테이블 생성 (월요일)
  create table yearly_mon_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 연간 요일별 테이블 생성 (화요일)
  create table yearly_tue_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (수요일)
  create table yearly_wed_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (목요일)
  create table yearly_thu_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (금요일)
  create table yearly_fri_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (토요일)
  create table yearly_sat_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 연간 요일별 테이블 생성 (일요일)
  create table yearly_sun_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

- MySQl Event Scheduler

💢 주의
CCTV 데이터는 현재 로컬 시간에 대한 UTC시간으로 저장될 것
본 Event는 UTC시간을 기준으로 스케줄러가 작동됨
따라서 데이터 조회 시, 사용자로부터 입력받은 로컬시간을 UTC시간으로 변환하여 조회해야함

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 `day_stats` 테이블에 저장

CREATE EVENT `day_stats`
    ON SCHEDULE EVERY 1 Day 
    STARTS CURDATE() #오늘부터 시작 (표준시간 기준임, 한국시간은 +9 hour 해줘야 함)
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of day'
    DO 
	INSERT INTO day_stats(_date, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT  DATE_ADD(CURDATE(),INTERVAL -1 day)  , CONVERT(AVG(people), float), CONVERT(AVG(people)+1.5*STDDEV(people), float),
				CONVERT(AVG(vehicle), float), CONVERT(AVG(vehicle)+1.5*STDDEV(vehicle), float)
                FROM test  WHERE DATE(start_date) =  DATE_ADD(CURDATE(), INTERVAL -1 day) #어제 데이터

2). 매주 월요일에 `day_stats` table의 일주일 치(7개의 row) 평균을 `week_stats` 테이블에 저장

  CREATE EVENT `week_stats`
    ON SCHEDULE
	EVERY 1 WEEK
	STARTS CURRENT_DATE + INTERVAL 7- WEEKDAY(CURRENT_DATE) DAY #가장 가까운 미래의 월요일
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of week'
    DO 
	INSERT INTO week_stats(start_date, end_date, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT CURRENT_DATE + INTERVAL -7- WEEKDAY(CURRENT_DATE) DAY,#지난 주 월요일
			CURRENT_DATE + INTERVAL -1 - WEEKDAY(CURRENT_DATE) DAY, #지난 주 일요일
			CONVERT(AVG(avg_people), float), CONVERT(AVG(std_people), float),
			CONVERT(AVG(avg_vehicle), float), CONVERT(AVG(std_vehicle), float)
			FROM day_stats WHERE _date BETWEEN DATE_ADD(NOW(),INTERVAL -1 WEEK ) AND NOW() #오늘(매주 월요일 0시) 기준으로 지난 일주일 간

3). 매달 1일에 `day_stats` table의 한달 치 평균을 `month_stats` 테이블에 저장

 CREATE EVENT `month_stats`
    ON SCHEDULE
	EVERY 1 MONTH
	STARTS LAST_DAY(NOW()) + interval 1 DAY #가장 가까운 1일부터 시작
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of week'
    DO 
	INSERT INTO month_stats(start_date, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT LAST_DAY(NOW() - interval 2 month) + interval 1 DAY, #지난 달의 1일
			CONVERT(AVG(avg_people), float), CONVERT(AVG(std_people), float),
			CONVERT(AVG(avg_vehicle), float), CONVERT(AVG(std_vehicle), float)
			FROM day_stats WHERE _date BETWEEN DATE_ADD(NOW(),INTERVAL -1 WEEK ) AND NOW()

4). 매년 1월1일에 `month_stats` table의 일년 치 평균(12개의 row)을 `year_stats` 테이블에 저장

 CREATE EVENT `year_stats`
    ON SCHEDULE
	EVERY 1 YEAR
	STARTS LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH))+ interval 1 DAY #가장 가까운 1월 1일부터 시작
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of year'
    DO 
	INSERT INTO year_stats(_year, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT YEAR(CURDATE()-interval 1 DAY), # 지난 년도
			CONVERT(AVG(avg_people), float), CONVERT(AVG(std_people), float),
			CONVERT(AVG(avg_vehicle), float), CONVERT(AVG(std_vehicle), float)
			FROM month_stats where start_date BETWEEN DATE_ADD(NOW(),INTERVAL -1 YEAR ) AND NOW() #지난 일년

2. 이상값 탐지 (MySQL Trigger)

DELIMITER //
CREATE TRIGGER detection 
	AFTER INSERT 
    ON test 
	FOR EACH ROW
BEGIN

# 기준 값 설정
	DECLARE people_ref_val, vehicle_ref_val FLOAT DEFAULT NULL;
    
	IF (SELECT COUNT(_id) FROM year_stats) >=1 THEN 	#년간 데이터가 있을 때
		SET people_ref_val = (SELECT AVG(std_people) FROM year_stats); #N년의 평균값으로 함
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM year_stats);
        
	ELSE IF (SELECT COUNT(_id) FROM month_stats) >=1 THEN
		SET people_ref_val = (SELECT AVG(std_people) FROM month_stats);
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM month_stats);
        
	ELSE IF (SELECT COUNT(_id) FROM week_stats) >=1 THEN
		SET people_ref_val = (SELECT AVG(std_people) FROM week_stats);
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM week_stats);
			
	ELSE IF (SELECT COUNT(_id) FROM day_stats) >=1 THEN
		SET people_ref_val = (SELECT AVG(std_people) FROM day_stats);
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM day_stats);
				END IF;
			END IF;
		END IF;
	END IF;

	# 설정된 기준 값으로 이상값 탐지
    IF  NEW.people > (SELECT @people_ref_val) THEN
		IF  NEW.vehicle > (SELECT @vehicle_ref_val) THEN
			INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier People & Vehicle');
		ELSE 
			INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier People');
		END IF;
	ELSEIF NEW.vehicle > (SELECT @vehicle_ref_val) THEN
		INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier Vehicle');
    END IF;
    
   
END //
DELIMITER ;

3. 실제 데이터 처리 방법

3. 영상팀에서 호출할 수 있는 API 생성 -- 사용 X

by. 태훈

수동으로 입력받는 페이지를 만들었습니다.
token을 통한 보안 인증 절차를 추가하였습니다.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

2021-08-05-CCTV.ver.4.1.md

2021-08-05-CCTV.ver.4.1.md

과제 목표

1. 리눅스 서버 환경 구축

리눅스 서버에 mysql설치 및 접속설정

Node.js 설치

MySQL table 생성

타임존 설정 및 확인

2. 데이터 베이스 분석 시스템

1. 데이터베이스 분석 테이블 생성 및 이벤트 스케줄러 생성

- 데이터베이스 테이블 생성

- MySQl Event Scheduler

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 `day_stats` 테이블에 저장

2). 매주 월요일에 `day_stats` table의 일주일 치(7개의 row) 평균을 `week_stats` 테이블에 저장

3). 매달 1일에 `day_stats` table의 한달 치 평균을 `month_stats` 테이블에 저장

4). 매년 1월1일에 `month_stats` table의 일년 치 평균(12개의 row)을 `year_stats` 테이블에 저장

2. 이상값 탐지 (MySQL Trigger)

3. 실제 데이터 처리 방법

3. 영상팀에서 호출할 수 있는 API 생성 -- 사용 X

Files

2021-08-05-CCTV.ver.4.1.md

Latest commit

History

2021-08-05-CCTV.ver.4.1.md

File metadata and controls

과제 목표

1. 리눅스 서버 환경 구축

리눅스 서버에 mysql설치 및 접속설정

Node.js 설치

MySQL table 생성

타임존 설정 및 확인

2. 데이터 베이스 분석 시스템

1. 데이터베이스 분석 테이블 생성 및 이벤트 스케줄러 생성

- 데이터베이스 테이블 생성

- MySQl Event Scheduler

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 day_stats 테이블에 저장

2). 매주 월요일에 day_stats table의 일주일 치(7개의 row) 평균을 week_stats 테이블에 저장

3). 매달 1일에 day_stats table의 한달 치 평균을 month_stats 테이블에 저장

4). 매년 1월1일에 month_stats table의 일년 치 평균(12개의 row)을 year_stats 테이블에 저장

2. 이상값 탐지 (MySQL Trigger)

3. 실제 데이터 처리 방법

3. 영상팀에서 호출할 수 있는 API 생성 -- 사용 X

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 `day_stats` 테이블에 저장

2). 매주 월요일에 `day_stats` table의 일주일 치(7개의 row) 평균을 `week_stats` 테이블에 저장

3). 매달 1일에 `day_stats` table의 한달 치 평균을 `month_stats` 테이블에 저장

4). 매년 1월1일에 `month_stats` table의 일년 치 평균(12개의 row)을 `year_stats` 테이블에 저장