SQL时间序列组具有最大结果量

Question

我使用TimeScaledB在表中有时间表数据。

数据如下：

    time                    locationid parameterid unitid value
2022-04-18T10:00:00.000Z      "1"        "1"        "2"    2.2
2022-04-18T10:00:00.000Z      "2"        "1"        "2"    3.0
2022-04-18T09:00:00.000Z      "1"        "1"        "2"    1.2
2022-04-18T09:00:00.000Z      "2"        "1"        "2"    4.0
2022-04-18T08:00:00.000Z      "1"        "1"        "2"    2.6
2022-04-18T08:00:00.000Z      "2"        "1"        "2"    3.1
2022-04-18T07:00:00.000Z      "1"        "1"        "2"    2.1
2022-04-18T07:00:00.000Z      "2"        "1"        "2"    2.7

我有1000秒的行，并使用HighCharts将其添加到图形中。

我的问题是，有没有一种方法可以限制结果中返回的项目数量，但不是经典限制。我想始终返回256个数据组。因此，如果我有2,560行，我的查询将按 / date trunc / time_bucket每100行分组，但是如果我有512行，我的查询只会每2行分组，以便无论如何我总是返回256。

我当前的查询：

SELECT time_bucket('4 hours', time) as "t"
,locationid, avg(timestamp) as "x", avg(value) as "y"
FROM probe_data
WHERE locationid = '${q.locationid}'and parameterid = '${q.parameterid}' 
and time > '${q.startDate}' and time < `${q.endDate}`
GROUP BY "t", locationid
ORDER BY "t" DESC;

似乎我应该能够使用最小日期和最大日期来计算可能的退货数量，然后除以256吗？这是最好的方法吗？

Answer 1

您可以通过几种不同的方式来执行此类操作：

1. 您可以使用time_bucket在查询中动态使用的时间存储桶。您可以在间隔上进行算术，并获得另一个间隔IE select（现在（） - '2022-04-21'）/256;将返回一个间隔，这可以是第一个输入到time_bucket 。因此，诸如此类的事情

SELECT time_bucket((enddate - startdate) / 256, time) as "t" 
...
GROUP BY time_bucket((enddate - startdate) / 256, time)

应该在很大程度上做您要寻找的事情...

但是，这确实意味着您将在缩小时获得任意更大的数据组的平均值，并且不允许您可怕缓存事物之类的东西，实际上并不是基础过程的很好的代表。

另一个选择是：

2. 您可以在与数据分析相关的设置时间_ bucket上进行平均值，然后下样本，然后使用像最大三角形这样的算法三个桶，该算法以有用的方式保持图形的视觉准确性，同时减少数据采样。 它是我们在TimesCaledB 。

这将使您还可以使用连续聚集用SET time_bucket对数据进行对示例，然后使用LTTB算法更快地获取图形所需的点数。

因此，这取决于您要寻找的东西...在某些情况下，使用LTTB单独使用LTTB，甚至不使用平均值，甚至使用 ASAP平滑（另一个实验性超功能）可能是一种更好的方法来完成您想要的工作，并且是为此类型的工作而内置的！我认为这些文档页面具有有关算法的更多信息以及它们有用的信息，但是LTTB和ASAP都是专门用于绘制应用程序的，因此我想我会指出它们！

Answer 2

否 - SQL不支持动态分组。要做您的要求，您必须获取结果集＆amp;检查返回的记录数，然后再次使用您的逻辑运行。

实际上，您有一个演示级问题 - 不是SQL问题。

Answer 3

可能有 > ，不确定这是否有效，但我想这样做这样的事情：

SELECT avg(sub.timestamp), avg(sub.value) FROM (
    SELECT 
        timestamp,
        value,
        NTILE (256) OVER (
            ORDER BY time
       ) bucket_no
    FROM 
        probe_data
) sub GROUP BY sub.bucket_no;

内部查询将所有数据分为256个组，然后每个组都有一个列buck> bucket_no < /code>，并且您的外部查询将按bucket_no

编辑：刚刚实现mysql在此问题上标记可能是不准确的，但是我将离开答案。可能会指向时间标度的正确方向。