给定时间间隔内的汇总函数 [英] Aggregate function over a given time interval
问题描述
我的SQL有点生锈,我在解决这个问题上遇到了很多困难.假设我有一个带有Timestamp列和Number列的表.目的是返回一个结果集,该结果集包含某个任意选择的规则间隔的平均值.
My SQL is a bit rusty and I'm having quite a bit of difficulty with this problem. Suppose I have a table with a Timestamp column and a Number column. The goal is to return a result set containing the average value for some arbitrarily chosen regular interval.
例如,如果我有以下初始数据,则以5分钟为间隔的结果输出如下:
So, for example, if I had the following initial data, the resulting output with a 5 minute interval would be as follows:
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的解决方案可以正常工作.当然,这可以通过存储过程来完成,但是我希望在单个查询中完成任务.
Note that this is an Oracle database, so Oracle-specific solutions would work fine. This could, of course, be done with a stored procedure but I was hoping to accomplish the task in a single query.
推荐答案
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
像往常一样被贾斯汀殴打...:-)
edit: beated by Justin, as usual... :-)
这篇关于给定时间间隔内的汇总函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!