Aggregate
如何聚合表中的數據點?
假設我有下表 -
CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER);
我想通過計算表中每 10 個點的平均值來聚合數據。
即如果表有 20 個數據點,則結果是兩個聚合點。第一個聚合點是 1-10 個數據點的平均值,第二個是 11-20 個數據點的平均值。
這可能使用 SQL 查詢嗎?
由於您尚未披露您打算使用的 RDBMS(在撰寫本文時),我覺得有權發布特定於 PostgreSQL 的解決方案:
WITH rownums AS ( SELECT *, row_number() OVER (ORDER BY col1) AS rownum FROM avg_test ), averages AS ( SELECT avg(col2) OVER (ORDER BY rownum ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS average, rownum FROM rownums ) SELECT average FROM averages WHERE rownum % 10 = 0 ;
在第一個 CTE(稱為
rownums
)中,我只是根據時間戳的順序添加了行號。這對於以後能夠找到每 10 行是必要的。(如果這個表上有一個額外的唯一鍵,這將是不必要的。)在averages
CTE 中,我計算每一行的移動平均值。設置視窗以便目前行和前九行給出要平均的數字。最後,我只是簡單地每 10 行返回一次。
row_number()
和都是這裡avg()
的視窗函式。MySQL 缺少此功能,據我所知,H2 也缺少此功能。因此,我認為將 abov 查詢移植到這些將涉及更多技巧。在SQLFiddle嘗試一下。