时间戳

首页 > 解决方案 > 选择聚合函数的时间戳

sql - 选择聚合函数的时间戳

问题描述

我有两张桌子：

products
id | actual_price

prices_history
id| product_id| price| timestamp

products.id = prices_history.product_id

我需要select products.id, max(prices_history.price) and one latest timestamp for max(prices_history.price).

我尝试了以下方法：

SELECT p.id, max_price, actual_price, max_price_time                       
FROM products p                                                
JOIN (SELECT max(price) as max_price, product_id
      FROM prices_history                              
      WHERE timestamp > '2019-02-01'                             
      GROUP BY product_id) h                             
      ON  h.product_id = p.id
JOIN (SELECT timestamp as max_price_time,  product_id, price                           
      FROM prices_history
      ORDER BY timestamp DESC LIMIT 1) h2                            
      ON  h2.product_id = p.id AND h2.price = max_price;

但它什么也不返回

有趣的是，如果没有第二次 JOIN，我会得到很多结果。所以我猜第二个 JOIN 中的 ORDER BY timestamp DESC LIMIT 1 以某种方式破坏了一切。但是没有 LIMIT 我不知道如何只获得一个价格为 MAX 的最新时间戳？

标签： sqlpostgresql

解决方案

我猜你的问题在于你的第二个子查询没有使用任何过滤器，你基本上总是找到相同的最新时间戳（所有时间戳，不仅仅是那个产品），这与任何一个都不匹配maximum_prices（因此每个产品的最高价格永远不会在最新的时间戳上）。

忽略我之前的回答。这可能是因为我有一段时间没有接触过纯 SQL，但我无法通过在单个查询中分组来实现该逻辑（尝试在 postgress SQLfiddle 中进行）。我本可以发誓我可以按组内的最大值过滤分组查询......无论如何，我尝试重写该查询，现在我明白你为什么使用 suqueries。

它仍然是完全相同的问题（没有过滤器）。将分组添加到您的第二个子查询可以解决问题。SQLfiddle 代码演示：

select p1.id, p1.max_price, p2.max_date 
from ( select id, max(price) as max_price from prod group by id ) as p1  
  inner join  ( select id, price, max(date) as max_date from prod group by id, price ) as p2 
    on p1.id = p2.id and p1.max_price = p2.price

此时在前 2 个子查询之前添加另一个内部连接应该足够简单。

推荐阅读