OracleSQL-在具有大量時間戳的（大）表中計算sysdate和sysdate-7天前之間資料的差異（并回傳增長百分比）

(OracleSQL) 我們有自己創建的應用程式，它以 MB 為單位跟蹤存盤使用情況。我想要一個查詢，無需手動更改（因為我們將在我們的應用程式中集成此查詢，它將自動定期運行此查詢）可以計算自 sysdate 以來過去一周的存盤差異。如果我們也可以計算出增長百分比的差異，則可以加分！

我們使用的表已經自動收集了存盤資訊。它每 X 分鐘檢查一次（老實說，我不確定資料的頻率如何，資料似乎相當隨機，但至少每小時一次，而且經常比這更頻繁）。

到目前為止，我的嘗試都失敗了。

任何幫助將不勝感激。

uj5u.com熱心網友回復：

這里有一些東西可以幫助您入門。此查詢將查找每個客戶端的最新條目（與 SYSDATE 相比），以及 SYSDATE 前 7 天的最新條目。如果您想與 TRUNC(SYSDATE) 進行比較，則可能需要將 SYSDATE 包裝在 TRUNC 中。

你可以把它變成一個子查詢，并做任何你需要的額外計算，比如增加 MB 和百分比。那部分是微不足道的；如果你不能自己添加它，也許你一開始就不應該解決這些問題。

由于我在 2022 年 4 月 20 日上午 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

uj5u.com熱心網友回復：

如果您只想要一天的值，并且想要最大總數和已使用以及最小剩余，那么：

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';

將，對于您的樣本資料：

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;

輸出：

客戶名稱	CHECK_DAY	TOTAL_MB	已用_MB	剩余_MB	TOTAL_MB_LAST_WEEK	USED??_MB_LAST_WEEK	REMAINING_MB_LAST_WEEK	PERCENT_TOTAL_GROWTH	PERCENT_USED_GROWTH	PERCENT_REMAINING_GROWTH
客戶1	22 年 3 月 28 日	52280	20533	31859	51090	20300	30790	2.33	1.15	3.47
客戶2	22 年 3 月 28 日	52280	20533	31859	51090	20300	30790	2.33	1.15	3.47

如果您想要當天的值（您尚未提供資料），請將最后一行更改為：

WHERE  check_day = TRUNC(SYSDATE);

或者，如果您想獲取資料庫中所有日期的值，則洗掉該WHERE子句。

db<>在這里擺弄

標籤：sql 甲骨文

上一篇：用于查找不符合閾值的資料的SQL查詢

下一篇：具有動態值的Apex主區域標頭