📊 Grafana × MySQL 샘플 쿼리

Grafana에서 MySQL Data Source 연결 후 아래 쿼리를 패널에 붙여넣으세요.
Time series 패널에서는 첫 번째 컬럼이 DATETIME, 나머지가 수치여야 합니다.

① 센서 — 실시간 온도/압력 시계열

mfg_sensor.values_json 에서 특정 센서값을 추출하는 방법

SELECT
    ts                                                   -- Grafana Time 컬럼
  , JSON_EXTRACT(values_json, '$.temperature_c')  AS temperature_c
  , JSON_EXTRACT(values_json, '$.pressure_bar')   AS pressure_bar
  , JSON_EXTRACT(values_json, '$.vibration_mm_s') AS vibration_mm_s
FROM  mfg_sensor
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
  AND  factory_id = 'F01'           -- 공장 필터 (선택)
ORDER BY ts

② 센서 — 공정 상태별 건수 (Pie Chart)

SELECT
    process_status             AS status
  , COUNT(*)                   AS count
FROM  mfg_sensor
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY process_status

③ 센서 — 1분 평균 집계 (Time series)

SELECT
    DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00')          AS ts
  , AVG(JSON_EXTRACT(values_json, '$.temperature_c')) AS avg_temp
  , AVG(JSON_EXTRACT(values_json, '$.pressure_bar'))  AS avg_pressure
FROM  mfg_sensor
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00')
ORDER BY ts

④ 알람 — 심각도별 시계열 (알람 발생 추이)

SELECT
    ts
  , CASE severity
      WHEN 'CRITICAL' THEN 3
      WHEN 'WARNING'  THEN 2
      ELSE               1
    END                  AS severity_level
  , value
FROM  mfg_alarm
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
ORDER BY ts

⑤ 알람 — 유형별 건수 (Bar chart)

SELECT
    alarm_type
  , COUNT(*)  AS cnt
  , SUM(CASE WHEN severity = 'CRITICAL' THEN 1 ELSE 0 END) AS critical
FROM  mfg_alarm
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY alarm_type
ORDER BY cnt DESC

⑥ 품질 — OK/NG 불량률 (1분 집계)

SELECT
    DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00')          AS ts
  , ROUND(AVG(CASE WHEN result = 'NG' THEN 100.0 ELSE 0 END), 2) AS defect_rate_pct
  , COUNT(*)                                          AS total
FROM  mfg_quality
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00')
ORDER BY ts

⑦ 에너지 — 전력/전압 시계열

SELECT
    ts
  , power_kw
  , voltage_v
  , pf
  , frequency_hz
FROM  mfg_energy
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
ORDER BY ts

⑧ 에너지 — 미터별 평균 전력 (Stat 패널)

SELECT
    meter_id
  , ROUND(AVG(power_kw), 2) AS avg_power_kw
  , ROUND(MAX(power_kw), 2) AS max_power_kw
FROM  mfg_energy
WHERE ts BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY meter_id

---

Grafana 설정 팁:
• Time series 패널: 첫 컬럼을 $__timeFrom()/$__timeTo() 필터 사용
• Grafana의 $__timeGroup(ts, '1m') 매크로로 자동 집계 가능
• 변수(Variable) 기능으로 factory_id, equipment_id를 드롭다운으로 만들 수 있음