在Oracle SQL选择查询中使用列引用以避免重新计算

Question

我想创建一个汇总的数据库备份报告，其中包括来自多个彼此没有关系的数据库表的数据。例如，我想包括v$database视图中的数据库名称：

SQL> Select name from v$database;

数据库的大小：

SQL> SELECT sum(bytes) FROM v$datafile;

并且考虑在这两个视图之间没有公共列

除此之外，我想将数据库大小包括在字节、KBs、MB、……中，。。。。。。，等等

我正在使用以下查询：

SQL> SELECT 
"Database", "DB_SIZE_IN_Bytes", "DB_SIZE_IN_KBs", "DB_SIZE_IN_MBs", 
"DB_SIZE_IN_GBs", "DB_SIZE_IN_TBs"
FROM (
SELECT 
(SELECT name FROM v$database ) "Database",
(SELECT sum(bytes) FROM v$datafile ) "DB_SIZE_IN_Bytes",
(SELECT sum(bytes)/1024 FROM v$datafile ) "DB_SIZE_IN_KBs",
(SELECT sum(bytes)/1024/1024 FROM v$datafile ) "DB_SIZE_IN_MBs",
(SELECT sum(bytes)/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_GBs",
(SELECT sum(bytes)/1024/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_TBs"
FROM dual
);

通过这种方式，我避免了v$database和v$datafile之间的联接条件。

这只是一个例子，然而，在我的项目中，我有10多个表。其中大多数没有公共列

问题是，在将字节转换为kbs、mbs等时，我想使用列引用，如：

(SELECT DB_SIZE_IN_Bytes/1024 FROM dual ) "DB_SIZE_IN_KBs",
(SELECT DB_SIZE_IN_KBs /1024 FROM dual ) "DB_SIZE_IN_MBs",
(SELECT DB_SIZE_IN_MBs /1024 FROM dual ) "DB_SIZE_IN_GBs",
(SELECT DB_SIZE_IN_GBs /1024 FROM dual ) "DB_SIZE_IN_TBs"

而不是一次又一次的计算。有没有任何方法可以使用上面例子中提到的列引用，而不是重新计算？

Answer 1

在from子句中使用子查询计算一次和的结果，然后使用：

SELECT (SELECT name FROM v$database ) as "Database",
DB_SIZE_IN_Bytes,
DB_SIZE_IN_Bytes / 1024 as DB_SIZE_IN_KBs,
DB_SIZE_IN_Bytes / (1024 * 1024) as DB_SIZE_IN_MBs,
DB_SIZE_IN_Bytes / (1024 * 1024 * 1024) as DB_SIZE_IN_GBs,
DB_SIZE_IN_Bytes / (1024 * 1024 * 1024 * 1024) as DB_SIZE_IN_TBs
FROM (SELECT sum(bytes) as DB_SIZE_IN_Bytes FROM v$datafile ) x;

你也可以使用横向连接来实现这一点，但对于这个特定的例子来说，这肯定是过头了：

SELECT (SELECT name FROM v$database ) as "Database",
bb.*, bk.*, bm.*, bg.*, bt.*
FROM (SELECT sum(bytes) as DB_SIZE_IN_Bytes FROM v$datafile
) bb CROSS JOIN LATERAL
(SELECT bb.DB_SIZE_IN_Bytes / 1024 as DB_SIZE_IN_Kb
FROM dual
) bk CROSS JOIN LATERAL
(SELECT bk.DB_SIZE_IN_Kb / 1024 as DB_SIZE_IN_Mb
FROM dual
) bm CROSS JOIN LATERAL
(SELECT bm.DB_SIZE_IN_Mb / 1024 as DB_SIZE_IN_Gb
FROM dual
) bg CROSS JOIN LATERAL
(SELECT bb.DB_SIZE_IN_Gb / 1024 as DB_SIZE_IN_Tb
FROM dual
) t;