튜토리얼 3: 실시간 분석

-- 생산 라인 상태
CREATE VOLATILE TABLE production_status (
    line_id INTEGER PRIMARY KEY,
    line_name VARCHAR(50),
    status VARCHAR(20),
    current_product VARCHAR(100),
    items_produced INTEGER,
    target_rate INTEGER,
    actual_rate INTEGER,
    last_updated DATETIME
);

-- 생산 라인 초기화
INSERT INTO production_status VALUES (
    1, 'Assembly Line A', 'RUNNING', 'Widget-X100', 1250, 100, 98, NOW
);
INSERT INTO production_status VALUES (
    2, 'Assembly Line B', 'RUNNING', 'Widget-X200', 890, 80, 82, NOW
);
INSERT INTO production_status VALUES (
    3, 'Packaging Line 1', 'IDLE', NULL, 0, 120, 0, NOW
);
INSERT INTO production_status VALUES (
    4, 'Assembly Line C', 'WARNING', 'Widget-X100', 450, 100, 75, NOW
);
INSERT INTO production_status VALUES (
    5, 'Quality Check', 'RUNNING', 'Widget-X200', 320, 50, 48, NOW
);

-- 생산 라인이 배치 완료
UPDATE production_status
SET items_produced = items_produced + 50,
    actual_rate = 101,
    last_updated = NOW
WHERE line_id = 1;

-- 라인이 유지보수 시작
UPDATE production_status
SET status = 'MAINTENANCE',
    current_product = NULL,
    actual_rate = 0,
    last_updated = NOW
WHERE line_id = 3;

-- 라인이 경고 해결
UPDATE production_status
SET status = 'RUNNING',
    actual_rate = 98,
    last_updated = NOW
WHERE line_id = 4;

-- 모든 생산 라인
SELECT * FROM production_status
ORDER BY line_id;

-- 실행 중인 라인만
SELECT line_id, line_name, actual_rate, target_rate
FROM production_status
WHERE status = 'RUNNING';

-- 문제가 있는 라인
SELECT line_id, line_name, status
FROM production_status
WHERE status IN ('WARNING', 'ERROR', 'MAINTENANCE');

-- 성능 메트릭
SELECT
    line_name,
    actual_rate,
    target_rate,
    ROUND((actual_rate * 100.0 / target_rate), 2) as efficiency_pct
FROM production_status
WHERE status = 'RUNNING';

-- 모든 상태 변경 로그
CREATE TABLE production_events (
    line_id INTEGER,
    event_type VARCHAR(50),
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    message VARCHAR(500)
);

-- 이벤트 로그 삽입
INSERT INTO production_events VALUES (
    4, 'STATUS_CHANGE', 'WARNING', 'RUNNING', 'Line recovered from warning state'
);
INSERT INTO production_events VALUES (
    3, 'STATUS_CHANGE', 'RUNNING', 'MAINTENANCE', 'Scheduled maintenance started'
);

CREATE VOLATILE TABLE active_alarms (
    alarm_id INTEGER PRIMARY KEY,
    line_id INTEGER,
    alarm_type VARCHAR(50),
    severity VARCHAR(20),
    message VARCHAR(500),
    triggered_at DATETIME
);

-- 알람 추가
INSERT INTO active_alarms VALUES (
    1001, 4, 'LOW_THROUGHPUT', 'WARNING', 'Rate below 80% of target', NOW
);
INSERT INTO active_alarms VALUES (
    1002, 2, 'TEMPERATURE_HIGH', 'WARNING', 'Temperature 5C above normal', NOW
);

-- 해결 시 알람 해제
DELETE FROM active_alarms WHERE alarm_id = 1001;

-- 활성 알람 쿼리
SELECT a.*, p.line_name
FROM active_alarms a
JOIN production_status p ON a.line_id = p.line_id
ORDER BY severity, triggered_at;

CREATE VOLATILE TABLE operator_sessions (
    session_id VARCHAR(50) PRIMARY KEY,
    operator_name VARCHAR(100),
    login_time DATETIME,
    assigned_lines VARCHAR(200),
    last_activity DATETIME
);

-- 운영자 로그인
INSERT INTO operator_sessions VALUES (
    'sess_12345', 'John Smith', NOW, '1,2,3', NOW
);

-- 활동 업데이트
UPDATE operator_sessions
SET last_activity = NOW
WHERE session_id = 'sess_12345';

-- 비활성 세션 제거 (30분 후 타임아웃)
DELETE FROM operator_sessions
WHERE last_activity < NOW - INTERVAL '30' MINUTE;

-- 현재 생산 요약
SELECT
    COUNT(*) as total_lines,
    SUM(CASE WHEN status = 'RUNNING' THEN 1 ELSE 0 END) as running,
    SUM(CASE WHEN status = 'IDLE' THEN 1 ELSE 0 END) as idle,
    SUM(CASE WHEN status = 'WARNING' THEN 1 ELSE 0 END) as warnings,
    SUM(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END) as errors,
    SUM(items_produced) as total_produced
FROM production_status;

-- 주의가 필요한 라인
SELECT
    p.line_id,
    p.line_name,
    p.status,
    COUNT(a.alarm_id) as active_alarms
FROM production_status p
LEFT JOIN active_alarms a ON p.line_id = a.line_id
WHERE p.status != 'RUNNING'
   OR a.alarm_id IS NOT NULL
GROUP BY p.line_id, p.line_name, p.status;

-- Volatile: 현재 장비 상태
CREATE VOLATILE TABLE equipment_status (
    equipment_id INTEGER PRIMARY KEY,
    temperature DOUBLE,
    vibration DOUBLE,
    status VARCHAR(20),
    last_reading DATETIME
);

-- Log: 과거 측정값
CREATE TABLE equipment_history (
    equipment_id INTEGER,
    temperature DOUBLE,
    vibration DOUBLE,
    status VARCHAR(20)
);

-- 현재 상태 업데이트 (빠름)
UPDATE equipment_status
SET temperature = 75.5,
    vibration = 0.2,
    last_reading = NOW
WHERE equipment_id = 101;

-- 매 분마다 과거 데이터로 보관
INSERT INTO equipment_history
SELECT equipment_id, temperature, vibration, status
FROM equipment_status;

CREATE VOLATILE TABLE config_cache (
    config_key VARCHAR(100) PRIMARY KEY,
    config_value VARCHAR(500),
    updated_at DATETIME
);

-- 설정 저장
INSERT INTO config_cache VALUES ('max_temp_threshold', '80.0', NOW);
INSERT INTO config_cache VALUES ('alert_email', 'ops@company.com', NOW);

-- 빠른 조회
SELECT config_value
FROM config_cache
WHERE config_key = 'max_temp_threshold';

-- 설정 업데이트
UPDATE config_cache
SET config_value = '85.0',
    updated_at = NOW
WHERE config_key = 'max_temp_threshold';

CREATE VOLATILE TABLE web_sessions (
    session_token VARCHAR(100) PRIMARY KEY,
    user_id INTEGER,
    ip_address IPV4,
    created_at DATETIME,
    expires_at DATETIME,
    last_activity DATETIME
);

-- 세션 생성
INSERT INTO web_sessions VALUES (
    'tok_abc123xyz', 12345, '192.168.1.100', NOW, NOW + INTERVAL '2' HOUR, NOW
);

-- 세션 검증
SELECT user_id
FROM web_sessions
WHERE session_token = 'tok_abc123xyz'
  AND expires_at > NOW;

-- 활동 업데이트
UPDATE web_sessions
SET last_activity = NOW
WHERE session_token = 'tok_abc123xyz';

-- 만료된 세션 정리
DELETE FROM web_sessions
WHERE expires_at < NOW;

-- 종료 전 중요한 데이터 보관
INSERT INTO production_events
SELECT line_id, 'SHUTDOWN', status, 'OFFLINE', 'Server shutdown'
FROM production_status
WHERE status = 'RUNNING';

-- volatile 테이블 크기 확인
SHOW TABLE production_status;
SHOW TABLE active_alarms;

-- 정리 정책 구현
DELETE FROM operator_sessions
WHERE last_activity < NOW - INTERVAL '1' HOUR;