mysql 为每个客户选择过去 3 天的行

Question

我想在 MySQL 中根据每个客户的最大时间戳为他们选择过去三天的行，但我不确定如何执行此操作。

示例表:

id     | timestamp           | cust_ID
899900 | 2016-04-09 12:00:00 | 500219
899901 | 2016-04-12 16:00:00 | 500219
899902 | 2016-04-14 11:00:00 | 500219
899903 | 2016-04-15 12:00:00 | 500219
909901 | 2016-04-08 16:00:00 | 500230
909902 | 2016-04-14 11:00:00 | 500230
909903 | 2016-04-15 12:00:00 | 500230
939905 | 2016-05-02 19:00:00 | 500240

到目前为止，我有以下内容:

SELECT * 
FROM table 
WHERE timestamp BETWEEN max(timestamp) AND (max(timestamp) - INTERVAL 3 DAY) 
GROUP BY cust_ID

但它给出了错误:

1111 'invalid use of group function'

Answer 1

将表连接到自身的聚合中，找到每个客户的最大时间戳:

SELECT t.*
FROM mytable t
JOIN (SELECT cust_ID, MAX(timestamp) max_timestamp    -- each cust_ID's max timestamp
      FROM mytable
      GROUP BY cust_ID) m
  ON timestamp BETWEEN max_timestamp - INTERVAL 3 DAY AND max_timestamp
  AND t.cust_ID = m.cust_ID           -- join on the specific cust_ID's max timestamp

---

另请注意，BETWEEN 中必须有较小的表达式 first，即

WHERE myColumn BETWEEN smaller AND larger

如果你反过来编码:

WHERE myColumn BETWEEN larger AND smaller  -- don't do this

它仍会编译和执行，但您不会返回任何行。