Skip to Main Content
  • Questions

  • Average number of rows over a time period

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: November 17, 2022 - 7:16 pm UTC

Last updated: December 05, 2022 - 5:40 am UTC

Version: 19C

Viewed 1000+ times

You Asked

I find myself needing to generate a data set where I get the average number of rows over a time period. This period needs to be daily, by hour, for the same period for previous days. As an example, get the average number records from 1 am – 2 am for the last 180 days. In effect count the number of rows on 17 Nov 2022 between 1 am and 2 am, then count the number for rows on 16 Nov between 1 am and 2 am, then count the number of rows on 15 Nov between 1 am and 2 am, etc. Putting the date and time in the WHERE clause is easy enough but is manual. I would like to dynamically create these values for the WHERE clause based on a start date and time passed to the query. Therefore, if the start date and time passed in is 11-17-2022 01:00, an hour is added to get the end hour and then subtract 1 day for the past 180 days to get the average row count for the date range.

Is there some function(s) within Oracle that I could use to dynamically create the date and times based on the start date time?

Update 23 Nov 2022

Based on some research I've been doing, here is some code I have come up with so far. I thought it best to put this in a procedure.

CREATE OR REPLACE PROCEDURE TestAvgATMTransactions
(
 DaysToLookBack INT,
 CurrentStartDateTimeInput TIMESTAMP,
 CurrentEndDateTimeInput TIMESTAMP,
 PreviousStartDateTimeInput TIMESTAMP,
 PreviousEndDateTimeInput TIMESTAMP,
 RTXNTYPCDToCount VARCHAR2(4 BYTE)
)

IS

BEGIN

 DECLARE
  Low_ATM_Tran_Count_PWTH EXCEPTION,
  
  TYPE two_cols_rt IS RECORD
   (
     PreviousStartDateTime TIMESTAMP,
     PreviousEndDateTime   TIMESTAMP
   );
   
 TYPE DateTimesToQuery IS TABLE OF two_cols_rt;
 StartEndDateTime DateTimesToQuery;
 
 PRAGMA EXCEPTION_INIT(Low_ATM_Tran_Count_PWTH, -20001);
 PWTHCount RTXN.RTXNNBR%TYPE;-- the average transactions during the given period
 AvgTransactions INT; -- the current average transactions

 BEGIN
 
-- This does generate the days given how far I want to look back
-- however, it does not include the time for some reason
-- Need to figure out why as the time is crucial
-- (Chris Saxon - https://livesql.oracle.com/apex/livesql/file/content_LIHBDFVF9IUU6AFZB4H6NVLWL.html)
  SELECT TO_TIMESTAMP (PreviousStartDateTimeInput, 'mm-dd-yyyy hh24:mi') + LEVEL - 1,-- AS StartDateTime,
         TO_TIMESTAMP (PreviousEndDateTimeInput, 'mm-dd-yyyy hh24:mi') + LEVEL - 1-- AS EndDateTime
         BULK COLLECT INTO StartEndDateTime
  FROM DUAL
  CONNECT BY LEVEL < DaysToLookBack;
  DBMS_OUTPUT.PUT_LINE (StartEndDateTime.COUNT);
  
 END;

-- Based on the article Bulk data processing with BULK COLLECT and FORALL in PL/SQL
-- by Steven Feuerstein (https://blogs.oracle.com/connect/post/bulk-processing-with-bulk-collect-and-forall)
-- FORALL seemes like it would do what I want, but not sure
 FORALL index IN 1 .. StartEndDateTime.COUNT

-- This code needs to get the average for the given RTXNTYPCD for every day
-- in my two_cols_rt record
-- Yes needs work as this will not give me the overal average of transactions
 
  SELECT COUNT(RTXNSOURCECD)
  INTO PWTHCount
  FROM RTXN
  WHERE datelastmaint BETWEEN PreviousStartDateTimeInput AND PreviousEndDateTimeInput
  AND RTXNSOURCECD = 'ATM'
  AND RTXNTYPCD RTXNTYPCDToCount;

-- end FORALL

-- This gets the current count for the given RTXNTYPCD
  SELECT COUNT(RTXNSOURCECD)
  INTO AvgTransactions
  FROM RTXN
  WHERE datelastmaint BETWEEN CurrentStartDateTimeInput AND CurrentEndDateTimeInput
  AND RTXNSOURCECD = 'ATM'
  AND RTXNTYPCD = RTXNTYPCDToCount;

-- If the current count for the given RTXNTYPCD
-- is less than the average for the period raise the error
  IF AvgTransactions < PWTHCount
   THEN RAISE Low_ATM_Tran_Count_PWTH;
  END IF

END;


Thank you

Michael

and Connor said...

I am going to suggest a different approach. Here's my demo table

SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL>
SQL> select
  2    min(created),
  3    max(created)
  4  from t;

MIN(CREAT MAX(CREAT
--------- ---------
30-MAY-19 28-NOV-22

SQL>
SQL> select to_char(created,'HH24'), count(*)
  2  from t
  3  group by to_char(created,'HH24')
  4  order by to_number(to_char(created,'HH24'));

TO   COUNT(*)
-- ----------
00          9
02         69
03      18197
04      46309
05       4991
06         52
07        321
08        224
09        111
10        949
11        689
12        856
13       3673
14       2177
15       1158
16         48
17         41
18        187
19        613
20         56
21        102
22        116
23         72

23 rows selected.


So you can see I've got plenty of dates, and plenty of stuff created on certain hours of the day

Let's say I want to report for everything from June 1 to July 1 for 8am to 9am.

So first I'll need list of all the days in my date range

SQL>
SQL> select date '2022-06-01' + level - 1 dte
  2  from   dual
  3  connect by date '2022-06-01' + level - 1 <= date '2022-07-01';

DTE
---------
01-JUN-22
02-JUN-22
03-JUN-22
04-JUN-22
05-JUN-22
06-JUN-22
07-JUN-22
08-JUN-22
09-JUN-22
10-JUN-22
11-JUN-22
12-JUN-22
13-JUN-22
14-JUN-22
15-JUN-22
16-JUN-22
17-JUN-22
18-JUN-22
19-JUN-22
20-JUN-22
21-JUN-22
22-JUN-22
23-JUN-22
24-JUN-22
25-JUN-22
26-JUN-22
27-JUN-22
28-JUN-22
29-JUN-22
30-JUN-22
01-JUL-22

31 rows selected.


Now I also need the real data, ie, for each day, how much stuff did I do between 8am and 9am. Rather than a query for every day (expensive) I'll scan the entire range, but only count the rows when they fall into the 8-9am timeslot.

SQL>
SQL> select
  2    trunc(created) dy,
  3    count(case when to_char(created,'HH24') = '08' then 1 end) in_this_hr
  4  from t
  5  where created >= date '2022-06-01'
  6  and   created < date '2022-07-02'
  7  group by trunc(created)
  8  order by 1;

DY        IN_THIS_HR
--------- ----------
01-JUN-22          7
03-JUN-22          0
08-JUN-22          2
09-JUN-22          0
13-JUN-22          0


Now I can just bring those together with an outer join and we're done

SQL>
SQL> with
  2  all_the_days as
  3  ( select date '2022-06-01' + level - 1 dte
  4    from   dual
  5    connect by date '2022-06-01' + level - 1 <= date '2022-07-01'
  6  )
  7  select *
  8  from  all_the_days;

DTE
---------
01-JUN-22
02-JUN-22
03-JUN-22
04-JUN-22
05-JUN-22
06-JUN-22
07-JUN-22
08-JUN-22
09-JUN-22
10-JUN-22
11-JUN-22
12-JUN-22
13-JUN-22
14-JUN-22
15-JUN-22
16-JUN-22
17-JUN-22
18-JUN-22
19-JUN-22
20-JUN-22
21-JUN-22
22-JUN-22
23-JUN-22
24-JUN-22
25-JUN-22
26-JUN-22
27-JUN-22
28-JUN-22
29-JUN-22
30-JUN-22
01-JUL-22

31 rows selected.

SQL>
SQL>
SQL> with
  2  all_the_days as
  3  ( select date '2022-06-01' + level - 1 dte
  4    from   dual
  5    connect by date '2022-06-01' + level - 1 <= date '2022-07-01'
  6  ),
  7  daily_summary as
  8  ( select
  9      trunc(created) dy,
 10      count(case when to_char(created,'HH24') = '08' then 1 end) in_this_hr
 11    from t
 12    where created >= date '2022-06-01'
 13    and   created < date '2022-07-02'
 14    group by trunc(created)
 15  )
 16  select *
 17  from  all_the_days a left outer join daily_summary d
 18  on a.dte = d.dy
 19  order by a.dte;

DTE       DY        IN_THIS_HR
--------- --------- ----------
01-JUN-22 01-JUN-22          7
02-JUN-22
03-JUN-22 03-JUN-22          0
04-JUN-22
05-JUN-22
06-JUN-22
07-JUN-22
08-JUN-22 08-JUN-22          2
09-JUN-22 09-JUN-22          0
10-JUN-22
11-JUN-22
12-JUN-22
13-JUN-22 13-JUN-22          0
14-JUN-22
15-JUN-22
16-JUN-22
17-JUN-22
18-JUN-22
19-JUN-22
20-JUN-22
21-JUN-22
22-JUN-22
23-JUN-22
24-JUN-22
25-JUN-22
26-JUN-22
27-JUN-22
28-JUN-22
29-JUN-22
30-JUN-22
01-JUL-22

31 rows selected.

SQL>
SQL>

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Michael, November 29, 2022 - 5:27 pm UTC

Hi Connor,

Thank you for the response. Interesting approach. I'll look at this today and provide an update on how things go.

Update

Michael, December 02, 2022 - 3:53 pm UTC

Hi Connor,

Thanks again for the help. Your advice and example were spot on and what I needed. I'm currently in the testing phase and getting expected results.

Michael
Connor McDonald
December 05, 2022 - 5:40 am UTC

glad it worked out
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.