sql - JOIN的ON子句中的聚合函数

为什么Postgres不允许我在JOIN中使用聚合函数？

SELECT p.id, p.actual_price,
   h1.min_price, h1.max_price
FROM (
  SELECT min(price) as min_price, max(price) as max_price, product_id
  FROM prices_history
  WHERE timestamp > '2019-01-01'
  GROUP BY product_id
  ) AS h1,
  products p
JOIN (
  SELECT max(timestamp), price, product_id
  FROM prices_history
  WHERE timestamp > '2019-01-01'
  GROUP BY product_id, price
  ) AS h2
  ON  h2.product_id = p.id
     AND h2.price = h1.min_price
 WHERE
  p.id = h1.product_id
 GROUP BY p.id, p.actual_price, h1.min_price, h2.max_price
 ORDER BY p.id;

错误：列“min价格”不存在
第19行：价格=最低价格
^
提示：表“h1”中有一个名为“min_price”的列，但不能从查询的这一部分引用它。

最佳答案

请检查下面的代码，我已经用正确的解释标记了这个问题。

SELECT p.id,
       p.actual_price,
       min_price,
       max_price
FROM   (SELECT Min(price) AS min_price,
               Max(price) AS max_price,
               product_id
        FROM   prices_history
        WHERE  timestamp > '2019-01-01'
        GROUP  BY product_id) AS h1,
       products p ,
       join (SELECT Max(timestamp),
                    price,
                    product_id
             FROM   prices_history
             WHERE  timestamp > '2019-01-01'
             GROUP  BY product_id,
                       price) AS h2
         ON p.id = h2.product_id
            AND price = min_price  -- <= This join is same level with subquery h1.
                                   -- you cannot use min_price here
WHERE  p.id = h1.product_id
GROUP  BY p.id,
          min_price,
          max_price,
          p.actual_price
ORDER  BY p.id;

为了修复这个问题，我还用适当的内部连接替换了遗留逗号分隔连接。

with h1 as
(
    SELECT Min(price) AS min_price,
           Max(price) AS max_price,
           product_id
    FROM   prices_history
    WHERE  timestamp > '2019-01-01'
    GROUP  BY product_id

), h2 as
(
    SELECT Max(timestamp),
            price,
            product_id
     FROM   prices_history
     WHERE  timestamp > '2019-01-01'
     GROUP  BY product_id,
               price

)
SELECT p.id,
       p.actual_price,
       min_price,
       max_price
FROM  h1,
inner join products p
  on p.id = h1.product_id
    join  h2
      ON p.id = h2.product_id
         AND price = min_price
GROUP  BY p.id,
          min_price,
          max_price,
          p.actual_price
ORDER  BY p.id;