Aggregate

如何聚合表中的數據點?

  • December 20, 2017

假設我有下表 -

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 行是必要的。(如果這個表上有一個額外的唯一鍵,這將是不必要的。)在averagesCTE 中,我計算每一行的移動平均值。設置視窗以便目前行和前九行給出要平均的數字。最後,我只是簡單地每 10 行返回一次。

row_number()和都是這裡avg()視窗函式。MySQL 缺少此功能,據我所知,H2 也缺少此功能。因此,我認為將 abov 查詢移植到這些將涉及更多技巧。

在SQLFiddle嘗試一下。

引用自:https://dba.stackexchange.com/questions/30485