CCTV_ver.3.1(feedback) Trigger Query수정, mysql-event

과제 목표

1. MySQL Trigger Query 수정
2. mysql-events 사용하여 데이터 감시

1. MySQL Trigger Query 수정

기존 방법

현재 모든 데이터의 평균 + 1.5* 표준편차 보다 큰 경우 outlier로 판정
➡ 전체 데이터를 분석하여 기준값에 대한 신뢰도가 높지만 데이터가 많아질수록 연산이 오래걸림

SELECT AVG(people)+1.5*STDDEV(people) FROM test;

개선 방안

1. 수동으로 기준 값을 부여

현재 가상 데이터는 사람 : 0 ~ 100, 차량 : 0 ~ 20 으로 주고 있으며 이에 80% 이상이 되면 탐지되도록 함
추후 실제 데이터에서는 CCTV화면이 비추는 면적 내 수동으로 기준점을 설정하도록 함
➡ 데이터 연산이 간단해 지고, 오히려 지금까지의 통계치로 하는 것 보다
기준 면적과 장소적 특성(주차 중심인지, 통행 중심인지 등)을 이용해서 기준을 부여할 수 있음

DELIMITER //
CREATE TRIGGER detection 
	AFTER INSERT 
    ON test 
	FOR EACH ROW
BEGIN
    IF  NEW.people > 80 THEN
		IF  NEW.vehicle > 16 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 > 16 THEN
		INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier Vehicle');
    END IF;
END //
DELIMITER ;

2. 랜덤으로 표본 추출하여 기준값 설정

사람과 차량을 각각 랜덤으로 추출하여 임시 테이블에 저장하고, 기존 평균+ 1.5*표준편차 보다 큰 값을 탐지 (현재는 10개로 함)
➡ 데이터 량이 10개로 정해져있어 연산은 훨씬 줄어듦. 적당한 표본의 크기를 정하여 기준값의 신뢰도를 높일 필요가 있음

DELIMITER //
CREATE TRIGGER detection 
	AFTER INSERT 
    ON test 
	FOR EACH ROW
BEGIN
	CREATE TEMPORARY TABLE temp_people SELECT * FROM test ORDER BY rand() LIMIT 10 ;# 랜덤으로 뽑아 임시 테이블 생성
	CREATE TEMPORARY TABLE temp_vehicle SELECT * FROM test ORDER BY rand() LIMIT 10 ;
	
	
    IF  NEW.people > (SELECT AVG(people)+1.5*STDDEV(people) FROM temp_people) THEN
		IF  NEW.vehicle > (SELECT AVG(vehicle)+1.5*STDDEV(vehicle) FROM temp_vehicle) 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 AVG(vehicle)+1.5*STDDEV(vehicle) FROM temp_vehicle) THEN
		INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier Vehicle');
    END IF;
    
    DROP TEMPORARY TABLE temp_people;
    DROP TEMPORARY TABLE temp_vehicle;
END //
DELIMITER ;

2. mysql-events 사용하여 데이터 감시

참고 페이지

기존 방법 : 스케줄러를 이용해 5분마다 데이터 마지막 행 감시

io.on("connection", (socket) => {
    console.log(`connect: ${socket.id}`);

    var lastRowID = -1;
    var job = schedule.scheduleJob(
        '*/5 * * * *', // 주기 (5분마다)
    function() {
        var sql = 'select * from backup order by id desc limit 1';
        conn.query(sql, (err, row) => {
            if (err) {
                console.log(err);
            } else {
                if (lastRowID != row[0].id) {
                    lastRowID = row[0].id;
                    console.log(row[0]);
                    socket.emit("outlierData", row[0]);
                } else {
                    console.log('not update database');
                }
            }
        });
    });
});

mysql-events 사용한 방법

백엔드에서 기존 행과 변경된 행을 지속적으로 감시하다가 oldRow가 null인 경우 (새로운 행이 추가된 경우) 마지막 행을 emit

io.on("connection", (socket) => {
    s = socket;
    console.log(`connect: ${socket.id}`);

    // mysql events
    var dsn = {
        host:     "",
        user:     "",
        password: "",
      };
      var lastRowID = -1;
      var mysqlEventWatcher = MySQLEvents(dsn);
      var watcher =mysqlEventWatcher.add(
        'test.backup',
        function (oldRow, newRow, event) {
        if (oldRow === null) {       
            var sql = 'select * from backup order by id desc limit 1';
            conn.query(sql, (err, row) => {
                if (err) {
                    console.log(err);
                } else {
                    socket.emit("outlierData", row[0]);
                    console.log(row[0]);
                }
            });
        }
        },
        'Active'
    );
});

생각해보아야 할 점

마지막 행을 찾는 쿼리문 select * from backup order by id desc limit 1; 없이 newRow의 요소에 접근하여 바로 보내줄 순 없을까…? console.log(newRow.changedColumns); 를 출력해보면 비어있음 []