问题描述
我对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：从表中计算数据，偏移一个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！