mysql query for change in values in a logging table
I have a table like this:
Index , PersonID , ItemCount , UnixTimeStamp
1 , 1 , 1 , 1296000000
2 , 1 , 2 , 1296000100
3 , 2 , 4 , 1296003230
4 , 2 , 6 , 1296093949
5 , 1 , 0 , 1296093295
Time and index always go up. Its basically a logging table to log the itemcount each time it changes. I get the most recent ItemCount for each Person like this:
SELECT *
FROM table a
INNER JOIN
(
SELECT MAX(index) as i
FROM table
GROUP BY PersonID) b
ON a.index = b.i;
What I want to do is get get the most recent record for each PersonID that is at least 24 hours older than the most recent record for each Person ID. Then I want to take the difference in ItemCount between these two to get a change in itemcount for each person over the last 24 hours:
personID ChangeInItemCountOverAtLeast24Hours
1 3
2 -11
3 6
Im sort of stuck with开发者_JAVA技巧 what to do next. How can I join another itemcount based on latest adjusted timestamp of individual rows?
You might want to add some composite indexes to help the query, depending (among others) on the the read:write ratio.
SELECT lsG.PersonID,
lsG.CrItemCount,
lsI.ItemCount,
IF(lsI.ItemCount IS NULL, 0, lsG.CrItemCount - lsI.ItemCount) Change
FROM (
SELECT cr.PersonID, MAX(ls.index) MaxLsIndex, cr.ItemCount CrItemCount
FROM (
SELECT crI.Index, crI.PersonID, crI.ItemCount, crI.UnixTimeStamp
FROM table crI JOIN (
SELECT PersonID, MAX(index) MaxIndex
FROM table
GROUP BY PersonID
) crG ON crI.Index = crG.MaxIndex
) cr LEFT JOIN table ls ON cr.PersonID = ls.PersonID
AND ls.UnixTimeStamp < (cr.UnixTimeStamp - 86400 /*24 hours*/)
GROUP BY cr.PersonID
) lsG LEFT JOIN table lsI ON lsG.MaxLsIndex IS NOT NULL
AND lsG.MaxLsIndex = lsI.index
I named the table aliases:
cr
= Currentls
= Last (most recent record before most recent - 24 hours)- Suffix
G
= Groupped - Suffix
I
= Item (the record itself found by the groups MaxID)
精彩评论