主页 / dba / 问题 / 30485
Accepted
Kshitiz Sharma
Asked: 2012-12-16 00:13:40 +0800 CST

如何聚合表中的数据点？

  • 772

假设我有下表 -

CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER);

我想通过计算表中每 10 个点的平均值来汇总数据。

即如果表有 20 个数据点，结果是两个聚合点。第一个聚合点是 1-10 个数据点的平均值，第二个是 11-20 个数据点的平均值。

这可能使用 SQL 查询吗？

aggregate h2

1 个回答

  1. Best Answer

    由于您尚未披露您打算使用的 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 试试看。

    • 2

相关问题