mysql查询日志表中值的变化

Question

我有一个这样的表:

Index , PersonID  , ItemCount         , UnixTimeStamp
1     , 1         , 1             , 1296000000
2     , 1         , 2             , 1296000100
3     , 2         , 4             , 1296003230
4     , 2         , 6             , 1296093949
5     , 1         , 0             , 1296093295

时间和指数总是在上升。它基本上是一个记录表，用于在每次更改时记录项目计数。我像这样获得每个人的最新 ItemCount:

SELECT *
FROM table a
INNER JOIN
(
 SELECT MAX(index) as i
 FROM table
 GROUP BY PersonID) b
ON a.index = b.i;

我想要做的是获取每个 PersonID 的最新记录，该记录比每个 Person ID 的最新记录至少早 24 小时。然后我想计算这两者之间的 ItemCount 差异，以获得过去 24 小时内每个人的 itemcount 的变化:

personID ChangeInItemCountOverAtLeast24Hours

    1        3
    2        -11
    3        6

我有点不知道下一步该做什么。如何根据各行的最新调整时间戳加入另一个项目计数？

Answer 1

您可能希望添加一些复合索引来帮助查询，这取决于(除其他外)读:写比率。

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 = Groupped
• 后缀 I = Item(组 MaxID 找到的记录本身)