sql - 使用 JOIN 和限制构建 SQL 查询

Question

请帮助我构建 PostgreSQL 查询。有 2 个表:products(id, title) 和 prices(id, product_id, price_type, moment, value)

moment - 时间戳，可以是过去也可以是 future

假设 price_type 只有两个选项:retail 或 purchase

但一个产品可能有多个不同时刻的零售价。

我需要选择所有具有实际零售价和采购价的产品，其中 moment 比现在少。

我能做到

SELECT 
      products.id, 
      products.title_translations AS title,
      retail_prices.moment AS ret_moment,
      pur_prices.value AS purchase,
      retail_prices.value AS retail
FROM products 
LEFT OUTER JOIN prices AS pur_prices ON products.id=pur_prices.product_id AND pur_prices.price_type='purchase' AND pur_prices.moment<current_timestamp
LEFT OUTER JOIN prices AS retail_prices ON products.id=retail_prices.product_id AND retail_prices.price_type='retail' AND retail_prices.moment<current_timestamp
ORDER BY products.id;

它有效，但返回具有所有价格的产品，但我只需要最后的价格(按时刻)。

Answer 1

只需使用ROW_NUMBER 即可找到当前时间之前的最后价格

with last_prices as (
    SELECT 
          products.id, 
          products.title_translations AS title,
          prices.moment,
          prices.value,
          prices.price_type,
          ROW_NUMBER() OVER (PARTITION BY product_id, price_type 
                             ORDER BY moment DESC) as rn
    FROM products 
    LEFT JOIN prices
      ON products.id = prices.product_id 
    WHERE moment < now() 
  )
SELECT id, title, 
       MAX(CASE WHEN price_type = 'retail'
                THEN moment
           END) as retail_moment,
       MAX(CASE WHEN price_type = 'retail'
                THEN value
           END) as retail_price,
       MAX(CASE WHEN price_type = 'purchase'
                THEN moment
           END) as purchase_moment,
       MAX(CASE WHEN price_type = 'purchase'
                THEN value
           END) as purchase_price
FROM last_prices     
WHERE rn = 1
GROUP BY id, title
ORDER BY id