MySQL，选择以包括前几周

Question

我正在使用下面的SQL从两个MySQL数据库表中获取每周的价格历史：

SELECT ROUND(MIN(ph.price), 0) AS min_price, WEEK(ph.added) AS added
FROM products_data pd JOIN products_history ph ON pd.id = ph.product_id
WHERE ph.price > 0
GROUP BY added
ORDER BY added

一切都很好，除了products_history表只包含价格更改时的条目。

如果 A 周的价格

变化创造了 A 周的最低min_price，那么一切都很好。

但是，如果价格变化发生在 A 周前 1+ 周，则查询不会检测到它，因此检索到的 A 周的最低min_price可能无法正确:(

例如，现在正在发生的事情：

Week 13: Price change so min_price for product A = 4.78,
query returns 4.78 (correct)
Week 14: Price change so min_price for product B = 4.74,
query returns 4.74 (correct)
Week 15: Price change so min_price for product C = 4.79,
query returns 4.79 (wrong, should be 4.74 since that's still the price for product B)
Week 16: Price change so min_price for product C = 4.75,
query returns 4.75 (wrong, should be 4.74 since that's still the price for product B)
Week 17: No price changes,
query returns nothing...  (wrong, should be 4.74 since that's still the price for product B)
Week 18: Price change so min_price for product B = 4.77,
query returns 4.77 (wrong, should be 4.75 since that's still the price for product C)

关于如何使查询检索每周最低min_price的任何想法，即使更改发生得更早？

---

products_data具有以下列：

id, name, category
2434, 'Product A', 'Bits'
3437, 'Product B', 'Bits'

products_history具有以下列：

id, product_id, price, added
4311, 2434, 4.74, 2019-05-15 22:19:50
2434, 3437, 4.78, 2019-05-15 22:19:59

---

基于戈登·利诺夫(Gordon Linoff(以下正确答案的工作版本：

SELECT id, yw.yyyyww,
       (SELECT ph2.price
        FROM products_history ph2
        WHERE ph2.price > 0 AND
              YEARWEEK(ph2.added) <= yw.yyyyww AND ph2.product_id = pd.id
        ORDER BY YEARWEEK(ph2.added) DESC,  -- put the most recent values first
                 ph2.price ASC  -- by lowest price
        LIMIT 1
       ) AS min_price
FROM products_data pd CROSS JOIN
     (SELECT DISTINCT YEARWEEK(ph.added) AS yyyyww
      FROM products_history ph
     ) yw

Answer 1

如果我理解正确，您希望每周的每个产品都有一个值。 如果是这样，请使用cross join生成行，然后 - 在MySQL中 - 您可以使用相关的子查询来获取价格

SELECT product_id, yw.yyyyww,
       (SELECT ph2.price
        FROM products_history ph2
        WHERE ph2.price > 0 AND
              YEARWEEK(ph2.added) <= yw.yyyyww
        ORDER BY YEARWEEK(ph2.added) DESC,  -- put the most recent values first
                 ph2.price ASC  -- by lowest price
       ) as min_price
FROM products_data pd CROSS JOIN
     (SELECT DISTINCT YEARWEEK(ph.added) as yyyyww
      FROM products_history ph
     ) yw