Postgres: Get what part is a record within a set

Question

Postgres: Get what part is a record within a set

Navigation

#1 by (5 votes)

0

Good morning, I am trying to obtain or calculate the information of each record within a set (quota and partial value), I explain it below with an example:

| id_pago    |  fecha      | valor |
|:-----------|------------:|:-----:|
| 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    |

With this information I must add two more columns, which indicate the quota and the partial value of it, so that it results:

| id_pago    |  fecha      | valor | cuota | parcial|
|:-----------|------------:|:-----:|:-----:|:------:|
| 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     |

I hope to be explicit enough, thank you very much.

I explain the comment, what is required is to apply a 'distinct but that is in each row according to the model that has a query:

Case 1: The query has payments made with installments and parcels

| id_pago    |  fecha      | modelo   | modelos            |
|:-----------|------------:|:--------:|:------------------:|
| 910006603  | 2017-04-19  | cuotas   | [cuotas, parcelas] |
| 910006603  | 2017-04-21  | parcelas | [cuotas, parcelas] |
| 910006603  | 2017-04-23  | cuotas   | [cuotas, parcelas] |
| 910006603  | 2017-04-25  | cuotas   | [cuotas, parcelas] |
| 910006604  | 2017-04-14  | parcelas | [cuotas, parcelas] |
| 910006604  | 2017-04-18  | cuotas   | [cuotas, parcelas] |

Case 2 and 3: The query only brings results with a payment model

| id_pago    |  fecha      | modelo   | modelos  |
|:-----------|------------:|:--------:|:--------:|
| 910006605  | 2017-05-19  | cuotas   | [cuotas] |
| 910006605  | 2017-05-21  | cuotas   | [cuotas] |
| 910006605  | 2017-05-23  | cuotas   | [cuotas] |
| 910006605  | 2017-05-25  | cuotas   | [cuotas] |
| 910006606  | 2017-05-14  | cuotas   | [cuotas] |
| 910006606  | 2017-05-18  | cuotas   | [cuotas] |

| id_pago    |  fecha      | modelo   | modelos    |
|:-----------|------------:|:--------:|:----------:|
| 910006607  | 2017-06-19  | parcelas | [parcelas] |
| 910006607  | 2017-06-21  | parcelas | [parcelas] |
| 910006608  | 2017-06-23  | parcelas | [parcelas] |
| 910006608  | 2017-06-25  | parcelas | [parcelas] |
| 910006608  | 2017-06-14  | parcelas | [parcelas] |
| 910006609  | 2017-06-18  | parcelas | [parcelas] |

The previous results of the columns that must also show quota and partial are ignored.

sql postgresql

asked by AndreFontaine 24.04.2017 в 17:37

source

1 answer

What is the equivalent of the VBA keyword "TO" in C #? Push notification certificates

score 5 · Accepted Answer

You can use window functions for this:

SELECT *,
       ROW_NUMBER() OVER(PARTITION BY id_pago ORDER BY fecha) as cuota,
       SUM(valor) OVER(PARTITION BY id_pago ORDER BY fecha) as parcial
FROM TuTabla;

Here is a demo with the code.

And the results are:

╔═══════════╦════════════╦═══════╦═══════╦═════════╗
║  id_pago  ║   fecha    ║ valor ║ cuota ║ parcial ║
╠═══════════╬════════════╬═══════╬═══════╬═════════╣
║ 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 ║
╚═══════════╩════════════╩═══════╩═══════╩═════════╝