[vox-tech] MySQL question: making stats out of orders

[Chanoch (Ken) Bloom]

On Tue, 2009-12-01 at 22:14 -0800, Bill Kendrick wrote:
> > It would seem pretty simple to create the analagous table for orders:
> > 
> >     orderstats consisting of productid, date, orders
> > 
> > and every time you complete an order, you run the query:
> > 
> >     insert into orderstats(productid, date, orders) VALUES (###, NOW(),
> >     QTY) on duplicate key update orders=orders+QTY
> 
> Yes, that's great for today-on, but the moment I switch to using this
> new way of recording purchases, there won't be any data.
> 
> That is, unless I start collecting data for the next 90 days BEFORE
> changing how the "top sellers" are determined.
> 
> What I'm looking for is a way to populate a new 'order stats' table
> based on the [last 90 days', or whatever, of] existing orders.

The one time command to fill the orderstats table for the first time
would be

INSERT into orderstats(productid, date, orders)
SELECT
     orderitem.productid, orders.timestamp, SUM(orderitem.qty)
FROM orderitem
  JOIN orders ON orders.id = orderitem.orderid
  WHERE orders.timestamp >= { 90 days ago }
group by orderitem.productid, orders.timestamp

assuming orders.timestamp has one-day granularity

T