mysql - 按月计数，仅包含两个日期字段 - IN 和 OUT

Question

无法找到此特定问题的答案。需要按月分组的不同产品的库存总数。源数据具有日期字段，一个用于 IN，一个用于 OUT。特定月份的总计数将包括 IN 日期在特定月份之前的所有行的总和，只要 out 日期为空或特定月份之后的日期即可。

显然，我可以通过使用 WHERE 子句编写 count(distinct ProductID) 查询来获取任何给定月份的计数，该查询指出 IN 日期在我感兴趣的月份(即 2012 年 9 月)和 Out 日期之前为 null 或 9/2012 之后:

Where ((in_date <= '2012-09-30') AND (out_date >= '2012-09-01' or out_date is null))  

如果该产品甚至在 9 月份的某一天属于库存的一部分，我希望它能够计数，这就是为什么过时日期超过 2012 年 9 月 1 日。下面的示例数据。我该如何转动它，而不是查询特定月份:

原始数据 - 每行都是单独的项目

InDate        OutDate        ProductAttr   ProductID2008-04-05    NULL           Blue          1012008-06-04    NULL           Red           1252008-01-01    2012-06-01     Blue          1342008-12-10    2012-10-09     Red           1292009-10-15    2012-11-01     Blue          1532012-10-01    2013-06-01     Red           149

Into this?:

Date          ProductAttr    Count2008-04       Blue           5032008-04       Red            10022008-05       Blue           942008-05       Red            30042008-06       Blue           20002008-06       Red            322

Through grouping I can get the raw data into this format grouped by months:

InDate       OutDate      Value    Count2008-05      2012-05      Blue     1192008-05      2008-06      Red      3332008-05      2012-10      Blue     42008-05      NULL         Red      174882008-06      2012-11      Blue     7112008-06      2013-02      Red      34

If you wanted to know how many products were 'IN' as of Oct. 2012- you would sum the counts of all rows except for 2. Group on Value to keep blue and red separate. Row 2 is ruled out because OutDate is before Oct. 2012.

Thank in advance.

EDIT:

Gordon Linoff's solution works just like I need it to. The only issue I am having now is the size and efficiency of the query, because the part I left out above is that the product attribute is actually located in a different table then the IN/OUT dates and I also need to join a third table to limit to a certain type of product (ForSale for example). I have tried two different approaches and they both work and return the same data, but both take far too long to automate this report:

select months.mon, count(distinct d.productID), d.ProductAttr
from (select '2008-10' as mon union all
  select '2008-11' union all
  select '2008-12' union all
  select '2009-01'
 ) months left outer join
 t
 on months.mon >= date_format(t.Indate, '%Y-%m') and 
    (months.mon <= date_format(t.OutDate, '%Y-%m') or t.OutDate is NULL)

join x on x.product_id = t.product_id and x.type = 'ForSale'
join d on d.product_id = x.product_id and d.type = 'Attribute'

group by months.mon, d.ProductAttr;

还通过添加产品属性和位置/排除的子查询来尝试在没有最后两个连接的情况下执行上述操作 - 这似乎运行得大致相同或稍慢一些:

select months.mon, count(distinct t.productID), (select ProductAttr from d where productid = t.productID and type = 'attribute' limit 1)
from (select '2008-10' as mon union all
  select '2008-11' union all
  select '2008-12' union all
  select '2009-01'
 ) months left outer join
 t
 on months.mon >= date_format(t.Indate, '%Y-%m') and 
    (months.mon <= date_format(t.OutDate, '%Y-%m') or t.OutDate is NULL)

WHERE exists (select 1 from x where x.productid = t.productID and x.type = 'ForSale')

group by months.mon, d.ProductAttr;

有什么想法可以通过我总共需要依赖 3 个源表(1 个仅用于排除)的附加数据来提高效率。提前致谢。

Answer 1

您可以通过生成所需月份的列表来完成此操作。最简单的方法是在 MySQL 中手动执行此操作(尽管在 Excel 中生成代码可以使此操作变得更容易)。

然后使用左连接和聚合来获取您想要的信息:

select months.mon, t.ProductAttr, count(distinct t.productID)
from (select '2008-10' as mon union all
      select '2008-11' union all
      select '2008-12' union all
      select '2009-01'
     ) months left outer join
     t
     on months.mon >= date_format(t.Indate, '%Y-%m') and 
        (months.mon <= date_format(t.OutDate, '%Y-%m) or t.OutDate is NULL)
group by t months.mon, t.ProductAttr;

此版本将所有比较都作为字符串进行。您正在以“月”为粒度进行工作，而 YYYY-MM 格式可以很好地进行比较。

编辑:

您确实需要输出中想要的每个月。如果您每个月都有产品上市，那么您可以这样做:

select months.mon, t.ProductAttr, count(distinct t.productID)
from (select distinct date_format(t.InDate, '%Y-%m') as mon
      from t
     ) months left outer join
     t
     on months.mon >= date_format(t.InDate, '%Y-%m') and
        (months.mon <= date_format(t.OutDate, '%Y-%m) or t.OutDate is NULL)
group by t months.mon, t.ProductAttr;

这会从数据中提取月份。