如何在Oracle SQL语句中重用动态列？

I try to reuse some columns that I calculate dynamically in Oracle SQL, something like

我尝试重用一些我在Oracle SQL中动态计算的列,类似于

SELECT
    A*2 AS P,
    P+5 AS Q
FROM tablename

Where 'tablename' has a column called 'A', but no other colums. This gives me an

其中'tablename'有一个名为'A'的列,但没有其他列。这给了我一个

ORA-00904: "P": invalid identifier

I know how to work around this by using a subquery like

我知道如何通过使用子查询来解决这个问题

SELECT P, P+5 AS Q
FROM ( SELECT A*2 AS P FROM tablename )

but I think this is kinda ugly. Furthermore I want to make the query somewhat more complex, e.g. reusing 'Q' as well, and I do not want to create yet another subquery.

但我认为这有点难看。此外,我想使查询更复杂,例如重用“Q”,我不想创建另一个子查询。

Update: The reason I want to store the calculation of 'P' is that I want to make it more complex, and reuse 'P' multiple times. So I do not want to explicitly say 'A*2+5 AS Q', because that would quickly become to cumbersome as 'P' gets more complex.

更新:我想存储'P'计算的原因是我想让它更复杂,并多次重复使用'P'。所以我不想明确地说'A * 2 + 5 AS Q',因为随着'P'越来越复杂,这很快会变得很麻烦。

There must be a good way to do this, any ideas?

必须有一个很好的方法来做到这一点,任何想法?

Update: I should note that I'm not a DB-admin :(.

更新:我应该注意到我不是DB-admin :(。

---

Update: A real world example, with a more concrete query. What I would like to do is:

SELECT 
    SL/SQRT(AB) AS ALPHA,
    5*LOG(10,ALPHA) AS B,
    2.5*LOG(10,1-EXP(-5/ALPHA)*(5/ALPHA+1)) AS D
    BS -2.74 + B + D AS BSA
FROM tablename

for now, I've written it out, which works, but is ugly:

现在,我已经把它写出来了,但是很难看:

SELECT
    SL/SQRT(AB) AS ALPHA,
    5*LOG(10,SL/SQRT(AB)) AS B,
    2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*(5/(SL/SQRT(AB))+1)) AS D
    BS -2.74 + 5*LOG(10,SL/SQRT(AB)) + 2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*((5/(SL/SQRT(AB)))+1)) AS BSA
FROM tablename

I could do all of this after receiving the data, but I thought, let's see how much I can let the database do. Also, I would like to select on 'BSA' as well (which I can do now with this query as a subquery/with-clause).

收到数据后我可以做所有这些,但我想,让我们看看我能让数据库做多少。另外,我也想选择“BSA”(现在我可以将此查询作为子查询/ with-clause)。

---

Update: OK, I think for now I finished with Cade Roux' and Dave Costa's solution. Albeit Pax' and Jens Schauder's solution would look better, but I can't use them since I'm not a DBA. Now I don't know who to mark as the best answer :).

更新:好的,我想现在我已经完成了Cade Roux和Dave Costa的解决方案。虽然Pax'和Jens Schauder的解决方案看起来会更好,但我不能使用它们,因为我不是DBA。现在我不知道谁将其标记为最佳答案:)。

WITH 
  A1 AS ( 
    SELECT A0.*, 
    SL/SQRT(AB) AS ALPHA
    FROM tablename A0
  ),
  A2 AS (
    SELECT A1.*, 
    5*LOG(10,ALPHA) AS B,
    2.5*LOG(10,1-EXP(-5/ALPHA)*((5/ALPHA)+1)) AS D
    FROM A1
  )
SELECT
  ALPHA, B, D, BS,
  BS -2.74 + B + D AS BSA
FROM A2

---

BTW, in case anyone is interested, SB is the 'surface brightness' of galaxies, for which B and D are correction terms.

顺便说一句,如果有人感兴趣,SB是星系的“表面亮度”,其中B和D是校正项。

5 个解决方案

#1

We have this same problem in SQL Server (it's an ANSI problem). I believe it is intended to avoid confusing aliasing effects:

我们在SQL Server中遇到同样的问题(这是一个ANSI问题)。我相信它旨在避免混淆别名效果:

SELECT A * 2 AS A
    ,A * 3 AS B -- This is the original A, not the new A
FROM whatever

We work around it by stacking up common table expressions:

我们通过堆叠公用表表达式来解决它:

WITH A1 AS (
    SELECT A * 2 AS A
    FROM whatever
)
,A2 AS (
    SELECT A1.*
        ,A * 3 AS B
    FROM A1
)
,A3 AS (
    SELECT A2.*
        ,A + B AS X
    FROM A2
)
SELECT *
FROM A3

This is the most readable and maintable and followable version.

这是最易读,最易维护和可跟随的版本。

For UPDATEs, there is a deprecated SQL Server workaround using the column_name = notation, where you can reference a column which has been updated previously in the list. But this cannot be used in SELECTs.

对于UPDATE,使用column_name = notation有一个不推荐使用的SQL Server变通方法,您可以在其中引用先前已在列表中更新的列。但这不能在SELECT中使用。

I would hope that some ability to stack expressions (without using a scalar UDF) is added to ANSI SQL at some point in the future.

我希望将来某些时候可以在ANSI SQL中添加一些堆栈表达式(不使用标量UDF)的能力。

#2

I'm not sure you can do this (I've never seen it done) but you could work around it with:

我不确定你能做到这一点(我从未见过它)但你可以解决它:

SELECT
    A*2   AS P,
    A*2+5 AS Q
FROM tablename

That's certainly better than introducing a subquery.

这肯定比引入子查询更好。

The only other way I'd suggest is to create a view giving you the P/Q-type columns (using the formulae above) which would at least simplify the text of the query. Then you could just:

我建议的唯一另一种方法是创建一个视图,为您提供P / Q类型列(使用上面的公式),这至少可以简化查询的文本。然后你可以:

SELECT P, Q FROM viewintotablename

#3

There is no direct way to do this in sql.

在sql中没有直接的方法。

But you could define a function using PL/SQL. So your select would look like this

但是你可以使用PL / SQL定义一个函数。所以你的选择会是这样的

select 
    P(A), 
    Q(P(A)) 
from tablename

For P and Q this isn't (much) better then the original, but if the functions are complex, and don't have to many parameter, it might make your statement much more readable.

对于P和Q,这并不比原始更好,但如果函数很复杂,并且没有很多参数,它可能会使您的语句更具可读性。

It also would allow you to test you functions independently from the sql statement, and any data.

它还允许您独立于sql语句和任何数据测试函数。

#4

You might like this a little better than the inline view example you gave:

您可能比您给出的内联视图示例更好一些:

WITH inner_view AS ( SELECT A*2 AS P FROM tablename )
SELECT P, P+5 AS Q
FROM inner_view

It amounts to the same thing but it's a little clearer to read, I think.

它相当于同样的东西,但我认为它更清晰。

If the computed column is something you will use in multiple columns, it may make sense to create a permanent view.

如果计算列是您将在多列中使用的内容,则创建永久视图可能是有意义的。

Oracle 11 (which I haven't used yet) has a virtual column feature that might be useful for you.

Oracle 11(我尚未使用)具有可能对您有用的虚拟列功能。

#5

You can't.

If you don't want the subquery to be reevaluated, add a NO_MERGE hint for the subquery:

如果您不希望重新评估子查询,请为子查询添加NO_MERGE提示:

This subquery will be reevaluated in the nested loop (MERGE hint is used):

此子查询将在嵌套循环中重新评估(使用MERGE提示):

SELECT  /*+ LEADING(g) USE_NL(g, r) MERGE(g) */
        *
FROM    (
        SELECT  1
        FROM    dual
        UNION ALL
        SELECT  2
        FROM    dual
        ) r, 
        (
        SELECT  SYS_GUID() AS guid
        FROM    dual d
        ) g

---
33CA48C1AB6B4403808FB0219302CE43
711BB04F9AFC406ABAEF8A8F4CFA1266

This subquery will not be reevaluated in the nested loop (NO_MERGE hint is used):

此子查询不会在嵌套循环中重新评估(使用NO_MERGE提示):

SELECT  /*+ LEADING(g) USE_NL(g, r) NO_MERGE(g) */
        *
FROM    (
        SELECT  1
        FROM    dual
        UNION ALL
        SELECT  2
        FROM    dual
        ) r, 
        (
        SELECT  SYS_GUID() AS guid
        FROM    dual d
        ) g

------
7715C69698A243C0B379E68ABB55C088
7715C69698A243C0B379E68ABB55C088

In your case, just write:

在你的情况下,只需写:

SELECT  BS - 2.74 + d
FROM    (
        SELECT  t2.*, 2.5 * LOG(10, 1 - EXP(-5 / b)) * ((5 / A) + 1) AS d
        FROM    (
                SELECT  t1.*, 5 * LOG(10, alpha) AS b
                FROM    (
                        SELECT  /*+ NO_MERGE */ t.*,
                                SL/SQRT(AB) AS alpha
                        FROM    tablename t
                        ) t1
                ) t2
        ) t3

, which is more efficient (EXP and LOG are costly) and is much easier to debug.

,效率更高(EXP和LOG成本高),并且更容易调试。