MySQL:在表中最老的日期和最新的日期之间更改列值

Following table in MySQL:

在MySQL下表:

id| value | date

There are multiple records for the same ID for different dates, i want for each "id" to calculate the difference in the column "value" between the records with oldest and newest "date" in my table.

对于不同的日期，有多个相同的ID记录，我希望每个“ID”计算表中最古老和最新的“date”记录之间的列“value”的差异。

In other words, display something like (remember value is the column "Value"):

换句话说，显示如下内容(记住value是“value”一栏):

id | Most Recent Value - Oldest Value

Any help/tips would be more than welcome! So far, I have tried to self-join the table 2 times to take the records for both first and last date using min() & max() but no luck, i am getting NULL as "Value" for min() dates.

任何帮助/提示都将非常受欢迎!到目前为止，我已经尝试使用min()和max()自连接表2次，以获取第一次和最后一次约会的记录，但没有运气，我得到了NULL作为min()日期的“值”。

SELECT t.id, (t2.value - t3.value) AS Difference
FROM table t 

LEFT JOIN 
    (SELECT t2.id, t2.value, MAX(t2.date) as TopDate
    FROM table t2
    GROUP BY t2.id) AS LatestValue
ON LatestValue.TopDate = t.date AND LatestValue.id = t.id

LEFT JOIN 
    (SELECT t3.id, t3.value, MIN(t3.date) as BotDate
    FROM table t3
    GROUP BY t3.id) AS FirstValue
ON FirstValue.BotDate = t.date AND FirstValue.id = t.id

GROUP BY t.id;

3 个解决方案

#1

Try this:

试试这个:

  select t1.id, t1.`value` - t2.`value` as res from 
      (
          select t.id, `value` from t
          inner join (select id,   max(`date`) dt   from t group by id) mx
          on t.id = mx.id and t.`date` = mx.dt
      )t1
      inner join (
          select t.id, `value` from t
          inner join (select id,   min(`date`) dt   from t group by id) mn
          on t.id = mn.id and t.`date` = mn.dt
      ) t2
  on t1.id = t2.id

Side note: try to not use words like value, date as column names

附注:尽量不要使用值、日期等字作为列名。

#2

In your inner queries the way group by was used if wrong. IT won't get you the correct value. In your outer queries also you don't need a group by. If you want to do it the way you were doing, then the Correct query is:-

在您的内部查询中，使用的方式组如果错误。它不会得到正确的值。在外部查询中，也不需要组by。如果您想按照您刚才的方式进行，那么正确的查询是:-

SELECT t.id, (LatestValue.value - FirstValue.value) AS Difference
FROM table t 
LEFT JOIN 
  (
   SELECT id,value, date as TopDate
   FROM TABLE a
   INNER JOIN
    (
    SELECT t2.id, MAX(t2.date) as TopDate
    FROM table t2
    GROUP BY t2.id 
    ) b
   ON a.id=b.id and a.date=b.TopDate
  ) LatestValue

ON LatestValue.TopDate = t.date AND LatestValue.id = t.id

LEFT JOIN 
  (
   SELECT id,value, date as BotDate
   FROM table a
   INNER JOIN
    (
    SELECT t3.id,MIN(t3.date) as BotDate
    FROM table t3
    GROUP BY t3.id 
    ) b 
   ON a.id=b.id and a.date=b.BotDate
  ) FirstValue
ON FirstValue.BotDate = t.date AND FirstValue.id = t.id;

Let me know if this works or if you have any questions.

如果有问题，请告诉我。

#3

Here's a solution that scans the table only once. It uses MySQL variables to work around the lack of window functions.

这里有一个只扫描一次表的解决方案。它使用MySQL变量来解决窗口函数的不足。

select  id
,       first_val - last_val
from    (
        select  case when id <> @id and @id <> -1 then @id end as id
        ,       case when id <> @id then @first_val end as first_val
        ,       case when id <> @id then @last_val end as last_val
        ,       @first_val := case when id <> @id then `value` else @first_val end
        ,       @last_val := `value`
        ,       @id := id
        from    (
                select  *
                from    (
                        select  *
                        from    YourTable
                        order by
                                id desc
                        ,       date
                        ) sorted
                union all
                select  -1
                ,       null
                ,       null
                ) with_closer
        cross join (select @id := -1, @first_val := 0, @last_val := 0) r
        ) calc
where   id is not null

This should perform well, especially with an index on (id desc, date, value).

这应该执行得很好，特别是在(id desc，日期，值)上的索引。

Working example at regtester.

在regtester工作示例。

#1