leetcode-1251-平均售价

链接:  1251. 平均售价 - 力扣（LeetCode）

前提条件:

查看代码

 CREATE TABLE Prices (
    product_id INT,
    start_date DATE,
    end_date DATE,
    price INT
);

-- 插入数据
INSERT INTO Prices (product_id, start_date, end_date, price)
VALUES
    (1, '2019-02-17', '2019-02-28', 5),
    (1, '2019-03-01', '2019-03-22', 20),
    (2, '2019-02-01', '2019-02-20', 15),
    (2, '2019-02-21', '2019-03-31', 30);

CREATE TABLE UnitsSold (
    product_id INT,
    purchase_date DATE,
    units INT
);
INSERT INTO UnitsSold (product_id, purchase_date, units)
VALUES
    (1, '2019-02-25', 100),
    (1, '2019-03-01', 15),
    (2, '2019-02-10', 200),
    (2, '2019-03-22', 30);

思路:

1. 先通过连表条件筛选，得到想要的内容
2. 设计两列相乘，其中一列相加，所以子查询实现。

优化:

实现:

查看代码

 # Write your MySQL query statement below
SELECT
        grouped.product_id, ROUND(SUM(sales) / SUM(units), 2) average_price
FROM
    (SELECT
            p.product_id, p.price * u.units sales, u.units units
    FROM
            Prices p JOIN  UnitsSold u ON p.product_id = u.product_id
            AND u.purchase_date BETWEEN p.start_date AND p.end_date
    ) grouped
GROUP BY
    grouped.product_id;