sql - 在 Postgresql 上的 SQL 查询中的特定条件下增加列值

Question

如果周之间的休息时间超过 2 周，我希望按周将我与动物的步行汇总为 1 组。

我有我的 table :

Create table test.walk (animal text, week integer)

我想分组的每次步行都有一行:

insert into test.walk values ('DOG', 2)
insert into test.walk values ('DOG', 3)
insert into test.walk values ('DOG', 4)
insert into test.walk values ('CAT', 1)
insert into test.walk values ('CAT', 1)
insert into test.walk values ('CAT', 11)
insert into test.walk values ('CAT', 12)
insert into test.walk values ('CAT', 13)

我努力使用dense_rank() 和lag() 窗口函数，但没有运气获得额外的列，因此我应该获得3 个不同的值。

这是我想要的输出:



我应该使用什么窗口函数组合来为 CAT 获得两个 WALK_NO？
(因为第 1 周后，猫等我的时间更长了 2 周)

Answer 1

使用 lag()和累计金额:

with query as (
select w.*,
       sum(case when week < prev_week + 2 then 0 else 1 end) over (partition by animal order by week) as grp
from (select w.*,
             lag(week) over (partition by animal order by week) as prev_week
      from test.walk w
     ) w
)
select
    animal, week, grp,
    dense_rank() over (order by animal, grp) as grp2
from query

注意:这将重新开始对每只动物进行计数——这似乎是您想要做的事情的实际意图。如果您希望以动物分隔的组，但要递增，则问题的定义有点棘手。一种方法是:

select w.*,
       sum(case when prev_week = week then 0 else 1 end) over (order by min_week, animal, week) as grp
from (select w.*,
             lag(week) over (partition by animal order by week) as prev_week,
             min(week) over (partition by animal) as min_week
      from test.walk w
     ) w;