在内部连接中选择 AS colName，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子句中尚不可用。在单独的表中，它只是另一列。

因此，与其在WHERE中distance，不如重复整个操作：

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的评论：

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

如果您遇到某些内容确实是昂贵查询的情况，并且您发现内置缓存确实使重复表达式变得昂贵，则有两种选择：

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

另一种是将您的SELECT包装在另一个SELECT中，将WHERE子句移动到外部SELECT，以便您可以在外部SELECT中使用distance列。但是，使用这种方法，您需要确保内部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)