SQL查询计算两个附加列

Question

我有一张表，它每天都填充数据库大小。我需要修改查询，以便计算每日增长和每周增长。

select * from sys.dbsize 
where SNAP_TIME > sysdate -3
order by SNAP_TIME

电流输出

​

​

我想增加两栏，即每日增长(DB_SIZE sysdate DB_SIZE (sysdate 1))每周增长(DB_SIZE sysdate DB_SIZE (sysdate 7))。

需要一些帮助为这两个额外的列构造SQL。任何帮助都将不胜感激。

谢谢,

Answer 1

一种选择是使用LAG分析函数计算每周增长的日增长和相关子查询(在SELECT语句中)。

例如：

SQL> with dbsize (snap_time, db_size) as
  2    (select sysdate - 8, 100 from dual union all
  3     select sysdate - 7, 110 from dual union all
  4     select sysdate - 6, 105 from dual union all
  5     select sysdate - 5, 120 from dual union all
  6     select sysdate - 4, 130 from dual union all
  7     select sysdate - 3, 130 from dual union all
  8     select sysdate - 2, 142 from dual union all
  9     select sysdate - 1, 144 from dual union all
 10     select sysdate - 0, 150 from dual
 11    )
 12  select
 13    a.snap_time,
 14    a.db_size,
 15    a.db_size - lag(a.db_size) over (order by a.snap_time) daily_growth,
 16    --
 17    db_size - (select db_size from dbsize b
 18               where trunc(b.snap_time) = trunc(a.snap_time) - 7
 19              ) weekly_growth
 20  from dbsize a
 21  order by a.snap_time;

SNAP_TIME              DB_SIZE DAILY_GROWTH WEEKLY_GROWTH
------------------- ---------- ------------ -------------
24.08.2020 21:52:20        100
25.08.2020 21:52:20        110           10
26.08.2020 21:52:20        105           -5
27.08.2020 21:52:20        120           15
28.08.2020 21:52:20        130           10
29.08.2020 21:52:20        130            0
30.08.2020 21:52:20        142           12
31.08.2020 21:52:20        144            2            44
01.09.2020 21:52:20        150            6            40

9 rows selected.

SQL>

Answer 2

我建议两列都使用lag()：

select s.*,
       (dbsize - dbsize_1) as daily_growth,
       (dbsize - dbsize_7) as weekly_growth
from (select s.*,
             lag(dbsize) over (order by snap_time) as dbsize_1,
             lag(dbsize, 7) over (order by snap_time) as dbsize_7
      from sys.dbsize 
     ) s
where SNAP_TIME > sysdate -3
order by SNAP_TIME;

如果您没有每天都有快照，则可以使用窗口框架来处理：

select s.*,
       (dbsize - dbsize_1) as daily_growth,
       (dbsize - dbsize_7) as weekly_growth
from (select s.*,
             max(dbsize) over (order by trunc(snap_time) range between interval '1' day preceding and interval '1' second preceding) as dbsize_1,
             lag(dbsize, 7) over (order by trunc(snap_time) range between '7' day preceding and interval '6 1' day to hour) as dbsize_7
      from sys.dbsize 
     ) s
where SNAP_TIME > sysdate - 3
order by SNAP_TIME;

Answer 3

如果每天总是有一个记录，您可以使用lag()

select 
    snap_time
    db_size,
    db_size - lag(db_size, 1) over(order by snap_time) daily_growth,
    db_size - lag(db_size, 7) over(order by snap_time) weekly_growth
from sys.db.size
order by snap_time

这实际上看起来是1行向后和7行后。如果缺少日期，或每天有多个记录，则可以按日平均快照大小，并在窗口函数中使用窗口范围：

select 
    trunc(snap_time) snap_day,
    avg(db_size) avg_db_size,
    avg(db_size) - avg(db_size) over(
        order by trunc(snap_time)
        range between interval '1' day preceding and interval '1' day preceding
    ) daily_growth,
    avg(db_size) - avg(db_size) over(
        order by trunc(snap_time)
        range between interval '7' day preceding and interval '7' day preceding
    ) weekly_growth
from sys.db.size
group by trunc(snap_time)
order by trunc(snap_time)

如果只想获得最后3天的结果，可以将上述两个查询中的任何一个转换为子查询，并在外部查询中筛选：

select *
from ( ... ) t
where snap_time > sysdate - 3 -- or: snap_day > trunc(sysdate) - 3