Aggregate Function Over a Given Time Interval

Aggregate function over a given time interval

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

fiddle: http://sqlfiddle.com/#!4/9e314/11

edit: beated by Justin, as usual... :-)

Aggregate my SQL results based on time interval?

If the splits don't matter, don't group by them and definitely don't group by the measure you want to aggregate on, just choose the aggregating function. I don't really know what acdcalls represent but if it's the number of calls via automated call distribution then you probably need a SUM:

DECLARE @searchdate date = '20220217'
SELECT [row_date]  
  ,[starttime]  
  ,SUM([acdcalls]) AS acdcalls
FROM [CMS].[dbo].[hsplit]  
WHERE split IN (100,101,102,103,104,105,106,107)  
AND (acdcalls > 0)  
AND (row_date = @searchdate)  
GROUP BY row_date, starttime
ORDER BY row_date, starttime asc ;

Aggregate function over a given time interval spark

Generally you can extract the 5 minutes bucket from each time (e.g. by getting the timestamp as a number, dividing by 5 minutes and flooring the result).

Then you simply do:

df.groupBy("bucket").avg($"value")

SQL: Apply an aggregate result per day using window functions

Consider calculating running total via window function after aggregating data to day level. And since you aggregate with a single condition, FILTER condition can be converted to basic WHERE:

SELECT daily,
       SUM(total_balance) OVER (ORDER BY daily) AS total_value_per_day
FROM (
    SELECT
        DATE_TRUNC('DAY', (time)) AS daily,
        SUM(balance) AS total_balance
    FROM tbl
    WHERE is_spent_column IS NULL
    GROUP BY 1
) AS daily_agg
ORDER BY daily

aggregating Data by Time Interval in Oracle SQL

Your case is just lingering after your query. The easiest way to write this is with a subquery:

select t.*,
       (Case When (Minutes>5 And Minutes<=15) 
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':15', 'dd-mon-yy hh24:mi')
             When (Minutes>20 And Minutes<=30)
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':30', 'dd-mon-yy hh24:mi')
             When (Minutes>35 And Minutes<=45)
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':45', 'dd-mon-yy hh24:mi')
             When (Minutes>50 And Minutes<=60)
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':60', 'dd-mon-yy hh24:mi')
       End)
from (Select Temperature, Extract(Minute From Reading_Time) As Minutes,
             Extract(Hour From Reading_Time) As Hours, Cast(Reading_Time As Date) As Calendar_Date
      From Weather_Data
      Where Weather_Station = 'BDX'
     ) t 

I don't fully understand your logic, because you have gaps. I would simply write:

       (Case When Minutes <= 15
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':15', 'dd-mon-yy hh24:mi')
             When Minutes <= 30
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':30', 'dd-mon-yy hh24:mi')
             When Minutes <= 45
             Then To_Timestamp(Calendar_Date &' '&'0'&Hours&':45', 'dd-mon-yy hh24:mi')
             else To_Timestamp(Calendar_Date &' '&'0'&Hours&':60', 'dd-mon-yy hh24:mi')
       End)

---

Related Topics