我正在尝试根据数据表中的复杂表达式选择一个值，有一个像这样的选择，为了将来的可维护性，我不是把整个表达式写成一个，而是我在子选择中计算部分结果：
（查询并不重要，概念是这样，所以请耐心等待我，因为我已经将这个样本查询去个性化。我使用一两个字母别名仅用于演示目的）
select t.*,
(1 - nvl(min(t.O / t.MA) over(partition by t.fk_1, t.fk_2, t.fk_3, t.fk_4), 0)) *
t.MA as V --value regulated by overhead
from (select t.*,
decode(sign(t.MA - t.M), 1, t.MA - t.M, to_number(null)) as O --overhead
from (select t.*,
(1 - (max(t.PD) over(partition by t.fk_1, t.fk_2, t.fk_3, t.fk_4))) * t.ASZ as MA --value regulated by maximum percent difference
from (select t.*,
t.D / t.ASZ as PD --precent of diff
from (select t.*,
t.ASZ - t.BE as D --difference
from (select l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
l.A, --Ratio
l.SB, --Sum of values
l.M, --Maximum of value
decode((l.SB * l.A), 0, to_number(null), (l.SB * l.A)) as ASZ --Sum distributed as of given ratio
from vw_data l,
) t) t) t) t) t;
我想知道是否可以用与此类似的方式重写（这种语法不起作用）：
select l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.be, --Value that needs regulation by ratio on of Sum of values and maximum
l.a, --Ratio
l.sb, --Sum of values
l.m, --Maximum of value
decode((l.sb * l.a), 0, to_number(null), (l.sb * l.a)) as asz, --Sum distributed as of given ratio
asz - be as d, --difference
d / asz as pd, --precent of diff
(1 - (max(pd) over(partition by l.fk_1, l.fk_2, l.fk_3, l.fk_4))) * asz as ma, --value regulated by maximum percent difference
decode(sign(ma - l.m), 1, ma - l.m, to_number(null)) as o, --overhead
(1 - nvl(min(o / ma) over(partition by l.fk_1, l.fk_2, l.fk_3, l.fk_4), 0)) * ma as v --value regulated by overhead
from vw_data l
如果你希望它的格式更好但更长：
SELECT t.*,
( 1 - nvl( MIN( t.O / t.MA ) OVER ( PARTITION BY t.fk_1,
t.fk_2,
t.fk_3,
t.fk_4 ),
0 ) ) *
t.MA AS V --value regulated by overhead
FROM ( SELECT t.*,
DECODE( sign( t.MA - t.M ),
1,
t.MA - t.M,
TO_NUMBER( null ) ) AS O --overhead
FROM ( SELECT t.*,
( 1 - ( MAX( t.PD ) OVER ( PARTITION BY t.fk_1,
t.fk_2,
t.fk_3,
t.fk_4 ) ) ) *
t.ASZ AS MA --value regulated by maximum percent difference
FROM ( SELECT t.*,
t.D / t.ASZ AS PD --precent of diff
FROM ( SELECT t.*,
t.ASZ - t.BE AS D --difference
FROM ( SELECT l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
l.A, --Ratio
l.SB, --Sum of values
l.M, --Maximum of value
DECODE( ( l.SB * l.A ),
0,
TO_NUMBER( NULL ),
( l.SB * l.A ) ) AS ASZ --Sum distributed as of given ratio
FROM vw_data l,
) t ) t ) t ) t ) t;
我想知道是否可以用与此类似的方式重写（这种语法不起作用）：
SELECT l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.be, --Value that needs regulation by ratio on of Sum of values and maximum
l.a, --Ratio
l.sb, --Sum of values
l.m, --Maximum of value
DECODE( ( l.sb * l.a ),
0,
TO_NUMBER( NULL ),
( l.sb * l.a ) ) AS asz, --Sum distributed as of given ratio
asz - be AS d, --difference
d / asz AS pd, --precent of diff
( 1 - ( MAX( pd ) OVER ( PARTITION BY l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4 ) ) ) * asz AS ma, --value regulated by maximum percent difference
DECODE( SIGN ( ma - l.m ),
1,
ma - l.m,
TO_NUMBER( NULL ) ) AS o, --overhead
( 1 - nvl( MIN( o / ma ) OVER ( PARTITION BY l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4 ), 0 ) ) * ma AS v --value regulated by overhead
FROM vw_data l
I'm trying to select a value based on a complicated expression from a data table, having a select like this where for future maintainability I'm not writing the whole expression in one, instead I'm calculating partial results in subselects :
(The query is not really important, the concept is, so please bear with me as I have depersonalized this sample query. I'm using one or two letter aliases just for presentation purposes)
select t.*,
(1 - nvl(min(t.O / t.MA) over(partition by t.fk_1, t.fk_2, t.fk_3, t.fk_4), 0)) *
t.MA as V --value regulated by overhead
from (select t.*,
decode(sign(t.MA - t.M), 1, t.MA - t.M, to_number(null)) as O --overhead
from (select t.*,
(1 - (max(t.PD) over(partition by t.fk_1, t.fk_2, t.fk_3, t.fk_4))) * t.ASZ as MA --value regulated by maximum percent difference
from (select t.*,
t.D / t.ASZ as PD --precent of diff
from (select t.*,
t.ASZ - t.BE as D --difference
from (select l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
l.A, --Ratio
l.SB, --Sum of values
l.M, --Maximum of value
decode((l.SB * l.A), 0, to_number(null), (l.SB * l.A)) as ASZ --Sum distributed as of given ratio
from vw_data l,
) t) t) t) t) t;
I was wondering if this could be rewritten in a way similar to this (which syntax doesn't work atm):
select l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.be, --Value that needs regulation by ratio on of Sum of values and maximum
l.a, --Ratio
l.sb, --Sum of values
l.m, --Maximum of value
decode((l.sb * l.a), 0, to_number(null), (l.sb * l.a)) as asz, --Sum distributed as of given ratio
asz - be as d, --difference
d / asz as pd, --precent of diff
(1 - (max(pd) over(partition by l.fk_1, l.fk_2, l.fk_3, l.fk_4))) * asz as ma, --value regulated by maximum percent difference
decode(sign(ma - l.m), 1, ma - l.m, to_number(null)) as o, --overhead
(1 - nvl(min(o / ma) over(partition by l.fk_1, l.fk_2, l.fk_3, l.fk_4), 0)) * ma as v --value regulated by overhead
from vw_data l
If you prefer to have it formatted a little bit better but much longer:
SELECT t.*,
( 1 - nvl( MIN( t.O / t.MA ) OVER ( PARTITION BY t.fk_1,
t.fk_2,
t.fk_3,
t.fk_4 ),
0 ) ) *
t.MA AS V --value regulated by overhead
FROM ( SELECT t.*,
DECODE( sign( t.MA - t.M ),
1,
t.MA - t.M,
TO_NUMBER( null ) ) AS O --overhead
FROM ( SELECT t.*,
( 1 - ( MAX( t.PD ) OVER ( PARTITION BY t.fk_1,
t.fk_2,
t.fk_3,
t.fk_4 ) ) ) *
t.ASZ AS MA --value regulated by maximum percent difference
FROM ( SELECT t.*,
t.D / t.ASZ AS PD --precent of diff
FROM ( SELECT t.*,
t.ASZ - t.BE AS D --difference
FROM ( SELECT l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
l.A, --Ratio
l.SB, --Sum of values
l.M, --Maximum of value
DECODE( ( l.SB * l.A ),
0,
TO_NUMBER( NULL ),
( l.SB * l.A ) ) AS ASZ --Sum distributed as of given ratio
FROM vw_data l,
) t ) t ) t ) t ) t;
I was wondering if this could be rewritten in a way similar to this (which syntax doesn't work atm):
SELECT l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.be, --Value that needs regulation by ratio on of Sum of values and maximum
l.a, --Ratio
l.sb, --Sum of values
l.m, --Maximum of value
DECODE( ( l.sb * l.a ),
0,
TO_NUMBER( NULL ),
( l.sb * l.a ) ) AS asz, --Sum distributed as of given ratio
asz - be AS d, --difference
d / asz AS pd, --precent of diff
( 1 - ( MAX( pd ) OVER ( PARTITION BY l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4 ) ) ) * asz AS ma, --value regulated by maximum percent difference
DECODE( SIGN ( ma - l.m ),
1,
ma - l.m,
TO_NUMBER( NULL ) ) AS o, --overhead
( 1 - nvl( MIN( o / ma ) OVER ( PARTITION BY l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4 ), 0 ) ) * ma AS v --value regulated by overhead
FROM vw_data l
最满意答案
不是真的。 除了order by clause之外，您不能在同一级别的查询中引用列别名 - 这使得理想的查询无法令人遗憾。 例如，当您尝试执行asz - be AS d ， asz将无法识别，因为基表中没有具有该名称的列，并且具有该名称的别名尚不存在。
从文档 ：
c_alias 为列表达式指定别名。 Oracle数据库将在结果集的列标题中使用此别名。 AS关键字是可选的。 别名在查询期间有效地重命名选择列表项。 别名可以在order_by_clause中使用，但不能在查询中的其他子句中使用。
关于为什么这样的原因有各种各样的解释，尽管它们倾向于为什么你不能在where，group-by或having子句中使用别名。 为什么你不能在同一个选择列表中再次使用它们可能不太明显。 但请考虑您可以将表达式别名设置为与实际列名相同，因此您可以尝试执行以下操作：
select abs(be) as be, be - asz as d, ...
......引入了额外歧义的可能性 - 在您的计算中是指原始列值，还是别名中的修改值？ 当然，我并不是在暗示你是或会这样做。 稍有可能的是，有人可能会更改表以添加名为asz的列，这会混淆现有查询。 但无论什么原因，您都不能在同一个选择列表中引用列别名。
您可以使用子查询因子而不是嵌套子查询 - 这至少可以让您按顺序列出中间步骤，这可能更直观一些; 但它并没有那么大的帮助：
with t1 as (
select l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
l.A, --Ratio
l.SB, --Sum of values
l.M, --Maximum of value
decode((l.SB * l.A), 0, to_number(null), (l.SB * l.A)) as ASZ --Sum distributed as of given ratio
from vw_data l),
t2 as (
select t1.*,
t1.ASZ - t1.BE as D --difference
from t1),
t3 as (
select t2.*,
t2.D / t2.ASZ as PD --precent of diff
from t2),
t4 as (
select t3.*,
(1 - (max(t3.PD) over (partition by t3.fk_1, t3.fk_2, t3.fk_3, t3.fk_4)))
* t3.ASZ as MA --value regulated by maximum percent difference
from t3),
t5 as (
select t4.*,
decode(sign(t4.MA - t4.M), 1, t4.MA - t4.M, to_number(null)) as O --overhead
from t4)
select t5.*,
(1 - nvl(min(t5.O / t5.MA) over(partition by t5.fk_1, t5.fk_2, t5.fk_3, t5.fk_4), 0))
* t5.MA as V --value regulated by overhead
from t5;
在这种情况下，这有点偏好。
否则，您需要坚持一个级别的查询并多次重复所有计算，这是您要避免的。 或者创建一系列函数来执行各种计算（在它们之间重复），这将是额外的开销，甚至更不透明。
Not really. You can't refer to a column alias in the same level of query, except in the order by clause - which makes your ideal query impossible sadly. For instance, when you try to do asz - be AS d the asz will not be recognised as there is no column with that name in the base table, and the alias with that name doesn't exist yet.
From the documentation:
c_alias Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.
There are various explanations around about why, like this one, though they tend to be about why you can't use the alias in a where, group-by or having clause. It's maybe less obvious why you can't use them again within the same select list. But consider that you can alias an expression to be the same as a real column name, so you could attempt do something like:
select abs(be) as be, be - asz as d, ...
... which introduces the possibility of additional ambiguity - is the be in your calculation referring to the original column value, or the modified value in the alias? I'm not suggesting you are or would do that, of course. Slightly more likely is that someone could alter the table to add a column called asz, which would confuse existing queries. But whatever the reasoning, you can't refer to a column alias in the same select list, either.
You could use subquery factoring instead of nested subqueries - which would at least let you list the intermediate steps in order, which might a bit more intuitive; but it doesn't really help that much:
with t1 as (
select l.fk_1,
l.fk_2,
l.fk_3,
l.fk_4,
l.fk_5,
l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
l.A, --Ratio
l.SB, --Sum of values
l.M, --Maximum of value
decode((l.SB * l.A), 0, to_number(null), (l.SB * l.A)) as ASZ --Sum distributed as of given ratio
from vw_data l),
t2 as (
select t1.*,
t1.ASZ - t1.BE as D --difference
from t1),
t3 as (
select t2.*,
t2.D / t2.ASZ as PD --precent of diff
from t2),
t4 as (
select t3.*,
(1 - (max(t3.PD) over (partition by t3.fk_1, t3.fk_2, t3.fk_3, t3.fk_4)))
* t3.ASZ as MA --value regulated by maximum percent difference
from t3),
t5 as (
select t4.*,
decode(sign(t4.MA - t4.M), 1, t4.MA - t4.M, to_number(null)) as O --overhead
from t4)
select t5.*,
(1 - nvl(min(t5.O / t5.MA) over(partition by t5.fk_1, t5.fk_2, t5.fk_3, t5.fk_4), 0))
* t5.MA as V --value regulated by overhead
from t5;
It's somewhat a matter of preference in this scenario.
Otherwise you'd need to stick to a single level of query and repeat all the calculations multiple times, which is what you're trying to avoid. Or create a load of functions that do the various calculations (with repetition between them), which would be extra overhead and even more opaque.
更多推荐
发布评论