MySQL：使用一个月的偏移计算表中的数据(MySQL: Calculating Data from Table with One Month Offset)

MySQL：使用一个月的偏移计算表中的数据(MySQL: Calculating Data from Table with One Month Offset)

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

+------------+------------+------------+ | 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月的计算将是：（ Value_A of Feb 2014 / Value_B or Jan 2014 ）或（ Value_A of Feb 2014 / Value_B or Jan 2014 ）。 然后，2014年3月的计算为：（ Value_A of Mar 2014 / Value_B of Feb 2014 ）或（ 300 / 400 ），依此类推。 希望有道理。

如果我只查询特定的月份，那就不难做到，因为我可以在程序中使用CASE语句。 但由于我需要按月分组检索一年的数据，我有点难过。

如果没有Value_B的一个月滞后，我将SQL语句设置为：

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

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.

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.

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`

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?

Thanks for your help.

最满意答案

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) 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)