Mysql query to change values ​​in registration table

Question

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 are always rising. Its basically a log table to register a list item every time it changes. I get the latest 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;

I want to do to get the most recent record for each PersonID, which is at least 24 hours older than the most recent record for each Person identifier. Then I want to change the ItemCount value between the two to get the change in the item for each person in the last 24 hours:

personID ChangeInItemCountOverAtLeast24Hours

    1        3
    2        -11
    3        6

I'm stuck on what to do next. How can I join another element of elements based on the last adjusted timestamp of individual rows?

Answer 1

, , , ( ) read: write.

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

:

• cr=
• ls= ( - 24 )
• G=
• I= ( MaxID)