(OracleSQL) We have our own created application that tracks storage usage in MB. I'd like a query that, without manual changes (as we will integrate this query in our application, which will automatically periodically run this query) can calculate the difference of storage for the past week since sysdate. Bonuspoints if we can calculate this difference in %growth too!

The table we use has automatically collected information on storage. It checks every X minutes (I honestly am not sure how often, the data seems rather random, but it's at least hourly and often more often than that).

My attempts have failed miserably this far.

Any help would be greatly appreciated.

CodePudding user response：

Here is something to get you started. This query will find the most recent entry for each client (comparing to SYSDATE), and the most recent entry as of exactly seven days before SYSDATE. You might need to wrap SYSDATE within TRUNC if you want to compare to TRUNC(SYSDATE) instead.

You can make this into a subquery and do any additional computations you need, such as increase in MB and in percentage points. That part is trivial; if you can't add it yourself, perhaps you shouldn't be working on such problems in the first place.

Since I am running this on 04/20/2022 at 10:26 AM my time, your sample data isn't relevant. I used this instead (only relevant columns):

create table storage_tracking
( client_name     varchar2(15)
, check_timestamp timestamp 
, used_mb number
);

alter session set nls_timestamp_format = 'mm/dd/yyyy hh24:mi:ss';

insert into storage_tracking
  select 'Client1', '04/20/2022 10:04:37',   20533 from dual union all
  select 'Client1', '04/19/2022 04:01:20',   20421 from dual union all
  select 'Client1', '04/13/2022 14:01:20',   20300 from dual union all
  select 'Client1', '04/13/2022 08:49:20',   20121 from dual union all
  select 'Client2', '04/20/2022 10:24:17',   20533 from dual union all
  select 'Client2', '04/20/2022 08:20:22',   20421 from dual union all
  select 'Client2', '04/14/2022 14:09:54',   20300 from dual union all
  select 'Client2', '04/12/2022 07:32:03',   20121 from dual
;

commit;

Query and output:

select client_name,
       max(case when check_timestamp <= sysdate - 7 
                then check_timestamp end) as week_earlier,
       max(used_mb) keep (dense_rank last order by case 
                when check_timestamp <= sysdate - 7
                then check_timestamp end nulls first) as used_mb_week_earlier,
       max(case when check_timestamp <= sysdate
                then check_timestamp end) as now,
       max(used_mb) keep (dense_rank last order by case
                when check_timestamp <= sysdate
                then check_timestamp end nulls first) used_mb_now
from   storage_tracking
group  by client_name
;

CLIENT_NAME     WEEK_EARLIER        USED_MB_WEEK_EARLIER NOW                 USED_MB_NOW
--------------- ------------------- -------------------- ------------------- -----------
Client1         04/13/2022 08:49:20                20121 04/20/2022 10:04:37       20533
Client2         04/12/2022 07:32:03                20121 04/20/2022 10:24:17       20533

CodePudding user response：

If you just want the values for a day and want the maximum total and used and the minimum remaining then:

SELECT t.*,
       ROUND((total_mb / total_mb_last_week) * 100 - 100, 2) AS percent_total_growth,
       ROUND((used_mb / used_mb_last_week) * 100 - 100, 2) AS percent_used_growth,
       ROUND((remaining_mb / remaining_mb_last_week) * 100 - 100, 2) AS percent_remaining_growth
FROM   (
  SELECT client_name,
         TRUNC(Check_timestamp) AS check_day,
         MAX(total_mb) AS total_mb,
         MAX(used_mb) AS used_mb,
         MIN(remaining_mb) AS remaining_mb,
         MAX(MAX(total_mb)) OVER (
           PARTITION BY Client_Name
           ORDER BY TRUNC(Check_Timestamp)
           RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '7' DAY PRECEDING
         ) AS total_mb_last_week,
         MAX(MAX(used_mb)) OVER (
           PARTITION BY Client_Name
           ORDER BY TRUNC(Check_Timestamp)
           RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '7' DAY PRECEDING
         ) AS used_mb_last_week,
         MAX(MIN(remaining_mb)) OVER (
           PARTITION BY Client_Name
           ORDER BY TRUNC(Check_Timestamp)
           RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '7' DAY PRECEDING
         ) AS remaining_mb_last_week
  FROM   table_name
  GROUP BY client_name, TRUNC(Check_Timestamp)
) t
WHERE check_day = DATE '2022-03-28';

Will, for your sample data:

CREATE TABLE table_name (Client_Name, Check_Timestamp, Total_MB, Used_MB, Remaining_MB) AS
SELECT 'Client1', DATE '2022-03-28'   INTERVAL '15:04:37' HOUR TO SECOND, 52280, 20533, 34747 FROM DUAL UNION ALL
SELECT 'Client1', DATE '2022-03-28'   INTERVAL '14:01:20' HOUR TO SECOND, 52280, 20421, 31859 FROM DUAL UNION ALL
SELECT 'Client1', DATE '2022-03-21'   INTERVAL '14:01:20' HOUR TO SECOND, 51090, 20300, 30790 FROM DUAL UNION ALL
SELECT 'Client1', DATE '2022-03-21'   INTERVAL '13:49:20' HOUR TO SECOND, 51090, 20121, 30969 FROM DUAL UNION ALL
SELECT 'Client2', DATE '2022-03-28'   INTERVAL '15:34:17' HOUR TO SECOND, 52280, 20533, 34747 FROM DUAL UNION ALL
SELECT 'Client2', DATE '2022-03-28'   INTERVAL '14:20:22' HOUR TO SECOND, 52280, 20421, 31859 FROM DUAL UNION ALL
SELECT 'Client2', DATE '2022-03-21'   INTERVAL '14:09:54' HOUR TO SECOND, 51090, 20300, 30790 FROM DUAL UNION ALL
SELECT 'Client2', DATE '2022-03-21'   INTERVAL '13:32:03' HOUR TO SECOND, 51090, 20121, 30969 FROM DUAL;

Output:

CLIENT_NAME	CHECK_DAY	TOTAL_MB	USED_MB	REMAINING_MB	TOTAL_MB_LAST_WEEK	USED_MB_LAST_WEEK	REMAINING_MB_LAST_WEEK	PERCENT_TOTAL_GROWTH	PERCENT_USED_GROWTH	PERCENT_REMAINING_GROWTH
Client1	28-MAR-22	52280	20533	31859	51090	20300	30790	2.33	1.15	3.47
Client2	28-MAR-22	52280	20533	31859	51090	20300	30790	2.33	1.15	3.47

If you want the values for the current day (which you have not provided data for) then change the last line to:

WHERE  check_day = TRUNC(SYSDATE);

Or, if you want to get the values for all days in the database then remove that WHERE clause.

db<>fiddle here