MySQL根据求和和组计算年间销售额的百分比变化

Question

我有一个数据仓库，其中SELECT (和SUM)查询具有以下输出。

+------+-----------+-------------+------------+
| YEAR | ITEM TYPE | TOTAL_ITEMS | TOTAL_COST |
+------+-----------+-------------+------------+
| 2009 | TYPE-1    |          19 |        330 |
| 2009 | TYPE-2    |           1 |         10 |
| 2009 | TYPE-3    |          11 |        190 |
| 2010 | TYPE-1    |          11 |        220 |
| 2010 | TYPE-2    |           7 |        230 |
| 2010 | TYPE-3    |           3 |        360 |
+------+-----------+-------------+------------+

我的问题是如何创建一个新的列，在该列中计算2009年至2010年(2009年为基数)之间的总成本差异百分比。

所以输出会是这样的：

  +------+-----------+-------------+------------+----------+----------+
| YEAR | ITEM TYPE | TOTAL_ITEMS | TOTAL_COST | ItemDiff | CostDiff |
+------+-----------+-------------+------------+----------+----------+
| 2009 | TYPE-1    |          19 |        330 | 0%       | 0        |
| 2009 | TYPE-2    |           1 |         10 | 0%       | 0        |
| 2009 | TYPE-3    |          11 |        190 | 0%       | 0        |
| 2010 | TYPE-1    |          11 |        220 | -42.11%  | -33.33%  |
| 2010 | TYPE-2    |           7 |        230 | 1000%    | 2200%    |
| 2010 | TYPE-3    |           3 |        360 | -72.73%  | 80.47%   |
+------+-----------+-------------+------------+----------+----------+

项目类型是由不同价格的几个项目组成的类别。我需要计算每个类别的变化，而不是每个项目。

到目前为止，我得到的查询是

SELECT
  date_dim.year,
  item_dim.item_type,
  SUM(fact.total_item)TotalItems,
  SUM(fact.total_cost) AS TotalCost 
FROM fact
  INNER JOIN date_dim
    ON fact.date_key = date_dim.date_key
  INNER JOIN item_dim
    ON fact.item_key = item_dim.item_key
WHERE date_dim.year BETWEEN 2009 AND 2011
GROUP BY date_dim.year,
         item_dim.item_type  

请看一下模式和查询已经构建的下面的小提琴。

http://sqlfiddle.com/#!9/8e53c0/2

这是简化的ERD..。

ERD

提前谢谢你的帮助..。

Answer 1

以下是如何实现这一目标的查询：

使用MySQL公共表表达式(这不会在sqlfiddle上运行)

WITH summary_table AS 
  (SELECT
    substr(date_dim.year,1,4) year,
    item_dim.item_type,
    SUM(fact.total_item) TotalItems,
    SUM(fact.total_cost) AS TotalCost 
  FROM fact
    INNER JOIN date_dim
      ON fact.date_key = date_dim.date_key
    INNER JOIN item_dim
      ON fact.item_key = item_dim.item_key
  WHERE date_dim.year BETWEEN 2009 AND 2011
  GROUP BY date_dim.year,
           item_dim.item_type) 
  SELECT  
     A.*, 
     CASE WHEN (A.TotalItems IS NULL OR B.TotalItems IS NULL OR B.TotalItems=0) THEN 0 ELSE
       (A.TotalItems - B.TotalItems)*100/B.TotalItems END AS ItemDiff,
     CASE WHEN (A.TotalCost IS NULL OR B.TotalCost IS NULL OR B.TotalCost=0) THEN 0 ELSE
        (A.TotalCost - B.TotalCost)*100/B.TotalCost END AS CostDiff
  FROM summary_table A LEFT JOIN summary_table B
   ON A.YEAR=(B.YEAR+1) AND A.ITEM_TYPE=B.ITEM_TYPE;

没有CTE的(参见SQL Fiddle演示)

SELECT 
     A.*, 
     CASE WHEN (A.TotalItems IS NULL OR B.TotalItems IS NULL OR B.TotalItems=0) THEN 0 ELSE
       (A.TotalItems - B.TotalItems)*100/B.TotalItems END AS ItemDiff,
     CASE WHEN (A.TotalCost IS NULL OR B.TotalCost IS NULL OR B.TotalCost=0) THEN 0 ELSE
        (A.TotalCost - B.TotalCost)*100/B.TotalCost END AS CostDiff
FROM (SELECT
    substr(date_dim.year,1,4) year,
    item_dim.item_type,
    SUM(fact.total_item)TotalItems,
    SUM(fact.total_cost) AS TotalCost 
  FROM fact
    INNER JOIN date_dim
      ON fact.date_key = date_dim.date_key
    INNER JOIN item_dim
      ON fact.item_key = item_dim.item_key
  WHERE date_dim.year BETWEEN 2009 AND 2011
  GROUP BY date_dim.year,
           item_dim.item_type) A LEFT JOIN (SELECT
    substr(date_dim.year,1,4) year,
    item_dim.item_type,
    SUM(fact.total_item)TotalItems,
    SUM(fact.total_cost) AS TotalCost 
  FROM fact
    INNER JOIN date_dim
      ON fact.date_key = date_dim.date_key
    INNER JOIN item_dim
      ON fact.item_key = item_dim.item_key
  WHERE date_dim.year BETWEEN 2009 AND 2011
  GROUP BY date_dim.year,
           item_dim.item_type) B
ON A.YEAR=(B.YEAR+1) AND A.ITEM_TYPE=B.ITEM_TYPE;