sql - Amazon Redshift - 横向列别名引用

Question

基于

Amazon Redshift announces support for lateral column alias reference:

The support for lateral column alias reference enables you to write queries without repeating the same expressions in the SELECT list. For example, you can define the alias 'probability' and use it within the same select statement:

select clicks / impressions as probability, 
        round(100 * probability, 1) as percentage from raw_data;

这基本上是一样的:

select 1 AS col
      ,col + 1 AS col2;

db<>fiddle demo

大多数 SQL RDBMS 将返回错误:Unknown column 'col' in 'field list'

---

它看起来像是一个有趣的语言扩展，但有一个警告。如果我有一个不确定的函数怎么办:

select RAND() AS col
      ,col + 1 AS col2

-- if RAND() returns 0.5 then I would expect
-- 0.5 and 1.5

-- I get: 0.3 and 1.7
-- it means that the query was evaluated as:
select RAND() AS col,
       RAND() + 1 AS col2

与 PostgreSQL 的 LATERAL JOIN 相比(是的，我知道这是不同的功能，我希望“lateral coulmn alias”的行为方式相同):

SELECT s.col, s.col+1 AS col2
FROM t ,LATERAL (SELECT RANDOM()) AS s(col)  
-- 0.19089933477628307  1.190899334776283

db<>fiddle demo

但事实并非如此。我得到两个独立的运行，如果它是简单的“内联”，这似乎是有效的:

SELECT List

The alias is recognized right after it is defined in the target list. You can use an alias in other expressions defined after it in the same target list. The following example illustrates this.

The benefit of the lateral alias reference is you don't need to repeat the aliased expression when building more complex expressions in the same target list. When Amazon Redshift parses this type of reference, it just inlines the previously defined aliases. If there is a column with the same name defined in the FROM clause as the previously aliased expression, the column in the FROM clause takes priority.

我的理解是否正确？当我们使用不确定或时间敏感的函数/引用/子查询时，此功能是否“安全”？

Answer 1

这种语法不安全。事实上，仅仅内联代码意味着它甚至不能提供性能优势。它只是语法糖。

鉴于有简单的替代方案——CTE 和子查询——我会避免这个新“功能”。

如果有关闭此功能的设置，我会推荐使用它。

顺便说一句，许多 SQL 新手发现这相当令人不安。这个目的是为了避免歧义。以下查询应返回什么？

select (a + 1) as b, b 
from (select 1 as a, 0 as b) x;

SQL 的设计者可能认为解决此类情况的规则比仅仅重写子查询更复杂。

据我所知，可以很好地解决这个问题的“数据库”实际上是 SAS proc SQL。它引入了 calculated 关键字，因此您可以这样写:

select (a + 1) as b, calculated b, b
from (select 1 as a, 0 as b) x;

这将返回 2, 2, 0。

换句话说，我认为亚马逊并没有花太多心思来实现这个“功能”。