mysql:获取带有版本的产品表的每日平均价格

Question

我的产品表:

ProductId(inc.key) | Price | VersionCreatedDate | MainProductId
1                  | 15    | 1-11-2016          | 1
2                  | 20    | 1-11-2016          | 2
3                  | 30    | 1-11-2016          | 3
4                  | 10    | 2-11-2016          | 1 -> mainProductId 1 changed price(-5$)
5                  | 20    | 3-11-2016          | 3 -> mainProductId 3 changed price(-10$) 
6                  | 30    | 4-11-2016          | 3 -> mainProductId 3 changed price(+10$)

我想像这样显示输出

Date      | AvgPrice 
1-11-2016 | 21.67  ((15+20+30)/3) 
2-11-2016 | 20     ((10+20+30)/3)
3-11-2016 | 16.67  ((10+20+20)/3)
4-11-2016 | 20     ((10+20+30)/3)

如何使用sql代码获取输出？

Answer 1

假设您有一个包含您需要的所有日期的日历表。您有一个 main_products 表，其中 MainProductId 作为主/唯一键。以下查询应返回 2016 年 10 月每天的平均价格。

select sub.date, avg(sub.Price) as Price
from (
    select 
        c.date,
        m.MainProductId,
        (
            select p.Price
            from products
            where p.MainProductId = m.MainProductId
              and p.VersionCreatedDate < c.date + interval 1 day
            order by p.VersionCreatedDate desc
            limit 1
        ) as Price
    from callendar c
    cross join main_products m
    where c.date between '2016-10-01' and '2016-10-31'
) sub
group by sub.date
order by sub.date

子查询(派生表别名为 sub)返回范围内所有日期以及 main_products 表中所有“主要产品”的组合。特定日期的每个“主要产品”的最近价格是在子查询(SELECT 子句中的相关子查询)中使用 ORDER BY 和 LIMIT 1 计算的。这允许我们按日期对子查询结果进行分组并计算每个日期的平均价格。

甚至可以消除派生表，并希望mysql可以使用索引来GROUP BY日期，而不是在临时表上工作:

select c.date, avg((
        select p.Price
        from products
        where p.MainProductId = m.MainProductId
          and p.VersionCreatedDate < c.date + interval 1 day
        order by p.VersionCreatedDate desc
        limit 1
    )) as Price
from callendar c
cross join main_products m
where c.date between '2016-10-01' and '2016-10-31'
group by c.date
order by c.date

我不知道该查询是否可以有效执行(特别是 mysql 是否可以)。但是，您至少应该有以下索引:callendar(date), products(MainProductId, VersionCreatedDate)