添加变量以计算连续月份

Question

我在Postgres数据库中有一个查询，它结合了客户端订阅。

我想添加一个称为“连续月份”的变量，但我不确定在Postgres中如何添加。

我的原始桌子是这样的：

我希望有一个连续数月的东西，比如：

谢谢你在先进的帮助！

Answer 1

根据tag OP，他显然意识到这是一个缺口和孤岛问题。此查询提取月份和年份信息，以生成按月递增的序列。之后，只需要使用标准差分逻辑来查找不同步的行，并创建标记岛。

with A as (
    select *,
        date_part('year', dt) * 12 + date_part('month', dt)
          - row_number() over (partition by client, product order by dt) as grp
    from T
)
select *,
    row_number()
        over (partition by client, product, grp order by dt) as consecutive_months
from A;

如果一个给定的客户产品在同一个月内有多行是可以接受的，那么在这两个位置将行数（）切换到密集列（）。

https://dbfiddle.uk/?rdbms=postgres_9.5

Answer 2

看来你有自己的差距和岛屿类型的问题。

诀窍是根据每个客户的连接日期计算一些排名。

然后可以根据客户和排名计算序列号。

select client, product, "Date"
, row_number() over (partition by client, daterank order by "Date") as Consecutive_months
from
(
  select "Date", client, product
  , dense_rank() over (partition by client order by "Date") 
    + (DATE_PART('year', AGE(current_date, "Date"))*12 + 
       DATE_PART('month', AGE(current_date, "Date"))) daterank
from raw t
) q
order by "Date", client

client | product | Date       | consecutive_months
-----: | :------ | :--------- | -----------------:
     1 | Sub     | 2020-10-01 |                  1
     1 | Sub     | 2020-11-01 |                  2
     2 | Sub     | 2020-11-01 |                  1
     2 | Sub     | 2020-12-01 |                  2
     1 | Sub     | 2021-01-01 |                  1
     1 | Sub     | 2021-02-01 |                  2
     2 | Sub     | 2021-02-01 |                  1

分贝