MySQL：从表中计算数据，偏移一个月 [英] MySQL: Calculating Data from Table with One Month Offset

问题描述

我对SQL还很陌生，所以如果这是一个菜鸟问题，请原谅我。我正在构建一个存储过程，该存储过程从具有按月存储数据的表中查询。这是表格的简化示例：

I am pretty new to SQL, so forgive me if this is a noob question. I am building a stored procedure that queries from a table that has data stored by month. Here is a simplified example of what the table looks like:

+------------+------------+------------+
|    date    |  value_A   |  value_B   |
+------------+------------+------------+
| 2014-01-01 |    100     |    200     |
| 2014-02-01 |    200     |    400     |
| 2014-03-01 |    300     |    600     |
| 2014-04-01 |    400     |    800     |
| 2014-05-01 |    500     |    1000    |
| 2014-06-01 |    600     |    1200    |
| 2014-07-01 |    700     |    1400    |
|    ...     |    ...     |    ...     |
+------------+------------+------------+

在此过程中，我需要计算：（ Value_A / Value_B ） 。但是，Value_B需要滞后一个月。因此，在上面的示例中，2014年2月的计算结果为：（2014年2月的 Value_A / 2014年1月的Value_B ）或（ 200/200 ）。那么，2014年3月的计算结果将是：（2014年3月的 Value_A / 2014年2月的Value_B ）或（ 300/400 ），依此类推。希望这是有道理的。

In the procedure, I need to calculate: (Value_A / Value_B). However, Value_B needs be lagged one month behind. So in the example above, the calculation for February 2014 would be: (Value_A of Feb 2014 / Value_B or Jan 2014) or (200 / 200). The calculation for March 2014 would then be: (Value_A of Mar 2014 / Value_B of Feb 2014) or (300 / 400), and so on and so forth. Hope that makes sense.

如果我只查询一个特定的月份，这不会太困难，因为我可以使用 CASE 语句在过程中。但是由于我需要检索按月分组的一年的数据量，因此我有些困惑。

It wouldn't be too difficult to do if I was only querying a specific month since I could use a CASE statement in the procedure. But since I need to retrieve a year's worth of data grouped by month, I am a bit stumped.

没有Value_B一个月的滞后，我就设置了SQL语句像这样：

Without the one month lag of Value_B, I have the SQL statement set up like:

SELECT ex.`date `, ex.value_A / ex.value_B AS calc
  FROM example_table ex
 WHERE ex.`date` BETWEEN '2014-03-01' AND '2015-02-01'
GROUP BY ex.`date`

我不确定从这里去哪里。我尝试了不同的联接和结合，但没有任何运气。我是否需要使用其各自的日期范围分别查询Value_A和Value_B，然后以某种方式将它们合并在一起进行计算？还是我要比实际难度更大？

I'm not sure where to go from here. I've experimented with different joins and unions but haven't had any luck. Do I need to query Value_A and Value_B separately with their own date range, then merge them together somehow for the calculation? Or am I making this much harder than it really is?

感谢您的帮助。

推荐答案

SELECT t.date, t.value_A/t2.value_B AS calc
FROM table t JOIN table t2 ON t.date = DATE_ADD(t2.date, INTERVAL 1 MONTH)

这篇关于MySQL：从表中计算数据，偏移一个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！