MySQL:产品当前最低价格/之前最低价格百分比差异

Question

我有两张 table ，一张存储当前最低价格v_prod_prices和td_prod_prices其中包含所有产品的所有价格更新，下面的函数旨在获取当前最低价格与之前最低价格之间的百分比差异。我几乎已经完成了，只是有一点逻辑我无法弄清楚。

电流输出

该函数目前正在返回 -71.42这是不正确的，因为它使用当前价格 10.00这是正确的，之前的价格是 34.99这是不正确的。

预期/期望输出

它应该返回 -50使用当前价格10.00之前的最低价格为 20.00在这种情况下。

td_prod_prices 中的这些记录将被忽略当获取之前的最低价格时:

id      prod_id price   store   added
-----------------------------------------------
734     426     10.00   1       2016-01-22 02:05:17
819     426     42.99   3       2016-01-24 23:47:51
1052    426     34.99   4       2016-01-27 00:54:50

v_prod_prices

View v_prod_prices仅包含基于 td_prod_prices 的每家商店每种产品的当前最低价格。表。

prod_id price   store   added
-------------------------------------------
426     34.99   4       2016-01-27 00:54:50
426     42.99   3       2016-01-24 23:47:51
426     10.00   1       2016-01-22 02:05:17
426     29.19   2       2016-01-15 14:09:53

td_prod_prices

td_prod_prices包含所有产品价格更新。

id      prod_id price   store   added
-----------------------------------------------
500     426     29.19   2       2016-01-15 14:09:53
660     426     20.00   1       2016-01-15 21:07:38
734     426     10.00   1       2016-01-22 02:05:17
819     426     42.99   3       2016-01-24 23:47:51
1052    426     34.99   4       2016-01-27 00:54:50

SQL

CREATE FUNCTION FN_GET_PROD_PRICE_CHANGE_DIFF (`prodId` BIGINT)
RETURNS DECIMAL(5,2)
BEGIN
    DECLARE `priceDiff` DECIMAL(5,2) DEFAULT 0;
    DECLARE `currentPrice`, `previousPrice` DOUBLE(7,2) DEFAULT 0;
    DECLARE `currentStore`, `previousStore` INT DEFAULT 0;
    DECLARE `currentAdded`, `previousAdded` TIMESTAMP;

    /* Get current price */
    SELECT MIN(`price`)
    , `store`
    , `added`
    INTO `currentPrice`
    , `currentStore`
    , `currentAdded`
    FROM `v_prod_prices`
    WHERE `prod_id` = `prodId`
    LIMIT 0,1;

    /* Get previous price */
    SELECT MIN(`spr`.`spr_price`) AS `price`
    , `spr`.`spr_str_id` AS `store`
    , `spr`.`spr_added` AS `added`
    INTO `previousPrice`
    , `previousStore`
    , `previousAdded`
    FROM `td_prod` `prod`
    JOIN `td_prod_prices` `spr` ON `prod`.`prod_id` = `spr`.`spr_prod_id`
    WHERE NOT EXISTS (
        SELECT 1
        FROM `td_prod_prices` `spr2`
        WHERE `spr`.`spr_prod_id` = `spr2`.`spr_prod_id`
        AND `spr`.`spr_str_id` = `spr2`.`spr_str_id`
        AND `spr`.`spr_added` < `spr2`.`spr_added`
    )
    AND `prod`.`prod_status` = 1
    AND `prod`.`prod_id` = prodId;

    IF `currentPrice` <> 0 AND `previousPrice` <> 0 AND `currentPrice` <> `previousPrice` THEN
        SET `priceDiff` = ((`currentPrice` - `previousPrice`) / `previousPrice`) * 100;
    END IF;

    RETURN `priceDiff`;
END//

Answer 1

最终成功了，在此发布以供遇到类似情况的任何人将来引用。我将其更改为获取当前最低价格记录的id，并在计算产品的先前最低价格时排除大于或等于该id的任何记录。

CREATE FUNCTION FN_GET_PROD_PRICE_CHANGE_DIFF (`prodId` BIGINT)
RETURNS DECIMAL(5,2)
BEGIN
    DECLARE `priceDiff` DECIMAL(5,2) DEFAULT 0;
    DECLARE `currentPriceId` BIGINT(11) DEFAULT 0;
    DECLARE `currentPrice`, `previousPrice` DOUBLE(7,2) DEFAULT 0;

    /* Get current price */
    SELECT `price_id`
    , `price`
    INTO `currentPriceId`
    , `currentPrice`
    FROM `v_prod_prices`
    WHERE `prod_id` = `prodId`
    ORDER BY `price` ASC
    LIMIT 0,1;

    /* Get previous price */
    SELECT `spr`.`spr_price` AS `price`
    INTO `previousPrice`
    FROM `td_prod` `prod`
    JOIN `td_prod_prices` `spr` ON `prod`.`prod_id` = `spr`.`spr_prod_id`
    WHERE NOT EXISTS (
        SELECT 1
        FROM `td_prod_prices` `spr2`
        WHERE `spr`.`spr_prod_id` = `spr2`.`spr_prod_id`
        AND `spr`.`spr_str_id` = `spr2`.`spr_str_id`
        AND `spr`.`spr_added` < `spr2`.`spr_added`
        AND `spr`.`spr_added` > `currentAdded`
        AND `spr2`.`spr_id` < `currentPriceId`
    )
    AND `prod`.`prod_status` = 1
    AND `prod`.`prod_id` = prodId
    AND `spr`.`spr_id` < `currentPriceId`
    ORDER BY price ASC
    LIMIT 0,1;

    IF `currentPrice` <> 0 AND `previousPrice` <> 0 AND `currentPrice` <> `previousPrice` THEN
        SET `priceDiff` = ((`currentPrice` - `previousPrice`) / `previousPrice`) * 100;
    END IF;

    RETURN `priceDiff`;
END//