sql - 如何重用 SELECT、WHERE 和 ORDER BY 子句的结果？

Question

以下查询返回我们附近的 field (纬度:62.0，经度:25.0)，我们落在其半径内按距离排序:

SELECT *, 
     earth_distance(ll_to_earth(62.0, 25.0), 
     ll_to_earth(lat, lon)) AS distance 
FROM venues 
WHERE earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) <= radius 
ORDER BY earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon))

是否可以(并且建议)重新使用 earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) 的结果，而不是为 SELECT、WHERE 和ORDER BY 子句？

Answer 1

在 GROUP BY 和 ORDER BY 子句中，您可以引用列别名(输出列)甚至 SELECT 列表项的序号.我引用 the manual on ORDER BY :

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

大胆强调我的。

但是在WHERE 和HAVING 子句中，您只能引用基表中的列(输入列)，因此您必须拼写出您的函数调用。

SELECT *, earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) AS dist
FROM   venues 
WHERE  earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) <= radius 
ORDER  BY distance;

如果您想知道将计算打包到 CTE 中是否更快或子查询，只需使用 EXPLAIN ANALYZE 对其进行测试。 (我对此表示怀疑。)

SELECT *
FROM  (
   SELECT *
         ,earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) AS dist
   FROM   venues
   ) x
WHERE  distance <= radius 
ORDER  BY distance;

赞@Mike commented ，通过声明函数 STABLE(或 IMMUTABLE)，您通知查询规划器函数调用的结果可以在单个语句中多次重复用于相同的调用。我引用 the manual here :

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call.

大胆强调我的。