sql - 当数据库时间片大于想要的时间片时，如何查询时间序列数据？

Question

当数据库时间片大于所需时间片时，如何从数据中查询时间片。最终结果将用于绘制堆积条形图。

示例数据:

START_TS (int)| END_TS (int) | DATA (int) | GROUP
-----------------------------------
0       | 179      | 2000  | G1
180     | 499      | 1000  | G2
500     | 699      | 1000  | G1
845 ...

使用时间片作为 100 个“单位”的想要的输出。输出中不需要 End_ts，但有助于理解计算。

START_TS |  END_TS  | DATA (equation = amount in that time slice) | GROUP
-------------------------------------------------------
0       |    99   | (2000 / 180) * 100 =  1111 | G1
100     |   199   | (2000 / 180) *  80 =   889 | G1
100     |   199   | (1000 / 320) *  20 =    63 | G2
200     |   299   | (1000 / 320) * 100 =   313 | G2 
300     |   399   | (1000 / 320) * 100 =   313 | G2
400     |   499   | (1000 / 320) * 100 =   313 | G2 

从中获取时间序列是这样的。

SELECT (startts/100)*100, ...
FROM TABLE
    FULL JOIN
        ( SELECT startts from generate_series(0,700,100) startts ) s1
    USING (startts)
GROUP BY  startts/100

所以它会是这样的(没有分组依据)

 STARTTS | ENDTS | DATA | GROUP
   0     | 179      | 2000   | G1
   100   |      
   180   | 499      | 1000   | G2
   200   |
   300   |
   400   | 
   500   | 699      | 1000   | G1
   600   |
   700

但是我如何将 DATA 拆分为两个或多个生成的行(时间片行)，以在时间片中计算。

---

** 这基本上是可行的，但在大数据集上并没有真正发挥作用。行，例如 1-100M 行。

这是执行此操作的查询 + 一些用于聚合不重叠时间片的值的查询

SELECT (start_ts/100)*100 as start_ts, sum(part) as data, cgroup
FROM (
SELECT *, ( data * (overlap_end-overlap_start + 1 ) / ( end_ts - tts + 1 ) ) as part
FROM 
    (
    SELECT (case when s1.start_ts > t.start_ts then s1.start_ts else t.start_ts end) as overlap_start,
        (case when s1.start_ts+100 < t.end_ts then s1.start_ts+100-1 else t.end_ts end) as overlap_end,
        t.start_ts as tts, s1.start_ts as start_ts, t.end_ts, cgroup, data
    FROM (SELECT start_ts from generate_series(0,800,100) start_ts ) s1 
        LEFT OUTER JOIN test t on t.start_ts < s1.start_ts+100 and t.end_ts >= s1.start_ts
    ) t
) t2
GROUP BY start_ts/100, cgroup

Answer 1

您需要的是将不同的时隙分成由序列定义的 bin。以下查询通过修改连接条件并计算两者之间的重叠来实现:

SELECT (startts/100)*100, ...
from (select (case when s1.starts > t.start_ts then s1.starts else t.start_t2 end) as overlap_start,
             (case when s1.starts+100 < t.end_ts then s1.starts+100-1 else t.end_ts end) as overlap_end,
             ts.*
      FROM (SELECT startts from generate_series(0,700,100) startts ) s1 left outer join
           TABLE t
           on t.startts < s1.starts+100 and
              t.end_ts >= s1.starts
     ) t