MySQL 不同时期的平均值更新

Question

我希望能够将这 3 个更新查询合并为一个高效且美观的查询，从而更新我的价格表。我知道你们中的一些人可能想知道为什么我要进行此更新，而不是在需要数据时即时执行平均值，您可以继续想知道。

查询 #1 使用 12 个月内价格组的平均价格更新价格表。

查询#2 使用 6 个月内价格组的平均价格更新价格表。

查询#3 使用 3 个月内价格组的平均价格更新价格表。

SET
@p12 = '2017-12-01',
@p6 = '2018-06-01',
@p3 = '2018-09-01',
@p1 = '2019-12-01';

/* AVG PRICE 12 MONTHS */
UPDATE
    prices
JOIN product ON
    product.id = prices.product_id
JOIN(
        SELECT
            pricegroup,
            AVG( price ) as avg_price
        FROM
            price_temp
        WHERE
            sales_date BETWEEN @p12 AND @p1
        GROUP BY
            pricegroup
    ) as a ON
    a.pricegroup = product.pricegroup
SET
    prices.price_12 = a.avg_price
WHERE
    prices.`date` = @p1;

/* AVG PRICE 6 MONTHS */
UPDATE
    prices
JOIN product ON
    product.id = prices.product_id
JOIN(
        SELECT
            pricegroup,
            AVG( price ) as avg_price
        FROM
            price_temp
        WHERE
            sales_date BETWEEN @p6 AND @p1
        GROUP BY
            pricegroup
    ) as a ON
    a.pricegroup = product.pricegroup
SET
    prices.price_6 = a.avg_price
WHERE
    prices.`date` = @p1;

/* AVG PRICE 3 MONTHS */
UPDATE
    prices
JOIN product ON
    product.id = prices.product_id
JOIN(
        SELECT
            pricegroup,
            AVG( price ) as avg_price
        FROM
            price_temp
        WHERE
            sales_date BETWEEN @p3 AND @p1
        GROUP BY
            pricegroup
    ) as a ON
    a.pricegroup = product.pricegroup
SET
    prices.price_3 = a.avg_price
WHERE
     prices.`date` = @p1;

---- 价格表 ----

| id | product_id | date | price | price_3 | price_6 | price_12 |

---- 产品表 ----

| id | pricegroup |

----prices_temp表----

| pricegroup | sales_date | price |

Answer 1

因此，在查看案例后，我重新制作了查询并在 AVG 中使用了案例

UPDATE
    prices
JOIN product ON
    product.id = prices.product_id
JOIN(
        SELECT
            pricegroup,
            avg(CASE WHEN sales_date = @p1 THEN price END) as p1,
            avg(CASE WHEN sales_date BETWEEN @p3 AND @p1 THEN price END) as p3,
            avg(CASE WHEN sales_date BETWEEN @p6 AND @p1 THEN price END) as p6,
            avg(CASE WHEN sales_date BETWEEN @p12 AND @p1 THEN price END) as p12
        FROM
            price_temp
        GROUP BY
            pricegroup
    ) as a ON
    a.pricegroup = product.pricegroup
SET
    prices.price = IF(a.p1 IS NULL, 0, a.p1),
    prices.price_3 = IF(a.p3 IS NULL, 0, a.p3),
    prices.price_6 = IF(a.p6 IS NULL, 0, a.p6),
    prices.price_12 = IF(a.p12 IS NULL, 0, a.p12)