给定时间间隔内的汇总函数

一尘不染

给定时间间隔内的汇总函数

sql

我的SQL有点生锈，我在解决这个问题上遇到了很多困难。假设我有一个带有Timestamp列和Number列的表。目的是返回一个结果集，该结果集包含某个任意选择的规则间隔的平均值。

因此，例如，如果我有以下初始数据，则以5分钟为间隔的结果输出如下：

time                               value
-------------------------------    -----
06-JUN-12 12.40.00.000000000 PM      2
06-JUN-12 12.41.35.000000000 PM      3
06-JUN-12 12.43.22.000000000 PM      4
06-JUN-12 12.47.55.000000000 PM      5
06-JUN-12 12.52.00.000000000 PM      2
06-JUN-12 12.54.59.000000000 PM      3
06-JUN-12 12.56.01.000000000 PM      4

OUTPUT:

start_time                         avg_value
-------------------------------    ---------
06-JUN-12 12.40.00.000000000 PM      3
06-JUN-12 12.45.00.000000000 PM      5
06-JUN-12 12.50.00.000000000 PM      2.5
06-JUN-12 12.55.00.000000000 PM      4

请注意，这是一个Oracle数据库，因此特定于Oracle的解决方案可以正常工作。当然，这可以通过存储过程来完成，但是我希望在单个查询中完成任务。

阅读 149

2021-03-17

共1个答案

一尘不染

CREATE TABLE tt (time TIMESTAMP, value NUMBER);

INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.40.00.000000000 PM', 2);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.41.35.000000000 PM', 3);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.43.22.000000000 PM', 4);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.47.55.000000000 PM', 5);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.52.00.000000000 PM', 2);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.54.59.000000000 PM', 3);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.56.01.000000000 PM', 4);


WITH tmin AS (
    SELECT MIN(time) t FROM tt
),   tmax AS (
    SELECT MAX(time) t FROM tt
)
SELECT ranges.inf, ranges.sup, AVG(tt.value)
FROM
     (
        SELECT 
            5*(level-1)*(1/24/60) + tmin.t as inf,
            5*(level)*(1/24/60) + tmin.t as sup
        FROM tmin, tmax
        CONNECT BY (5*(level-1)*(1/24/60) + tmin.t) < tmax.t
    ) ranges JOIN tt ON tt.time BETWEEN ranges.inf AND ranges.sup
GROUP BY ranges.inf, ranges.sup
ORDER BY ranges.inf

小提琴：http ://sqlfiddle.com/#!4/
9e314/11

编辑：像往常一样被贾斯汀殴打… :-)

2021-03-17