连接中ON子句中的Aggregare函数

Question

为什么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_price”不存在第19行：且price=min_price^提示：表“h1”中有一个名为“min_price”的列，但无法从查询的这一部分引用该列。

Answer 1

您使用的是“表表达式”。表表达式（如h2）不能引用前一个表达式（如h1）或其列，除非在JOIN子句中。

如果您想使用h2来使用h1，请使用公共表表达式（简称CTEs）。您的查询可以重新措辞为：

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,
  h1.min_price, h1.max_price 
from products p
join h2 ON p.id = h2.product_id
join h1 on h2.price = h1.min_price
       and p.id = h1.product_id
GROUP BY p.id, h1.min_price, h1.max_price, p.actual_price
ORDER BY p.id

Answer 2

除了现有的答案之外，您当然可以使用派生表使用原始查询，只需将隐式连接更改为显式连接：

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
JOIN
  products p
ON p.id = h1.product_id
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
 GROUP BY p.id, p.actual_price, h1.min_price, h2.max_price
 ORDER BY p.id; 

Answer 3

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

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;