mysql - 棘手的查询 : Forecasting Dates

Question

再会。

我在我的 SQL 查询中被这个问题阻止:

鉴于下表:

创建表`Forecasted_Sales_tcl`(
`DEALER_id` varchar(15) 非空，
`SALES_period` 日期非空，
`TYPE` int(2) 非空，
`UNIT_SALES` int(6) DEFAULT NULL,
`HEAD_OFFICE_CODE` varchar(15) DEFAULT NULL
主键(`DEALER_CODE`、`SALES_MONTH`、`TYPE`)
) 引擎=InnoDB 默认字符集=latin1;

http://sqlfiddle.com/#!2/b780c

我需要在 future 几个月内产生可能的销售费用。
例如，我绘制了一个月的出站销售(unit_sales)
(SALES_period)“2012 年 6 月”在一家商店。我期待一些服务费
2012 年 8 月键入 A，2012 年 10 月键入 B，2012 年 12 月键入 C。我也是
在不同的商店在不同的月份有一些对外销售。

我正在尝试生成这样的报告:

期间|费用A |充电B |充电C | store_id
2012-1 月 | X |是 | Z | (ID)
2012-2 月 | : | : | : :
2012-Mar | : | : | : :
2012-4 月 | : | : | : :
2012-5 月 | : | : | : :
2012-六月 | : | : | : :
2012-7 月 | : | : | : :
2012-8 月 | : | : | : :
2012-9 月 | : | : | : :
2012-10 月 | : | : | : :
2012-11 月 | : | : | : :
2012-12 月 | : | : | : :

X 是商店 (id) 的 unit_sales (2 个月前) 总数
Y 是商店 (id) 的 unit_sales (4 个月前) 总数
Z 是商店 (id) 的 unit_sales (6 个月前) 总数

鉴于 sql fiddle 上的上述数据和一些参数:
生成报告:
来自:2012-06
至 : 2013-07

期间 |经销商编号 |充电 X |充电 B |充电 C |
2012-06 | 0001 | 0 | 0 | 0 |
2012-07 | 0001 | 0 | 0 | 0 |
2012-08 | 0001 | 100 | 0 | 0 |
2012-09 | 0001 | 0 | 0 | 0 |
2012-10 | 0001 | 0 | 100 | 0 |
2012-11 | 0001 | 0 | 0 | 0 |
2012-12 | 0001 | 0 | 0 | 100 |
2013-01 | 0001 | 0 | 0 | 0 |
2013-02 | 0001 | 0 | 0 | 0 |
2013-03 | 0001 | 0 | 0 | 0 |
2013-04 | 0001 | 0 | 0 | 0 |
2013-05 | 0001 | 0 | 0 | 0 |
2013-06 | 0001 | 0 | 0 | 0 |
2013-07 | 0001 | 0 | 0 | 0 |

期间 |经销商编号 |充电A |充电 B |充电 C |
2012-06 | 0002 | 0 | 10 | 2 |
2012-07 | 0002 | 0 | 0 | 0 |
2012-08 | 0002 | 10 | 0 | 0 |
2012-09 | 0002 | 18 | 0 | 0 |
2012-10 | 0002 | 5 | 10 | 0 |
2012-11 | 0002 | 0 | 18 | 0 |
2012-12 | 0002 | 0 | 5 | 10 |
2013-01 | 0002 | 0 | 0 | 18 |
2013-02 | 0002 | 0 | 0 | 5 |
2013-03 | 0002 | 0 | 0 | 0 |
2013-04 | 0002 | 0 | 0 | 0 |
2013-05 | 0002 | 0 | 0 | 0 |
2013-06 | 0002 | 0 | 0 | 0 |
2013-07 | 0002 | 0 | 0 | 0 |

On This 10 is for the Sales (2012-04) while 2 is for the sales (2012-02)

  Period  | Dealer Id  |  CHARGE A |  CHARGE B |  CHARGE C |  2012-06  |    0003    |         0 |         0 |         0 | 2012-07  |    0003    |         0 |         0 |         0 | 2012-08  |    0003    |         1 |         0 |         0 | 2012-09  |    0003    |         0 |         0 |         0 | 2012-10  |    0003    |         0 |         1 |         0 | 2012-11  |    0003    |         0 |         0 |         0 | 2012-12  |    0003    |         0 |         0 |         1 | 2013-01  |    0003    |         0 |         0 |         0 | 2013-02  |    0003    |         0 |         0 |         0 | 2013-03  |    0003    |         0 |         0 |         0 | 2013-04  |    0003    |         0 |         0 |         0 | 2013-05  |    0003    |         0 |         0 |         0 | 2013-06  |    0003    |         0 |         0 |         0 | 2013-07  |    0003    |         0 |         0 |         0 |

Master Report

| 2012-06 | 2012-07 | 2012-08 | 2012-09 | 2012-10 | 2012-11 |
经销商 ID |充电A |充电 B |充电 C |充电A |充电 B |充电 C |充电A |充电 B |充电 C |充电A |充电 B |充电 C |充电A |充电 B |充电 C |充电A |充电 B |充电 C |
001 | 0 | 0 | 0 | 0 | 0 | 0 | 100 | 0 | 0 | 18 | 0 | 0 | 0 | 100 | 0 | 0 | 18 | 0 |
002 | 0 | 10 | 2 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | 0 |
003 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |

谢谢您的帮助。

Answer 1

非常感谢 SQLFiddle!这是一个 真讨厌报告你试图在这里实现:D

我能得到的最接近的(在使用体面的 SQL 时)是这样的:

SELECT
  DEALER_ID,
  DATE,
  -- Next 3 rows feature the trick to transpose lines to columns.
  SUM(IF(CHARGE = 'A', UNIT_SALES, 0)) as CHARGE_A,
  SUM(IF(CHARGE = 'B', UNIT_SALES, 0)) as CHARGE_B,
  SUM(IF(CHARGE = 'C', UNIT_SALES, 0)) as CHARGE_C
FROM (

SELECT -- Create a row for each charge A.
  DEALER_id,
  'A' as CHARGE,
  DATE_FORMAT(DATE_ADD(SALES_PERIOD, INTERVAL 2 MONTH), "%Y-%m") as DATE,
  UNIT_SALES
FROM forecasted_sales_tcl

UNION

SELECT -- Create a row for each charge B.
  DEALER_id,
  'B' as CHARGE,
  DATE_FORMAT(DATE_ADD(SALES_PERIOD, INTERVAL 4 MONTH), "%Y-%m") as DATE,
  UNIT_SALES
FROM forecasted_sales_tcl

UNION

SELECT -- Create a row for each charge C.
  DEALER_id,
  'C' as CHARGE,
  DATE_FORMAT(DATE_ADD(SALES_PERIOD, INTERVAL 6 MONTH), "%Y-%m") as DATE,
  UNIT_SALES
FROM forecasted_sales_tcl

) T
WHERE DATE >= "2012-06" AND DATE <= "2013-07"
GROUP BY DEALER_ID, DATE
ORDER BY DEALER_ID, DATE;

这给了你你想要的东西(顺便指出你的假输出中的一些错误:p)，除了它不会生成空行，这就是我为了体面而停下来的地方。

我不是说这是不可能的，但它变得非常丑陋 生成它 .如果你真的想进入它，第一个(也是最困难的)工作是编写一个生成单列输出的 SQL 查询:

DATE
2012-06
2012-07
2012-08
(...)
2013-06
2013-07

这可能是一个很好的问题:p

出于好奇，你可以看看另一个技巧:

SELECT @row := @row + 1 as row, t.* FROM some_table t, (SELECT @row := 0) r

无论如何，如果你真的想得到空行的输出，最简单的方法是用另一个表填充句点。下一个 LEFT JOIN完成工作。

对于主报告，它是完全相同的模式(如果需要，我很乐意为您提供帮助) 但是 我强烈建议您不要在 SQL 中执行此操作，因为它实际上并不是它的工作。移调将非常难看，并且完全不可参数化(拼写？)。

我不知道你用什么来输出报告，但你应该看看适合这个业务的 BI 工具。根据内存，Jasper Reports、BIRT...

好吧，享受 SQL :p