如何设置一个最大范围条件与时间尺度time_bucket_gapfill()，以不填补真正的缺失值?

Question

我想知道一些建议，如果我需要做的是可以实现的时间刻度函数。

我刚刚发现我可以使用time_bucket_gapfill()来完成丢失的数据，这是惊人的!我需要每5分钟的数据，但我可以收到10分钟，30分钟或1小时的数据。所以这个函数帮助我补全了缺失的点，以便只有5分钟的点。此外，我使用locf()设置空白填充值与最后找到的值。

我的问题是:当我设置locf()的最后一个值时，我可以设置一个最大范围，以便永远不超过1小时吗?

示例:如果发现的最后一个值大于1小时，我不想填充空白，我需要将其保留为空，以说明我们在这里有真正的缺失值。

我想我接近这个东西，但显然我不允许在相同的情况下使用locf()。

错误:不支持对每个结果集列调用多个interpolate/locf函数

有人知道我该如何解决这个问题吗?

如何复制:

1. Create table powers

CREATE table powers (
delivery_point_id BIGINT NOT NULL,
at timestamp NOT NULL,
value BIGINT NOT NULL
);

2. 创建超表

SELECT create_hypertable('powers', 'at');

3. 创建索引

CREATE UNIQUE INDEX idx_dpid_at ON powers(delivery_point_id, at);
CREATE INDEX index_at ON powers(at);

4. 插入数据1天，1个交付点，点10分钟

INSERT INTO powers SELECT 1, at, round(random()*10000) FROM generate_series(TIMESTAMP '2021-01-01 00:00:00', TIMESTAMP '2022-01-02 00:00:00', INTERVAL '10 minutes') AS at;

5. 从凌晨4点到早上7点删除三个小时的数据

DELETE FROM powers WHERE delivery_point_id = 1  AND at < '2021-01-1 07:00:00'  AND at > '2021-01-01 04:00:00';

6. 需要修复的查询

SELECT
time_bucket_gapfill('5 minutes', at) AS point_five,
avg(value) AS avg,
CASE
WHEN (locf(at) - at) > interval '1 hour' THEN null
ELSE locf(avg(value))
END AS gapfilled
FROM powers
GROUP BY point_five, at
ORDER BY point_five;

实际:ERROR: multiple interpolate/locf function calls per resultset column not supported

预期:除了凌晨4点到7点之间，每5分钟补一次值(真正的缺失值)。

Answer 1

这是一个很好的问题!我将为如何使用当前的东西做这件事提供一个解决方案，但我认为如果你也打开一个Github问题，那就太好了，因为可能有一种方法可以为此添加一个选项，而不需要像这样的解决方案。

我也认为你的尝试是一个很好的方法，只是需要一些调整才能正确!

你看到的错误是我们不能在一个列中有多个locf调用，这是一个很容易解决的限制，因为我们可以将它们都转移到子查询中，但这还不够。另一件事，我们需要改变的是，locf只适用于聚合，现在，你试图使用它的列(at)没有聚合，这是不会工作的，因为它不知道哪的at的值在time_bucket"向前拉"的gapfill。

现在你说你想要填充数据，只要前一个点不超过一个小时，所以，我们可以通过使用last(at, at)来获取at在bucket中的last值，这也是max(at)，所以这些聚合都可以工作。因此我们将其放入CTE(公共表表达式或WITH查询)中然后在外部执行case语句，如

WITH filled as (SELECT
time_bucket_gapfill('5 minutes', at) AS point_five,
avg(value) AS avg,
locf(last(at, at)) as filled_from,
locf(avg(value)) as filled_avg
FROM powers
WHERE at BETWEEN '2021-01-01 01:30:00' AND '2021-01-01 08:30:00'
AND delivery_point_id = 1
GROUP BY point_five
ORDER BY point_five)
SELECT point_five,
avg,
filled_from,
CASE WHEN point_five - filled_from > '1 hour'::interval THEN NULL
ELSE filled_avg
END as gapfilled
FROM filled;

请注意，我已经尝试了表达性地命名我的CTE，以便它更容易阅读!

同时，我想指出一些你可能会考虑使用的其他超函数:

1. heartbeat_agg是一个新的/实验性的，它将帮助您确定系统上升或下降的时间段，因此，如果您至少每小时期望一个点，您可以使用它来查找交付点下降或类似的时间段。

2. 当你有更多的不规则采样或想要处理来自不同交付点的不同数据频率时，我会看看time_weight函数族。它们可以比使用像gap填充之类的方法来上样更有效，而不是让你类似地对待所有不同的采样率，而不必创建更多的点和更多的工作来这样做。例如，即使你想比较值的总和，你也可以使用integral之类的东西来获得基于locf插值的一段时间内的时间加权和。

无论如何，希望所有这些都是有帮助的!