代码之家 › 专栏 › 技术社区 › a.kozubenko

如何计算连续日期序列中的一天数?

gaps-and-islands postgresql sql

2

a.kozubenko · 技术社区 · 6 年前

我有一张桌子

id name     created_at
1  name 1   08/01/2017
2  name 2   08/02/2017
3  name 3   08/03/2017
4  name 4   08/05/2017
5  name 5   08/06/2017
6  name 6   08/07/2017
7  name 7   08/10/2017
8  name 8   08/12/2017

我需要添加一个列,其中be rank代表所有行,但如果它们是每天创建的。

结果如下

id name     created_at   days_on
1  name 1   08/01/2017   1
2  name 2   08/02/2017   2
3  name 3   08/03/2017   3
4  name 4   08/05/2017   1
5  name 5   08/06/2017   2
6  name 6   08/07/2017   3
7  name 7   08/10/2017   null
8  name 8   08/12/2017   null

2 回复 | 直到 6 年前

1

klin 6 年前

有 many answers 描述解决类似问题的典型方法,其中还可以找到下面使用的技术的说明。

select 
    id, name, created_at, 
    case when count(*) over wa > 1 then row_number() over wo end as rank
from (
    select 
        id, name, created_at, 
        sum(first) over w as part
    from (
        select *, (lag(created_at) over w+ 1 is distinct from created_at)::int as first
        from my_table
        window w as (order by id)
        ) s
    window w as (order by id)
    ) s
window 
    wa as (partition by part),
    wo as (partition by part order by id);

DbFiddle.

2

0

Gordon Linoff 6 年前

这是群岛问题的一个变种。让我用 lag() 要定义组:

() 得到前一天
获取组的累积和
row_number() 指定最终值

其工作原理如下:

select id, name, created_at,
       (case when count(*) over (partition by grp) > 1
             then row_number() over (partition by grp order by id)
        end) as days_on
from (select t.*,
             sum( (prev_ca <> created_at - interval '1 day')::int ) as grp
      from (select t.*,
                   lag(created_at) over (order by id) as prev_ca
            from t
     ) t;