mysql - MySQL 根据 SUM 和 GROUP BY 计算年份之间的销售额百分比变化

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%   |
+------+-----------+-------------+------------+----------+----------+

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

到目前为止我的查询是

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

预先感谢您的任何帮助...

标签： mysqldata-warehousecumulative-summysql-5.6

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;