通过事件获得最大的连续条纹 [英] Getting maximum sequential streak with events

问题描述

我很难解决这个问题。

我正在寻找一个查询，如果可能的话，在pgAdmin3 v1下运行PostgreSQL 9.6.6。 22.1

I’m looking for a single query, if possible, running PostgreSQL 9.6.6 under pgAdmin3 v1.22.1

我有一个表，该表上有一个日期，并且该日期的每个事件都有一行：

I have a table with a date and a row for each event on the date:

Date        Events
2018-12-10  1
2018-12-10  1
2018-12-10  0
2018-12-09  1
2018-12-08  0
2018-12-07  1
2018-12-06  1
2018-12-06  1
2018-12-06  1
2018-12-05  1
2018-12-04  1
2018-12-03  0

我正在寻找不间断的最长日期序列。在这种情况下，只有 2018-12-08 和 2018-12-03 是没有事件的唯一日期是两个日期分别在 2018-12-08 和今天之间的事件，以及四个日期在 2018之间的事件-12-8 和 2018-12-07 -所以我想要4的答案。

I’m looking for the longest sequence of dates without a break. In this case, 2018-12-08 and 2018-12-03 are the only dates with no events, there are two dates with events between 2018-12-08 and today, and four between 2018-12-8 and 2018-12-07 - so I would like the answer of 4.

我知道我可以将它们与以下内容组合在一起：

I know I can group them together with something like:

Select Date, count(Date) from Table group by Date order by Date Desc

要获取最新的序列，我需要类似以下内容-子查询返回没有事件的最新日期，外部查询将计算该日期之后的日期：

To get just the most recent sequence, I’ve got something like this- the subquery returns the most recent date with no events, and the outer query counts the dates after that date:

select count(distinct date) from Table
where date>
  ( select date from Table
    group by date
    having count (case when Events is not null then 1 else null end) = 0
    order by date desc
    fetch first row only)

但是现在我需要最长的条纹，而不仅仅是最近的条纹。

But now I need the longest streak, not just the most recent streak.

谢谢！

推荐答案

您的直觉是一个很好的观察对象具有零事件的行并对其进行处理。我们可以使用带有窗口函数的子查询来获取零事件日之间的间隔，然后在外部查询中获取我们想要的记录，例如：

Your instinct is a good one in looking at the rows with zero events and working off them. We can use a subquery with a window function to get the "gaps" between zero event days, and then in a query outside it take the record we want, like so:

select * 
from (
  select date as day_after_streak
   , lag(date) over(order by date asc) as previous_zero_date 
   , date - lag(date) over(order by date asc) as difference
   , date_part('days', date - lag(date) over(order by date asc) ) - 1 as streak_in_days
  from dates

  group by date
  having sum(events) = 0 ) t 
where t.streak_in_days is not null
order by t.streak_in_days desc
limit 1

这篇关于通过事件获得最大的连续条纹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！