MySQL连续天数/连续天数

Question

我有两张桌子。两者都包含一个用户的条目，一个来自早上，一个来自晚上。

我正在尝试获取天数，因此用户在早上和晚上分别添加了一个条目。只要有一个丢失了，“连线”就会结束--在当天结束。因此，如果用户在一年前是活动的，并且连续有100天，那么它并不介意，它只是“用户从今天起连续多少天在morningTable和eveningTable中添加了条目”。

两个表都有每个条目的时间戳。

举个例子：

morningTable:

ID text timestamp
1  bla  2020-08-07 10:30:00
2  blub 2020-08-06 11:30:00
3  abc  2020-08-05 09:20:00
4  def  2020-08-04 06:13:00
5  ghi  2020-08-02 07:05:00

eveningTable:

ID text timestamp
1  abc  2020-08-07 19:30:00
2  def  2020-08-06 20:45:00
3  ghi  2020-08-05 21:30:00
4  jkl  2020-08-03 20:13:00
5  mno  2020-08-01 17:33:00
6  pqr  2020-07-29 19:19:19

因此，对于这两个表，结果必须是数字3，因为两个表在一行中都有05.08 - 07.08……

我希望你能理解我的情况。

我用ROWNUMBER找到了查找差距等的例子，但是，很难将它与两个表结合起来。

有没有人能帮我讲讲如何用MySQL完成这个任务？

我也想过在PHP中收集所有的数据并在那里做，然而，当数据增长时，用MySQL (数据库端)做会比用PHP做要好得多。

提前感谢！

Answer 1

使用union all和aggregate来组合这些表，以获得这两个表的天数。然后使用间隙和孤岛方法：

with me as (
      select dte
      from ((select date(timestamp) as dte, 1 as morning, 0 as evening
             from morning
            ) union all
            (select date(timestamp) as dte, 0 as morning, 1 as evening
             from evening
            )
           ) me
      group by dte
      having sum(morning) > 0 and sum(evening) > 0
     )
select min(dte), max(dte), count(*)
from (select dte, row_number() over (order by dte) as seqnum
      from me
     ) me
group by dte - interval seqnum day
order by count(*) desc
limit 1;

Here是一个db<>fiddle。

编辑：

实际上，让变量适应这个问题并不是特别困难：

select min(dte), max(dte), count(*)
from (select dte, (@rn := @rn + 1) as seqnum
      from (select dte
            from ((select date(timestamp) as dte, 1 as morning, 0 as evening
                   from morning
                  ) union all
                  (select date(timestamp) as dte, 0 as morning, 1 as evening
                   from evening
                  )
                 ) me
            group by dte
            having sum(morning) > 0 and sum(evening) > 0
            order by dte
           ) d cross join
           (select @rn := 0) params
     ) me
group by dte - interval seqnum day
order by count(*) desc
limit 1;

Answer 2

WITH 
cte1 AS ( SELECT DISTINCT DATE(m.`timestamp`) uni_date                              
          FROM morningTable m
          JOIN eveningTable e ON DATE(m.`timestamp`) = DATE(e.`timestamp`) ),
cte2 AS ( SELECT uni_date, ROW_NUMBER() OVER (ORDER BY uni_date DESC) rn
          FROM cte1 )
SELECT MAX(DATEDIFF( CURRENT_DATE, uni_date ) + 1) output
FROM cte2
WHERE rn = DATEDIFF( CURRENT_DATE, uni_date ) + 1

如果表中包含的行太多，那么使用某个日期值来限制在cte1中检查的日期可能是安全的，该值位于有保证的后一个间隙之前。

PS。很抱歉，这是从MariaDB 10.2.1...

在MariaDB 10.1.45中，下一个查询必须有效：

SELECT DATEDIFF(CURRENT_DATE, MIN(uni_date)) + 1 output
FROM ( SELECT DISTINCT DATE(m.`timestamp`) uni_date                              
       FROM morningTable m
       JOIN eveningTable e ON DATE(m.`timestamp`) = DATE(e.`timestamp`) ) t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM ( SELECT DISTINCT DATE(m.`timestamp`) uni_date                              
                          FROM morningTable m
                          JOIN eveningTable e ON DATE(m.`timestamp`) = DATE(e.`timestamp`) ) t2
                   WHERE t2.uni_date = t1.uni_date - INTERVAL 1 DAY )
  AND ( SELECT DATE(MAX(m.`timestamp`))
        FROM morningTable m
        JOIN eveningTable e ON DATE(m.`timestamp`) = DATE(e.`timestamp`) ) = CURRENT_DATE

如果今天至少有一个表中没有行，则查询将返回NULL -您可以使用COALESCE for来获取零。