InfluxDB 聚合函數實用案例

InfluxDB 聚合函數實用案例

文章大綱

InfluxDB 簡介

InfluxDB是GO語言編寫的分散式時間序列化資料庫，非常適合對數據（跟隨時間變化而變化的數據）的跟蹤、監控和分析。在我們的項目中，主要是用來收集設備實時上傳的值。從而分析該設備值的趨勢圖和各個設備的能耗占比等一系列功能。InfluxDB的功能很強大，文檔也很詳細。可美中不足的是，它的單機性能並不是很理想。因為InfluxDB存儲的數據量本身是非常巨大的，在執行一些時間範圍比較大的sql語句，耗時會很長，甚至直接崩潰。而開源的InfluxDB目前已經不再支持集群。若要通過搭建集群提升性能問題，可以考慮企業版。當然，我們寫的程式也有很大的性能優化空間。

能耗趨勢圖分析

需求：統計指定設備、指定區域、指定分項或者指定能耗類型的能耗趨勢圖。如下圖所示，縱坐標是能耗值，橫坐標是時刻（每小時、每天、每周、每月）。

分析：獲取某個區間時刻的值，可以用GROUP BY time 進行時間分組。再用聚合函數LAST或者SUM統計。但這個看似很簡單的需求卻暗藏殺機。SQL語句如下

SELECT LAST("currentValue"), * FROM "$TABLE_NAME" 
WHERE time > '$startTime' AND time <= '$endTime' AND id = '$id' 
GROUP BY time($timeSpan) 
ORDER BY time DESC

第一：先要清楚，數據是通過什麼規則保存到InfluxDB資料庫

為了記錄設備能耗的實時數據，我們會通過訂閱MQTT通道，當值發生變化後存儲到InfluxDB資料庫中，或者在指定時間範圍內沒有變化也會上傳。這樣做的好處可以避免一些冗餘數據，同時也埋下了一個坑。

例如：一臺設備在InfluxDB資料庫中最後一次記錄的時間是15分鐘前。但是sql語句是從5分鐘前開始統計。這會導致該設備的其點值就是null。簡單來說：設備的存儲的值正好在分組統計的時間範圍外。解決方法有很多：比如用FILL(previous)函數填充；比如使用time(time_interval,offset_interval)進行時間推移等。但是我比較推薦下麵的方法：

先獲取指定開始時間之前的最後值（lastValue），然後再根據返回值是否為null，來決定是否替換或者更新lastValue。偽代碼如下。

## 獲取該設備的最後記錄值
val lastValue = "SELECT LAST("currentValue") FROM "$TABLE_NAME" WHERE time <= '$startTime'"
## 遍歷查詢結果，將currentValue為 null的值替換
"SELECT LAST("currentValue"), * FROM "$TABLE_NAME" 
WHERE time > '$startTime' AND time <= '$endTime' AND id = '$id' 
GROUP BY time($timeSpan) 
ORDER BY time DESC".forEach {
    lastValue = currentValue?: lastValue
    result[time] = currentValue?: lastValue
}

你以為這樣就結束了嗎？還不夠，返回的time格式化後，你會發現有8小時的時區問題。

第二：解決InfluxDB時區問題

InfluxDB 預設以UTC時間存儲並返回時間戳，查詢返回的時間戳對應的也是UTC時間。我們需要通過tz()子句指定時區名稱，比如Asia/Shanghai。若InfluxDB安裝在Windows環境上，可能還會出現 error parsing query: unable to find time zone 錯誤，解決方法是安裝GO語言環境，文章也詳細介紹過。

SELECT LAST("currentValue"), * FROM "$TABLE_NAME" 
WHERE time > '$startTime' AND time <= '$endTime' AND id = '$id' 
GROUP BY time($timeSpan) 
ORDER BY time DESC tz('Asia/Shanghai')

實用tz() 子句後，返回的時間格式："2019-11-18T13:50:00+08:00"。需要通過 "yyyy-MM-dd'T'HH:mm:ss" 將其格式化。

第三：GROUP BY time 自然月

group by time 支持秒、分鐘、小時、天和周，卻唯獨不支持自然月。如果對數據的精準性要求不高，可以考慮使用30d實現。或者分12次統計。或者有更好的方法，請不吝賜教