java2dev java2dev - 5 months ago 9
SQL Question

Query to calculate data +/- 4 weeks from a specific date

I have to calculate for a specific date (creation date) the sales 4 weeks before and 4 weeks after (the data a need to calculate is for each week)

My blocker is to calculate the item list 4 weeks before week by week and the same for weeks after.So I have to do this query 8 times changing the date

SELECT
product,
SUM(sales)
FROM
Sales_table
WHERE
DATA <= date - 1
AND product IN (
product1,
product2,
....,
product10000
);


I have to do it for date-1, date-2..., date-4 and then date+1,...,date+4 (sometimes I have to do it for +/- 12 weeks) and do the same thing for views_table so it's like 48 queries for 10K products and it copuld generate bug in workbench. There exits any other way to do this? with TMP tables for example, and how could I write the query. Thanks.

Answer
SELECT p.id   AS product
     , IFNULL(SUM(IF(t.creation_date >= i.dt + INTERVAL -4 WEEK AND t.creation_date < i.dt + INTERVAL -3 WEEK,t.sales,NULL)),0) AS `d-4`
     , IFNULL(SUM(IF(t.creation_date >= i.dt + INTERVAL -3 WEEK AND t.creation_date < i.dt + INTERVAL -2 WEEK,t.sales,NULL)),0) AS `d-3`
     , IFNULL(SUM(IF(t.creation_date >= i.dt + INTERVAL -2 WEEK AND t.creation_date < i.dt + INTERVAL -1 WEEK,t.sales,NULL)),0) AS `d-2`
     , IFNULL(SUM(IF(t.creation_date >= i.dt + INTERVAL -1 WEEK AND t.creation_date < i.dt + INTERVAL  0 WEEK,t.sales,NULL)),0) AS `d-1`
     , IFNULL(SUM(IF(t.creation_date >= i.dt + INTERVAL  0 WEEK AND t.creation_date < i.dt + INTERVAL  1 WEEK,t.sales,NULL)),0) AS `d+0`
     , IFNULL(SUM(IF(t.creation_date >= i.dt + INTERVAL  1 WEEK AND t.creation_date < i.dt + INTERVAL  2 WEEK,t.sales,NULL)),0) AS `d+1`
  FROM ( SELECT '2016-04-01' AS dt ) i
  JOIN products p
    ON p.id IN 
       ( product1
       , product2
       , ...
       , product10000
       )
  LEFT
  JOIN Sales_table t
    ON t.product_id = p.id
   AND t.creation_date >= i.dt + INTERVAL -6 WEEK
   AND t.creation_date <  i.dt + INTERVAL  6 WEEK
 GROUP BY p.id
Comments