postgresql - 在 PostgreSQL 中查找日期序列

Question

我试图在我的数据中找到客户的最大天数。我想了解特定客户制作的最大天数是多少。如果有人在 25/8/16 AND 26/08/16 AND 27/08/16 AND 01/09/16 AND 02/09/16 进入我的应用程序 - 最大序列将为 3 天(25,26, 27).

最后(输出)我想得到两个字段:custid |最大日序列

我的数据表中有以下字段:custid |订单日期(时间戳)

例如:

custid  orderdate
1   25/08/2007
1   03/10/2007
1   13/10/2007
1   15/01/2008
1   16/03/2008
1   09/04/2008
2   18/09/2006
2   08/08/2007
2   28/11/2007
2   04/03/2008
3   27/11/2006
3   15/04/2007
3   13/05/2007
3   19/06/2007
3   22/09/2007
3   25/09/2007
3   28/01/2008

我正在使用 PostgreSQL 2014。

谢谢

---

尝试:

select custid, max(num_days) as longest 
from ( 
       select custid,rn, count (*) as num_days 
       from (
           select custid, date(orderdate), 
                  cast (row_number() over (partition by custid order by date(orderdate)) as varchar(5)) as rn 
           from table_
       ) x group by custid, CURRENT_DATE - INTERVAL rn|| ' day'
 ) y group by custid

Answer 1

尝试:

SELECT custid, max( abc ) as max_sequence_of_days
FROM (
  SELECT custid, yy, count(*) abc
  FROM (
    SELECT * ,
          SUM( xx ) OVER (partition by custid order by orderdate ) yy
    FROM (
      select * ,
          CASE WHEN 
              orderdate - lag( orderdate ) over (partition by custid order by orderdate ) 
              <= 1
         THEN 0 ELSE 1 END xx
      from mytable
    ) x
  ) z
  GROUP BY custid, yy
) q
GROUP BY custid

演示:http://sqlfiddle.com/#!15/00422/11

---

===== 编辑 ===========

---

Got "operator does not exist: interval <= integer"

这意味着orderdate列的类型为 timestamp , 不是 date .
在这种情况下，您需要使用 <= interval '1' day条件而不是 <= 1 :

请查看此链接:https://www.postgresql.org/docs/9.0/static/functions-datetime.html了解有关 PostgreSQL 中日期算法的更多信息

请看这个演示: http://sqlfiddle.com/#!15/7c2200/2

SELECT custid, max( abc ) as max_sequence_of_days
FROM (
  SELECT custid, yy, count(*) abc
  FROM (
    SELECT * ,
          SUM( xx ) OVER (partition by custid order by orderdate ) yy
    FROM (
      select * ,
          CASE WHEN 
              orderdate - lag( orderdate ) over (partition by custid order by orderdate ) 
              <= interval '1' day
         THEN 0 ELSE 1 END xx
      from mytable
    ) x
  ) z
  GROUP BY custid, yy
) q
GROUP BY custid