I need to count the average of each day's records and size in MB for each file created in a day-CodePudding

I ask for your help after several unsuccessful attempts.

I am learning with PL SQL. I am using Oracle SQL developer v.20

I have this situation. My data set looks like this:

id_file     size_byte   created_at
_________   _________   ____________________________
1               45323   17-FEB-22 17:21:13,726874000
2               41232   17-FEB-22 17:21:13,740587004
3             1234456   20-FEB-22 17:25:13,368874058
4           233545488   20-FEB-22 17:21:18,400049000
5           233545488   21-FEB-22 18:11:18,058746868

So my desired output would be something like this for year 2022:


TOT_records     AVG_file_created_for_day     TOT_size_files     AVG_size_files_created_each_day
___________     ________________________     ______________     _______________________________
9.999.999       10.000                       999.999.999        5 MB (default is byte)

ID is type NUMBER, SIZE_BYTE is type NUMBER, CREATED_AT is TIMESTAMP(6)

My table is partitioned for each year, PARTITION_DATE is type DATE

CodePudding user response：

There's some ambiguity on things like "average file size per day"... That could be:

sum all file sizes / total number of days, or
average of files size per day, then take average of that average

Anyway, here's some stuff to get you going (I'm assuming the latter above)

SQL> create table t as
  2  select
  3    rownum id_file,
  4    dbms_random.value(1000,20000000) bytes,
  5    date '2021-01-01'   dbms_random.value(1,700) created_at
  6  from dual
  7  connect by level <= 5000;

Table created.

SQL>
SQL> select * from t
  2  where rownum <= 20;

   ID_FILE      BYTES CREATED_A
---------- ---------- ---------
         1 19305636.7 02-SEP-22
         2 6305773.83 10-OCT-21
         3 11939117.8 04-NOV-21
         4 11039507.9 01-SEP-21
         5 15555516.8 02-NOV-22
         6 2809048.47 13-SEP-22
         7 2070381.41 18-DEC-21
         8 11116786.1 11-MAR-22
         9 17519679.8 21-DEC-21
        10 6728222.84 02-APR-22
        11 7569442.31 07-AUG-22
        12 16949454.2 06-JUL-21
        13 8019443.02 03-JUN-21
        14 13147674.9 31-AUG-21
        15 14590702.5 16-JUL-22
        16 13028609.7 11-MAY-21
        17 5466477.07 06-APR-22
        18 4469902.12 08-MAY-21
        19   14511096 31-MAY-22
        20 5245726.03 12-JUL-21

20 rows selected.

SQL> select
  2    count(*) total_records,
  3    avg(daily_size_avg)/1024/1024 avg_size_files_per_day_mb,
  4    sum(bytes)/1024/1024/1024 tot_bytes_gb,
  5    avg(files_per_day) avg_files_per_day
  6  from
  7  (
  8  select
  9    bytes,
 10    avg(bytes) over ( partition by trunc(created_at) ) daily_size_avg,
 11    count(*) over ( partition by trunc(created_at) ) files_per_day
 12  from t
 13  );

TOTAL_RECORDS AVG_SIZE_FILES_PER_DAY_MB TOT_BYTES_GB AVG_FILES_PER_DAY
------------- ------------------------- ------------ -----------------
         5000                 9.5313187   46.5396421             8.092