时间戳

首页 > 解决方案 > 在 PostgreSQL 中基于多个字段更改（包括“不可见”）对行进行编号

sql - 在 PostgreSQL 中基于多个字段更改（包括“不可见”）对行进行编号

问题描述

我查看了以前的主题，但我无法实现我想要的。

我有一张这样的桌子：

id   status     update_date
---  ---        ---
A    PENDING    2020-11-01
A    PENDING    2020-11-02
A    CONFIRMED  2020-11-03
A    CONFIRMED  2020-11-04
A    CONFIRMED  2020-11-05
A    PENDING    2020-11-06
A    PAID       2020-11-07
B    CONFIRMED  2020-11-02
etc.

我想要这个：

id   status     rank
---  ---        ---
A    PENDING    1
A    CONFIRMED  2
A    PENDING    3
A    PAID       4
B    CONFIRMED  1
etc.

意味着考虑更新日期（当然还有状态变化）来对行进行排序和编号，但最终结果中没有订单日期

PS：如您所见，我可以多次从一种状态到另一种状态（PENDING -> CONFIRMED -> PENDING -> etc.）来回切换

非常感谢！

标签： sqlpostgresqlgaps-and-islandsdense-rank

解决方案

您可以将其作为一个差距和孤岛问题来解决。行号之间的差异为您提供每条记录所属的组，然后您可以使用它来聚合：

select id, status, 
    row_number() over(partition by id order by min(update_date)) as rn
from (
    select t.*,
        row_number() over(partition by id order by update_date) rn1,
        row_number() over(partition by id, status order by update_date) rn2
    from mytable t
) t
group by id, status, rn1 - rn2
order by id, min(update_date)

DB Fiddle 上的演示：

编号 | 状态 | rn
:- | :-------- | -：
一个 | 待定 | 1
一个 | 已确认 | 2
一个 | 待定 | 3
一个 | 付费 | 4
乙| 已确认 | 1

推荐阅读