sql - Postgres : Get which part is a record within a set

Question

我正在尝试获取或计算一个集合中每条记录的信息(分期付款和部分值(value))，我在下面举例说明:

| pay        |  date       | value |
|:-----------|------------:|:-----:|
| 910006603  | 2017-04-19  | 30    |
| 910006603  | 2017-04-21  | 30    |
| 910006603  | 2017-04-23  | 30    |
| 910006603  | 2017-04-25  | 30    |  
| 910006604  | 2017-04-14  | 45    | 
| 910006604  | 2017-04-18  | 45    |

根据这些信息，我必须添加另外两列，指示分期付款及其部分值(value)，以便得到:

| pay        |  date       | value | insta | partial|
|:-----------|------------:|:-----:|:-----:|:------:|
| 910006603  | 2017-04-19  | 30    | 1     | 30     |
| 910006603  | 2017-04-21  | 30    | 2     | 60     |
| 910006603  | 2017-04-23  | 30    | 3     | 90     |
| 910006603  | 2017-04-25  | 30    | 4     | 120    |

| 910006604  | 2017-04-14  | 45    | 1     | 45     | 
| 910006604  | 2017-04-18  | 45    | 2     | 90     |

我希望足够明确，谢谢。

Answer 1

您可以使用 ROW_NUMBER() 和 SUM() 来获取它 window functions .

create table payments(pay int, dt date, value int);
insert into payments values
(910006603, '2017-04-19', 30),
(910006603, '2017-04-21', 30),
(910006603, '2017-04-23', 30),
(910006603, '2017-04-25', 30),
(910006604, '2017-04-14', 45),
(910006604, '2017-04-18', 45);

select pay, 
       dt as date, 
       value, 
       row_number() over (partition by pay order by dt) as insta,
       sum(value) over (partition by pay order by dt) as partial
from   payments;

      pay | date       | value | insta | partial--------: | :--------- | ----: | ----: | ------:910006603 | 2017-04-19 |    30 |     1 |      30910006603 | 2017-04-21 |    30 |     2 |      60910006603 | 2017-04-23 |    30 |     3 |      90910006603 | 2017-04-25 |    30 |     4 |     120910006604 | 2017-04-14 |    45 |     1 |      45910006604 | 2017-04-18 |    45 |     2 |      90

dbfiddle here