sql – 怎样重用SELECT,WHERE和ORDER BY子句的功效？

以下查询返回我们四面的园地(lat：62.0,lon：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,lon)) <= radius 
ORDER BY earth_distance(ll_to_earth(62.0,lon))

是否可以(而且提议)一再行使earth_distance(ll_to_earth(62.0,lon))的功效,而不是单独为SELECT,WHERE和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 *,lon)) AS dist
FROM   venues 
WHERE  earth_distance(ll_to_earth(62.0,lon)) <= radius 
ORDER  BY distance;

假如您想知道将计较打包到CTE或子查询中是否更快,只需行使EXPLAIN ANALYZE举办测试即可. (我对此暗示猜疑.)

SELECT *
FROM  (
   SELECT *,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.

斗胆夸大我的.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!