sql—mysql/mariadb中的数据库条目—直到今天

amrnrhlw 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(383)

我昨天问了这个问题，但我似乎说得不够清楚，所以我要补充一些信息来说明一切。
考虑以下两个表：

0_12_table

ID userID text timestamp
1  1      bla  2020-08-07 10:30:00
2  1      blub 2020-08-06 11:30:00
3  1      abc  2020-08-05 09:20:00
4  1      def  2020-08-04 06:13:00
5  2      ghi  2020-08-02 08:05:00
6  2      abc  2020-08-05 10:20:00
7  3      def  2020-08-04 07:13:00
8  4      ghi  2020-08-02 09:05:00
9  5      jkl  2020-08-07 06:30:00
10 5      mno  2020-08-08 08:32:00

12_24_table:

ID userID text timestamp
1  1      bla  2020-08-07 19:30:00
2  1      blub 2020-08-06 21:30:00
3  1      abc  2020-08-05 19:20:00
4  2      def  2020-08-04 16:13:00
5  2      ghi  2020-08-02 18:05:00
6  2      abc  2020-08-05 20:20:00
7  3      def  2020-08-04 17:13:00
8  4      ghi  2020-08-02 19:05:00
9  5      jkl  2020-08-07 20:13:00

基本上，用户可以在00:00到12:00之间和12:01到23:59之间在数据库中添加一个条目。
现在我要奖励他们连续添加条目。每当他们错过了他们的时间表，那个“计数器”被重置为0虽然。。。
在上面给定的数据中，用户ID为1的用户现在会连续3天（在我的时间里，现在是上午9点），但只要是在上午12点之后，并且他没有再输入一个条目，计数器就会设置为0，并且连续结束，因为他错过了添加上午的条目。
用户ID为2、3和4的用户将完全没有条纹。当上午或晚上有一个报名不到时，连胜总是取消的。
当用户id为5的用户在12:01到23:59的时间段内输入时，其连续性为1，这将增加到2。
我希望你能理解其中的逻辑。重要的是，如果他在2天前连得10分，这并不重要。无论何时缺少条目，条纹都会重置为0。因此，当一天早上12点之前没有人进入餐桌，或者晚上23点59分之前没有人进入餐桌，那么连续记录就消失了。它总是以今天为参照，所以它实际上是“直到今天的连续条目”。
到目前为止，我得到的答案似乎是：

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;

然而，到目前为止，我还没有介绍userid，最大的问题是：它只需要最后一次，不管到今天为止是否还有差距。。但是，如前所述，它总是以今天为参照。
我希望有人能帮我。
最后一个重要信息：我使用的是mariadb 10.1.45，因此“with”或“rownum（）”不可用，现在无法更新。
提前谢谢！

sql mysql mariadb gaps-and-islands mariadb-10.1

来源：https://stackoverflow.com/questions/63312729/database-entry-streak-in-mysql-mariadb-until-today

1条答案

按热度按时间

uelo1irk1#

在使用窗口函数的较新版本中，这将非常简单。但您可以调整变量以获得用户的所有条纹：

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

事实证明，对于这个问题，“全局”序列和局部序列一样有效，所以变量的使用仍然很简单。这些变化是对 group by 以及 order by 条款。
然后可以将其用作子查询以获取最大值：

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

注意：没有条纹的用户将被过滤掉。你可以用一个 left join 在外部查询中。但是，您可能真的需要一个包含所有用户的表，而不是两个单独的表，所以我不必费心。

赞(0）回复(0）举报 2021-07-24

我来回答

sql—mysql/mariadb中的数据库条目—直到今天

1条答案

相关问题

热门标签

最新问答