如何重用 SELECT、WHERE 和 ORDER BY 子句的结果? [英] How to re-use result for SELECT, WHERE and ORDER BY clauses?

问题描述

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

The following query returns the venues near us (lat: 62.0, lon: 25.0) inside whose radius we fall in ordered by distance:

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 子句?

Is it possible (and advisable) to re-use the result from earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) instead of computing it separately for SELECT, WHERE and ORDER BY clauses?

推荐答案

在 GROUP BY 和 ORDER BY 子句中，您可以引用列别名(输出列)或SELECT 列表项的偶数序号.我引用了 ORDER BY 的手册:

In the GROUP BY and ORDER BY clause you can refer to column aliases (output columns) or even ordinal numbers of SELECT list items. I quote the manual on ORDER BY:

每个表达式可以是输出列的名称或序号(SELECT 列表项)，也可以是由以下构成的任意表达式输入列值.

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 子句中，您只能引用基表中的列(输入列)，因此您必须拼出您的函数调用.

But in the WHERE and HAVING clauses, you can only refer to columns from the base tables (input columns), so you have to spell out your function call.

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 对其进行测试.(我怀疑.)

If you want to know if it's faster to pack the calculation into a CTE or subquery, just test it with EXPLAIN ANALYZE. (I doubt it.)

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 评论，通过声明一个函数 STABLE(或 IMMUTABLE)，你通知查询规划器一个函数调用的结果可以被多次重复用于相同的调用在单个语句中.我引用此处的手册:

Like @Mike commented, by declaring a function STABLE (or IMMUTABLE) you inform the query planner that results from a function call can be reused multiple times for identical calls within a single statement. I quote the manual here:

STABLE 函数不能修改数据库，并且保证给定相同参数的所有行返回相同的结果单一声明.这个类别允许优化器优化多次调用函数到一个调用.

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.

粗体强调我的.

这篇关于如何重用 SELECT、WHERE 和 ORDER BY 子句的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！