UPDATE年度变化与不连续日期 [英] UPDATE annual changes with discontinuous dates

问题描述

此答案向我展示了如何从数据中获取年度变化：

This answer has shown me how to get annual changes from data:

UPDATE values_table as a
join  values_table as b 
ON b.date_sampled = DATE_SUB(a.date_sampled, INTERVAL 1 YEAR)
set a.annual_change = a.sample_value - b.sample_value

而这个答案向我展示了如何找到最近的日期到 INTERVAL （相对于 NOW（）在这个问题的情况下有3个结果）：

And this answer has shown me how to find the closest date to an INTERVAL (relative to NOW() with 3 results, in this question's case):

SELECT event_id FROM Table ORDER BY ABS( DATEDIFF( EVENT_START_DATE, NOW() ) ) LIMIT 3

日期有变化的年度变化率 SELECT 将最接近的日期设置为所需的 INTERVAL ？

How can the two be combined to get annual rates of change when the dates have holes, SELECTing the closest date to the desired INTERVAL?

我终于有机会尝试Gordon的答案，但我得到查询中的错误（1054）：'where子句'中的未知列'vt.date_sampled'。

I finally had a chance to try Gordon's answer, but I'm getting Error in query (1054): Unknown column 'vt.date_sampled' in 'where clause'.

我也试图做一个在 ON b.date_sampled = 之后的第二个答案的子查询，但它给出了查询中的错误（1064）：'SELECT date_sampled FROM values_table ORDER BY ABS（DATE_SUB（a.date_sampled，INT'。

I also tried to do a subquery based upon the second answer above after ON b.date_sampled =, but it gives Error in query (1064): Syntax error near 'SELECT date_sampled FROM values_table ORDER BY ABS( DATE_SUB(a.date_sampled, INT'.

推荐答案

引用更新语句中的更新表，但可以使用子查询。

MySQL makes it hard to reference the update table in the update statement, but it is possible using subqueries.

我想到一年前的最近日期作为相关子查询的良好候选人：

I think of the "nearest date one year ago" as a good candidate for a correlated subquery:

UPDATE values_table vt
    set vt.annual_change = vt.sample_value - 
                           (select sample_value
                            from (select sample_value, date_sampled
                                  from values_table vt2
                                  where vt2.date_sampled <= DATE_SUB(vt.date_sampled, INTERVAL 1 YEAR) 
                                 ) t
                            order by date_sampled desc
                            limit 1
                           )

我会认为你实际上想要的是至少一年的日期。但是如果你想要最近的日期，相同的想法是有用的：

I would think that you would actually want the date that is at least a year old. But if you want the closest date, the same idea works:

UPDATE values_table vt
    set vt.annual_change = vt.sample_value - 
                           (select sample_value
                            from (select sample_value, date_sampled
                                  from values_table vt2
                                  where vt2.date_sampled <= DATE_SUB(vt.date_sampled, INTERVAL 1 YEAR) 
                                 ) vt2
                            order by ABS( DATEDIFF(vt.date_sampled, vt2.date_sampled))
                            limit 1
                           )

这篇关于UPDATE年度变化与不连续日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！