mysql - 查找每个对象的最长连续时间间隔

Question

有Merchant，他们可以提交Claim。

我需要找到一个Merchant 至少有 1 次 claim 的最长时间段。因此每个 merchant_id 的时间段(以一天的分数为单位)。

所以，例如:

+-------------+-----------+----------------------+----------------------+
| merchant_id |  claim_id |         from         |          to          |
+-------------+-----------+----------------------+----------------------+
|           1 |        11 |  2016-08-15 12:00:00 |  2016-08-17 12:00:00 |
|           1 |        22 |  2016-08-16 12:00:00 |  2016-08-18 12:00:00 |
|           1 |        33 |  2016-08-19 12:00:00 |  2016-08-20 12:00:00 |
|           2 |        66 |  2016-08-15 12:00:00 |  2016-08-17 12:00:00 |
|           2 |        67 |  2016-08-18 12:00:00 |  2016-08-19 12:00:00 |
+-------------+-----------+----------------------+----------------------+

如果 merchant_id = 1，则为 3 天。

如果 merchant_id = 2，则为 2 天。

我该怎么做？

Answer 1

在 MySQL 中单独执行此操作非常复杂。我尝试了一个特定的 merchant_id。如果不检查不同的输入集，我仍然不确定这是否 100% 正确。

不过你可以试试这个，稍后我会解释背后的逻辑。

SELECT 
firstTable.merchant_id,
MAX(TIMESTAMPDIFF(DAY,firstTable.from,secondTable.to)) AS maxConsecutiveDays
FROM 
(
    SELECT 
    A.merchant_id,
    A.from,
    @rn1 := @rn1 + 1 AS row_number
    FROM merchants A
    CROSS JOIN (SELECT @rn1 := 0) var
    WHERE A.merchant_id = 2
    AND NOT EXISTS (
         SELECT 1 FROM merchants B WHERE B.merchant_id = A.merchant_id AND A.idt <> B.idt AND A.`from` BETWEEN B.from AND  B.to 
    )
    ORDER BY A.from
) AS firstTable

INNER JOIN (

    SELECT 
    A.merchant_id,
    A.to,
    @rn2 := @rn2 + 1 AS row_number
    FROM merchants A
    CROSS JOIN (SELECT @rn2 := 0) var
    WHERE A.merchant_id = 2
    AND NOT EXISTS (
         SELECT 1 FROM merchants B WHERE B.merchant_id = A.merchant_id AND A.idt <> B.idt AND A.to BETWEEN B.from AND  B.to 
    )
    ORDER BY A.to
) AS secondTable 
ON firstTable.row_number = secondTable.row_number;

WORKING DEMO

算法:

_{让我们考虑针对特定 merchant_id}

的以下步骤

1. 首先找到所有不在任何范围。我称之为独立起点。让我们说这些开始点存储在集合 S 中。
2. 其次现在找到所有不在任何内部的端点范围。这些是独立的端点，存储在一个集合中E.
3. 按时间升序对集合进行排序。
4. 现在从 1 开始给集合中的每个元素排序。
5. 以匹配的排名编号加入这两个集合。
6. 现在同时枚举这两个集合并求出它们的区别天。然后找到这个差异的最大值。

_{最后一步可以用下面的代码片段来说明:}

---

int maxDiff = 0;

for(int i=0; i< E.size(); i++){
  if((E.get(i) - S.get(i) > maxDiff){
     maxDiff = E.get(i) - S.get(i);
  }
}

maxDiff 是您的输出；

编辑:

为了获得每个商家最长的连续天数，请查看此 DEMO