插入一列以不同的药物治疗

Question

我有一个包含耐心治疗日期的数据库，如下所示：

pacient_id |       date |    type | finish
         1 | 2021-01-05 | routine |   null
         1 | 2021-01-10 | routine |   null
         1 | 2021-01-22 | routine |   null
         1 | 2021-01-30 | routine |    yes
         1 | 2021-02-04 | routine |   null
         1 | 2021-02-12 | routine |   null
         1 | 2021-02-19 | routine |   null
         1 | 2021-02-22 | routine |    yes

我需要在查询中添加一个新列，以显示咨询是第一次治疗还是第二次治疗，例如：

pacient_id |       date |    type | finish | treatment
         1 | 2021-01-05 | routine |   null |         1
         1 | 2021-01-10 | routine |   null |         1
         1 | 2021-01-22 | routine |   null |         1
         1 | 2021-01-30 | routine |    yes |         1
         1 | 2021-02-04 | routine |   null |         2
         1 | 2021-02-12 | routine |   null |         2
         1 | 2021-02-19 | routine |   null |         2
         1 | 2021-02-22 | routine |    yes |         2

当完成列=是时，则治疗完成。该表有很多 pacients：

pacient_id |       date |    type | finish
         1 | 2021-01-05 | routine |   null
         2 | 2021-01-10 | routine |   null
         5 | 2021-01-22 | routine |   null
         2 | 2021-01-30 | routine |    yes
         1 | 2021-02-04 | routine |   null
         3 | 2021-02-12 | routine |   null
         9 | 2021-02-19 | routine |   null
         1 | 2021-02-22 | routine |    yes

所以我订购 pacient_id 和日期。感谢您的帮助。

Answer 1

试试这个：

SELECT pacient_id, date, type, finish
     , 1 + count(*) FILTER (WHERE finish = 'yes') OVER (PARTITION BY pacient_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS treatment
  FROM your_table

在 dbfiddle 中查看测试结果