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
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
1
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
0
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,日期,值)上的索引。
在regtester工作示例。
#1
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
1
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
0
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,日期,值)上的索引。
在regtester工作示例。