在Postgresql上的SQL查询中某些条件下的列值递增 [英] Increment column value on certain condition in SQL query on Postgresql

问题描述

如果两周之间的休息时间大于2周，我希望通过几周将行与动物聚集在一起，将行分成1组。

I want aggregate my walks with animals by weeks groupping my rows in 1 group if break between weeks was greater than 2 weeks.

我有一张桌子：

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)

我在密集窗口（lag）和lag（）窗口函数中苦苦挣扎，但是没有运气获得额外的列，因此我应该得到3个不同的值。

I struggle with dense_rank() and lag() window functions, but have no luck to get additional column where I should get 3 distinct values as a result.

这是我想要的输出：

我应该使用窗口功能的哪种组合来获得CAT的两个WALK_NO？
（例如第1周过后，猫等我的时间超过2周）

What combination of window functions should I use to get two WALK_NO for CAT? (becase after week 1 cat waited for me longer that 2 weeks)

推荐答案

使用 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

< a href = https://i.stack.imgur.com/7XBjJ.jpg rel = nofollow noreferrer>

注意：这将重新开始对每只动物的计数-这似乎是您想要做的实际意图。如果您希望各组之间用动物隔开，但要逐渐增加，则问题的定义有些棘手。一种方法是：

Note: This will restart the counting over for each animal -- which seems to be the actual intention of what you want to do. The definition of the problem is a bit tricky if you want the groups separated by animal, but to be incremental. One method is:

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;

这篇关于在Postgresql上的SQL查询中某些条件下的列值递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！