mysql - 按多列和时间跨度对 MYSQL 结果进行分组

Question

如何从今天开始倒数，每 7 天为每个 UID 选择一个最近的 NID。

如果今天是7月11日，则如下表

+-----+------------+-----+
| NID |  timestamp | UID |
+-----+------------+-----+
|   1 | 1341719851 |   8 | //July 7
|   2 | 1341115051 |   8 | //July 1
|   3 | 1341547051 |   8 | //July 6
|   4 | 1341719851 |   8 | //July 8
|   5 | 1341979051 |   8 | //July 11
|   6 | 1341806251 |   9 | //July 9
|   7 | 1341460651 |   9 | //July 5
|   8 | 1341892651 |   9 | //July 10
+-----+------------+-----+

将输出:

+-----+------------+-----+
| NID |  timestamp | UID |
+-----+------------+-----+
|   2 | 1341115051 |   8 | //July 1
|   5 | 1341979051 |   8 | //July 11
|   8 | 1341892651 |   9 | //July 10
+-----+------------+-----+

在过去 7 天内，每个用户的最新 NID 为 '5' 和 '8'(在前 7 天内) ，最近的 NID 是 '2'，依此类推...

我假设 Group By 可以解决问题；但我不知道从哪里开始。

更新

根据最重要的答案，这是有效的查询:

SELECT nid, timestamp, uid, weeks_ago
FROM (
    SELECT nid, timestamp, uid, FLOOR(
        (UNIX_TIMESTAMP()- timestamp)/604800
    ) weeks_ago
    FROM `table`
    ORDER BY timestamp DESC
) x
GROUP BY uid, weeks_ago 

Answer 1

select nid, max(timestamp), uid, weeks_ago
from (select nid, timestamp, uid, floor(datediff(now(), from_unixtime(timestamp))/7) weeks_ago
      from mytable) x
group by nid, uid, weeks_ago