OracleSQL -在一个有很多时间戳的(大)表中计算sysdate和sysdate-7天前的数据差异(并以%增长率返

Question

(OracleSQL)我们有自己创建的以MB为单位跟踪存储使用情况的应用程序。我想要一个查询，无需手动更改(因为我们将在应用程序中集成此查询，它将自动定期运行此查询)可以计算过去一周的存储差异。如果我们也能计算出%增长的差异，那就加分了!

我们使用的表自动收集了存储信息。它每隔X分钟检查一次(老实说，我不确定多久检查一次，数据看起来相当随机，但至少每小时检查一次，有时甚至更频繁)。

tbody> <<tr>

Client_Name	Check_Timestamp	Total_MB	Used_MB	Remaining_MB
Client1	03/28/2022 15:04:37	52280	20533	34747
Client1	03/28/2022 14:01:20	52280	20421	31859
Client1	03/21/2022 14:01:20	51090	20300	30790
Client1	03/21/2022 13:49:20	51090	20121	30969
Client2	03/28/2022 15:34:17	52280	20533	34747
Client2	03/28/2022 14:20:22	52280	20421	31859
Client2	03/21/2022 14:09:54	51090	20300	30790
Client2	03/21/2022 13:32:03	51090	20121	30969

这里有一些东西可以让你开始。该查询将找到每个客户机的最新条目(与SYSDATE相比)，以及在SYSDATE之前正好7天的最新条目。如果您想与TRUNC(SYSDATE)进行比较，则可能需要将SYSDATE包装在TRUNC中。

您可以将其创建为子查询，并执行您需要的任何其他计算，例如MB和百分比的增长。这部分是微不足道的;如果你不能自己添加它，也许你一开始就不应该处理这样的问题。

因为我在04/20/2022上午10:26运行这个，你的样本数据是不相关的。我用这个代替(只使用相关的列):

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;

查询和输出:

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

如果您只想要一天的值，并且想要最大总数和使用的和最小剩余的，那么:

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，对于你的样本数据:

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;

输出:

USED_MB

CLIENT_NAME	CHECK_DAY	TOTAL_MB	REMAINING_MB	TOTAL_MB_LAST_WEEK	USED_MB_LAST_WEEK	REMAINING_MB_LAST_WEEK	PERCENT_TOTAL_GROWTH	PERCENT_USED_GROWTH		tbody> <<tr>	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