代码之家 › 专栏 › 技术社区 › Sigularity

汇总函数和合计百分比值

rollup oracle

Sigularity · 技术社区 · 7 年前

我试图通过使用汇总函数来显示表空间使用情况,结果看起来不错。然而,正如你所看到的,总的百分比只是一个总和,而不是百分比。你能帮我更改下面的查询以获得总数的百分比值吗?

SELECT nvl(df.tablespace_name,'Total') TABLESPACE,
    sum(df.total_space_mb) TOTAL_SPACE_MB,
    sum((df.total_space_mb - fs.free_space_mb)) USED_SPACE_MB,
    sum(fs.free_space_mb) FREE_SPACE_MB,
    sum(ROUND(100 * (fs.free_space / df.total_space),2)) PERCENT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
    FROM dba_free_space
    GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
GROUP BY rollup(df.tablespace_name)
ORDER BY df.tablespace_name


TABLESPACE      TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB PERCENT_FREE
--------------- -------------- ------------- ------------- ------------
DEMO                      1500            47          1453        96.87
NORMAL                      10             1             9        88.75
SYSAUX                    7990          7600           390         4.88
SYSTEM                     970           961             9           .9
UNDOTBS1                  1690            24          1666        98.57
USERS                     5275           964          4311        81.73
Total                    17435          9597          7838        371.7

1 回复 | 直到 7 年前

Mighty.Moogle 7 年前

您可以使用 DECODE 为此:

DECODE(df.tablespace_name, NULL,
        ROUND(100*SUM(fs.FREE_SPACE)/SUM(df.total_space),2),
        SUM(ROUND(100 * (fs.free_space / df.total_space),2))) PERCENT_FREE,

当它是“Total”行(tablespace\u name为null)时,对整个表的和进行四舍五入。然后像往常一样在解码的else子句中计算其他行。

SELECT nvl(df.tablespace_name,'Total') TABLESPACE,
    sum(df.total_space_mb) TOTAL_SPACE_MB,
    sum((df.total_space_mb - fs.free_space_mb)) USED_SPACE_MB,
    sum(fs.free_space_mb) FREE_SPACE_MB,
    DECODE(df.tablespace_name, NULL,
      ROUND(100*SUM(fs.FREE_SPACE)/SUM(df.total_space),2),
      SUM(ROUND(100 * (fs.free_space / df.total_space),2))) PERCENT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
    FROM dba_free_space
    GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
GROUP BY rollup(df.tablespace_name)
ORDER BY df.tablespace_name