用 SQL sensor 直接查資料庫，做出內建沒有的統計

charles

HA 內建的感測器都是「現在的值」。
但有些東西你想知道的是「累積」或「歷史」，例如：

• 這個月冷氣開了幾次
  
• 洗衣機上次跑完是什麼時候
  
• 某個感應器今天觸發幾次
  
• 資料庫現在多大
  

這些內建都沒有。SQL sensor 可以自己做。



先裝整合

設定 → 裝置與服務 → 新增整合 → 搜尋 SQL

它可以用 UI 設定，也可以寫 YAML。我習慣寫 YAML 因為好備份、好複製。

第一個：資料庫大小

這個最實用，可以做成通知「資料庫超過 1GB 了」。

1. sql:
2.   - name: 資料庫大小
3.     unique_id: ha_db_size
4.     query: >
5.       SELECT ROUND(page_count * page_size / 1024.0 / 1024.0, 1) AS size_mb
6.       FROM pragma_page_count(), pragma_page_size();
7.     column: size_mb
8.     unit_of_measurement: MB
9.     state_class: measurement

複製代碼

三個必填欄位：

• query — SQL 語句，只能是 SELECT（HA 會擋掉寫入類的語句）
  
• column — 要取查詢結果的哪一欄
  
• name — 感測器名稱
  

重點：query 一定要只回傳一列。
回傳多列的話 HA 只取第一列，結果會很奇怪。



第二個：某個實體今天觸發幾次

1.   - name: 前門今日開啟次數
2.     unique_id: front_door_open_today
3.     query: >
4.       SELECT COUNT(*) AS cnt
5.       FROM states s
6.       JOIN states_meta sm ON s.metadata_id = sm.metadata_id
7.       WHERE sm.entity_id = 'binary_sensor.front_door'
8.         AND s.state = 'on'
9.         AND s.last_updated_ts >= strftime('%s', 'now', 'start of day', 'localtime');
10.     column: cnt
11.     unit_of_measurement: 次
12.     state_class: total

複製代碼

幾個細節：

• 新版資料表把 entity_id 移到 states_meta，要 JOIN 才拿得到
  
• 時間欄位是 last_updated_ts（Unix 秒數），不是舊的 last_updated
  
• localtime 不能省，不然「今天」會用 UTC 算，台灣會差 8 小時
  

第三個：某個裝置上次動作是多久以前

1.   - name: 洗衣機上次完成
2.     unique_id: washer_last_done
3.     query: >
4.       SELECT datetime(MAX(s.last_updated_ts), 'unixepoch', 'localtime') AS t
5.       FROM states s
6.       JOIN states_meta sm ON s.metadata_id = sm.metadata_id
7.       WHERE sm.entity_id = 'binary_sensor.washer_running'
8.         AND s.state = 'off';
9.     column: t

複製代碼

第四個：這個月的累計用電

這個要查長期統計表，不是 states：

1.   - name: 本月用電
2.     unique_id: energy_this_month
3.     query: >
4.       SELECT ROUND(MAX(st.state) - MIN(st.state), 2) AS kwh
5.       FROM statistics st
6.       JOIN statistics_meta sm ON st.metadata_id = sm.id
7.       WHERE sm.statistic_id = 'sensor.total_energy'
8.         AND st.start_ts >= strftime('%s', 'now', 'start of month', 'localtime');
9.     column: kwh
10.     unit_of_measurement: kWh
11.     device_class: energy
12.     state_class: total

複製代碼

statistics 表才是長期資料的家，
而且它不受 purge_keep_days 影響，可以查很久以前。

怎麼先試 SQL 再寫進設定

不要直接寫 YAML 然後重啟 —— 錯了你只會看到一個 unavailable 的實體。

裝 SQLite Web 附加元件，在裡面把語句跑過一次，確認：

• 有沒有語法錯誤
  
• 回傳幾列（必須是 1）
  
• 欄位名稱跟 column 設定一不一致
  

確認沒問題再貼進 configuration.yaml。

效能警告

SQL sensor 預設每 30 秒查一次。
如果你的查詢要掃整張 states 表，資料庫又有 2GB，
那 HA 每 30 秒就卡一下。

兩個對策：

• 加 scan_interval 拉長間隔（統計類的東西，一小時查一次就夠）
  
• 查詢加上時間範圍條件，不要全表掃描
  

1.   - name: 本月用電
2.     scan_interval: 3600        # 一小時一次
3.     query: ...

複製代碼

換成 MariaDB 的人要注意

上面的語句是 SQLite 寫法。用 MariaDB/MySQL 的話：

• strftime('%s', ...) → UNIX_TIMESTAMP(...)
  
• datetime(x, 'unixepoch') → FROM_UNIXTIME(x)
  
• 資料庫大小要查 information_schema
  

安全提醒

SQL sensor 用的是 HA 自己的資料庫連線，權限很大。
雖然 HA 會擋掉非 SELECT 的語句，但不要從網路上複製看不懂的 SQL 貼進去。

---

我自己最常用的是「資料庫大小」和「裝置今日觸發次數」。
後者拿來查「這個感應器是不是壞了一直誤觸發」特別好用 ——
正常一天二十次的東西突然變兩千次，就知道要去看了。