postgresql - SELECT AS colName in inner join, colName 不存在 (Postgres)

Question

我在一个函数中有一个 SELECT 子句，在该子句中我连接了两个表，以查找距离用户一定距离内且当前营业的餐馆。出于某种原因，当我运行这个函数时，我得到以下信息错误:

ERROR: column "distance" does not exist
LINE 10:              distance < searchDist AND
                      ^

这是我的代码:

CREATE OR REPLACE FUNCTION get_nearby_open_restaurants (searchDist integer, myLat numeric, myLon numeric)
RETURNS json AS $$
BEGIN
CREATE TEMPORARY TABLE nearbyOpenRestaurants AS
    SELECT Restaurants.restaurantID, Restaurants.name, Restaurants.type, 
           Restaurants.address, Restaurants.deliveryDistance,
           (3959 * acos(cos(radians(myLat)) * cos(radians(latitude)) * 
            cos(radians(longitude) - radians(myLon)) + 
            sin(radians(myLat)) * sin(radians(latitude)))) AS distance,
           BusinessHours.hoursJSON
    FROM Restaurants, BusinessHours
    WHERE Restaurants.RestaurantID = BusinessHours.RestaurantID AND 
          distance < searchDist AND 
          distance < Restaurants.deliveryDistance
    /*Check if the restaurant is open here*/
    ;

RETURN to_json(nearbyOpenRestaurants);
DROP TABLE nearbyOpenRestaurants;
END;
$$ LANGUAGE plpgsql;  

澄清一下，包含距离计算的 SELECT AS 子句在其表中工作得很好，如下所示:

SELECT restaurantID, name, type, address, deliveryDistance,
    (3959 * acos(cos(radians(myLat)) * cos(radians(latitude)) * 
     cos(radians(longitude) - radians(myLon)) + 
     sin(radians(myLat)) * sin(radians(latitude)))) AS distance 
    FROM Restaurants
    HAVING distance < searchDist AND distance < deliveryDistance;

所以只有当我将两个表连接在一起时才会出现问题。 HAVING 子句似乎对加入的 SELECT 也没有帮助。我究竟做错了什么？谢谢!

Answer 1

您的问题是 distance这是 SELECT 中的别名WHERE 中尚不可用条款。在单独的表格中，它只是另一列。

所以，而不是 distance在WHERE ，重复整个操作:

sin(radians(myLat)) * sin(radians(latitude))))

所以你的 WHERE条款将成为:

 WHERE Restaurants.RestaurantID = BusinessHours.RestaurantID AND 
          ( sin(radians(myLat)) * sin(radians(latitude)))) ) < searchDist AND 
         ( sin(radians(myLat)) * sin(radians(latitude)))) ) < Restaurants.deliveryDistance

我添加了括号以进一步描绘并增加清晰度。

根据 OP 的评论进行编辑:

当您想重新使用 SELECT 中的值时，这种重复计算正是需要在 SQL 中完成的方式在WHERE同一个查询。一般来说，重做一个计算不是什么大问题(我相信它有一些缓存可以帮助解决这个问题)，我怀疑这些简单的数学计算也不异常(exception)，所以我建议这样做重复法。

如果您碰巧遇到了一个真正昂贵的查询的情况，并且您发现内置缓存确实使重复表达式变得昂贵，那么有两种选择:

一种是使用 CTE ，其中涉及 WITH表达。这适用于独立查询，即不依赖于您将在其中使用它的查询的输入的查询。这基本上用作查询范围的临时表。但是，在您的情况下，您的重复表达式不是恒定的——它会根据您的 JOIN 中的特定行而变化。 ed 表 - 因此 CTE 对您不起作用。

另一个是包装你的 SELECT另一个内部SELECT , 移动 WHERE外部条款 SELECT这样 distance列在外部可用 SELECT .但是，使用这种方法，您需要确保您的内部 SELECT返回了外部 SELECT 所需的所有列在其 WHERE 中需要子句，这将需要在您的案例中添加列，总体而言，查询似乎比它需要的更复杂，尽管如果更新的 JOIN 复杂性可能会降低一点在标准移出 WHERE 的地方使用语法和 JOIN ... ON被改用了。

所以，我再次建议重复这个表达。仅在绝对必要时尝试进一步优化。

编辑以回应来自 OP 的第二条评论:

它不应该与表是否连接有任何关系——引用来自 SELECT 的别名列不会在 HAVING 中工作或 WHERE单数表中的子句。

例如

mydb=# create table bar (id serial, val decimal);
CREATE TABLE
mydb=# insert into bar (val) values (1.2), (1.1), (3.2);
INSERT 0 3
mydb=# select * from bar;
 id | val 
----+-----
  1 | 1.2
  2 | 1.1
  3 | 3.2
(3 rows)

mydb=# select id, val, sin(val) from bar;
 id | val |         sin         
----+-----+---------------------
  1 | 1.2 |   0.932039085967226
  2 | 1.1 |   0.891207360061435
  3 | 3.2 | -0.0583741434275801
(3 rows)

mydb=# select id, val, sin(val) as narf from bar;
 id | val |        narf         
----+-----+---------------------
  1 | 1.2 |   0.932039085967226
  2 | 1.1 |   0.891207360061435
  3 | 3.2 | -0.0583741434275801
(3 rows)

mydb=# select id, val, sin(val) as narf from bar having narf > 0;
ERROR:  column "narf" does not exist
LINE 1: select id, val, sin(val) as narf from bar having narf > 0;
                                                         ^
mydb=# select id, val, sin(val) as narf from bar having sin(val) > 0;
ERROR:  column "bar.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select id, val, sin(val) as narf from bar having sin(val) > ...
               ^

mydb=# select id, val, sin(val) as narf from bar where narf > 0;
ERROR:  column "narf" does not exist
LINE 1: select id, val, sin(val) as narf from bar where narf > 0;
                                                        ^
mydb=# select id, val, sin(val) as narf from bar where sin(val) > 0;
 id | val |       narf        
----+-----+-------------------
  1 | 1.2 | 0.932039085967226
  2 | 1.1 | 0.891207360061435
(2 rows)