sql - 根据postgresql中的某些值为多行分配唯一ID

Question

我有一个 CTE 返回类似于您在下面看到的内容:

Date   | ID | Category
--------------------
1/1/18 | 1  | Delivery
1/2/18 | 2  | Return
1/4/18 | 3  | Delivery 
1/5/18 | 4  | Middle
1/6/18 | 5  | Return
1/7/18 | 6  | Delivery

我希望添加一个唯一且递增的 ID，该 ID 以 category = 'Delivery' 开头并在 category = 'Return' 之后结束/重置。

例如……

Date   | ID | Category | New ID
-------------------------------
1/1/18 | 1  | Delivery |  1
1/2/18 | 2  | Return   |  1
1/4/18 | 3  | Delivery |  2
1/5/18 | 4  | Middle   |  2
1/6/18 | 5  | Return   |  2
1/7/18 | 6  | Delivery |  3
...

我的大部分经验都是使用 MySQL，所以我可以使用存储过程很容易地做到这一点，但很难用 postgres 来解决。

请注意，这全部由 CTE 定义并保留在查询中 - 我没有要更新的实际基础表

Answer 1

使用渐进式sum()计算Delivery类别的实例数:

with my_data(date, id, category) as (
values
    ('1/1/18', 1, 'Delivery'),
    ('1/2/18', 2, 'Return'),
    ('1/4/18', 3, 'Delivery'),
    ('1/5/18', 4, 'Middle'),
    ('1/6/18', 5, 'Return'),
    ('1/7/18', 6, 'Delivery')
)

select *, sum((category = 'Delivery')::int) over (order by id) as new_id
from my_data

  date  | id | category | new_id 
--------+----+----------+--------
 1/1/18 |  1 | Delivery |      1
 1/2/18 |  2 | Return   |      1
 1/4/18 |  3 | Delivery |      2
 1/5/18 |  4 | Middle   |      2
 1/6/18 |  5 | Return   |      2
 1/7/18 |  6 | Delivery |      3
(6 rows)