查询从特定日期开始计算数据+/- 4周

Question

我必须计算一个特定的日期(创建日期)，销售前4周和4周后(需要计算的数据是每周)

我的阻止程序是每周计算4周前的项目列表，after.So也是一样的，我必须执行这个查询8次，更改日期

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

我必须为日期-1，日期-2.，日期-4，然后date+1，.，date+4 (有时我必须做+/- 12周)并且为views_table做同样的事情，所以它就像48查询的10K产品，它会在工作台上生成bug。还有别的办法吗？例如，使用TMP表，以及如何编写查询。谢谢。

Answer 1

我已经像这样使用了您的查询(下面)。我使用的表是P_Master和P_Master_Cat (我可以使用第一个表，因为其中存在所有信息)。当我运行该查询时，我遇到以下问题：“错误代码: 1111.组函数的无效使用”

SELECT p.sku_conf  AS product
 , IFNULL(SUM(IF(t.date >= i.dt + INTERVAL -4 WEEK AND t.date < i.dt + INTERVAL -3 WEEK,t.ItemID,NULL)),0) AS `d-4`
 , IFNULL(SUM(IF(t.date >= i.dt + INTERVAL -3 WEEK AND t.date < i.dt + INTERVAL -2 WEEK,t.ItemID,NULL)),0) AS `d-3`
 , IFNULL(SUM(IF(t.date >= i.dt + INTERVAL -2 WEEK AND t.date < i.dt + INTERVAL -1 WEEK,t.ItemID,NULL)),0) AS `d-2`
 , IFNULL(SUM(IF(t.date >= i.dt + INTERVAL -1 WEEK AND t.date < i.dt + INTERVAL  0 WEEK,t.ItemID,NULL)),0) AS `d-1`
 , IFNULL(SUM(IF(t.date >= i.dt + INTERVAL  0 WEEK AND t.date < i.dt + INTERVAL  1 WEEK,t.ItemID,NULL)),0) AS `d+0`
 , IFNULL(SUM(IF(t.date >= i.dt + INTERVAL  1 WEEK AND t.date < i.dt + INTERVAL  2 WEEK,t.ItemID,NULL)),0) AS `d+1`
FROM ( SELECT '2016-01-06' AS dt ) i
JOIN P_Master_Cat p
  ON p.sku_conf IN 
  ('OE599EL9',
  'OE599EL86',
  'OE599EL90',
  'OE599EL75')
LEFT JOIN P_Master t
  ON t.SKUConf = p.sku_conf
  AND t.date >= i.dt + INTERVAL -6 WEEK
  AND t.date <  i.dt + INTERVAL  6 WEEK
GROUP BY p.sku_conf

Answer 2

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

Answer 3

只需使用一个自联接来获得这里的结果

select t.date,sum(t1.sales) 
 from Sales_table t join Sales_table t1 on
  t1.date between date_add(t.date, interval -4 week) and
     date_add(t.date, internal 4 week)

使用此查询可获得四周前和之后的销售总额。

如果您希望获得特定产品的详细信息，请使用以下查询(对于产品明智和)

select t.date,sum(t1.sales) 
 from Sales_table t join Sales_table t1 on
  t.product=t1.product and
  t1.date between date_add(t.date, interval -4 week) and
     date_add(t.date, internal 4 week)