困难的MySQL查询-获取日期之间的最大差异

Question

我有一个如下形式的MySQL表

account_id | call_date
1            2013-06-07
1            2013-06-09  
1            2013-06-21
2            2012-05-01
2            2012-05-02
2            2012-05-06

我想要编写一个MySQL查询，它将为每个account_id在call_date中的连续日期之间获得最大差异(以天为单位)。因此，对于上面的示例，此查询的结果将为

account_id | max_diff
1            12
2            4

我不知道该怎么做。在MySQL查询中可以做到这一点吗？

我可以使用datediff(max(call_date),min(call_date))，但这将忽略第一次调用日期和最后一次调用日期之间的日期。我需要一些方法来获取每个account_id的每个连续call_date之间的datediff()，然后找到其中的最大值。

Answer 1

我相信fp的回答会更快，但只是为了好玩……

SELECT account_id
     , MAX(diff) max_diff
  FROM 
     ( SELECT x.account_id
            , DATEDIFF(MIN(y.call_date),x.call_date) diff
         FROM my_table x 
         JOIN my_table y 
           ON y.account_id = x.account_id 
          AND y.call_date > x.call_date 
        GROUP 
           BY x.account_id
            , x.call_date
     ) z
 GROUP
    BY account_id;

Answer 2

CREATE TABLE t
    (`account_id` int, `call_date` date)
;

INSERT INTO t
    (`account_id`, `call_date`)
VALUES
    (1, '2013-06-07'),
    (1, '2013-06-09'),
    (1, '2013-06-21'),
    (2, '2012-05-01'),
    (2, '2012-05-02'),
    (2, '2012-05-06')
;

select account_id, max(diff) from (
select
account_id,
timestampdiff(day, coalesce(@prev, call_date), call_date) diff,
@prev := call_date
from
t
, (select @prev:=null) v
order by account_id, call_date
) sq 
group by account_id

| ACCOUNT_ID | MAX(DIFF) |
|------------|-----------|
|          1 |        12 |
|          2 |         4 |

• 看到它在sqlfiddle

中实时工作

Answer 3

如果您在account_id, call_date上有一个索引，那么您可以在不使用变量的情况下高效地执行此操作：

select account_id, max(call_date - prev_call_date) as diff
from (select t.*,
             (select t2.call_date
              from table t2
              where t2.account_id = t.account_id and t2.call_date < t.call_date
              order by t2.call_date desc
              limit 1
             ) as prev_call_date
      from table t
     ) t
group by account_id;