价格变化时如何正确计算产品的收益?

Question

我有两个数据集:

• 价格:2018年9月产品历史价格变动记录
• 销售:2018年9月产品销售记录使用任何数据整理工具计算每个产品的总收入和每个价格的收入。

谢谢。

价格(示例):

<表类> product_id old_price new_price updated_at tbody><<tr>642700002390009/10/2018 4:37:00点395420360000640009/11/2018 10:59:00点395420364000605009/17/2018 11:54:00是399890919000170009/10/2018 4:35:00点399890917000155009/16/2018 5:09:00是408586167000625009/11/2018 8:51:00是408586162500580009/17/2018 3:35:00是

您可以使用两个子查询来确定特定日期的价格

如果下面找到的行在销售日期之前，则取最后一个new_price，否则取下一个可用日期的old_price。

第二个子选择是不必要的，如果您在所有销售日期之前为每个生产日期设置一个初始行，并且您拥有相同的old_price和new_price。

product表将从prodict_id和updated_at的组合INDEX中获益

SELECT
s.`product_id`
, s.`quantity_ordered`
,IFNULL(
(SELECT `new_price` FROM product p WHERE p.`product_id` = s.`product_id`
AND  s.`ordered_at`>= p.`updated_at` ORDER BY `updated_at` DESC LIMIT 1)
,(SELECT `old_price` FROM product p WHERE p.`product_id` = s.`product_id`
AND  s.`ordered_at`< p.`updated_at` ORDER BY `updated_at` ASC LIMIT 1))
p_price
, s.`ordered_at`
FROM sale s

ordered_at2018-09-09 18:37:002018-09-12 00:59:002018-09-10 13:54:002018-09-12 18:35:002018-09-18 07:09:002018-09-10 10:51:002018-09-19 05:35:00