Estoy tratando de obtener o calcular la información de cada registro dentro de un conjunto (cuota y valor parcial), explico a continuación con un ejemplo:
| 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 |Con esta información debo agregar otras dos columnas, indicando la cuota y el valor parcial de la misma, para que resulte:
| 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 |Espero ser lo suficientemente explícito, gracias.
Puede obtenerlo usando las funciones de ventana ROW_NUMBER() y SUM().
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;pagar | fecha | valor | insta | 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
dbfiddle aquí
Intente esto y hágamelo saber en caso de cualquier consulta.
select pay, date, value, (select sum(value) from t t2 where t2.pay = t.pay and t2.date <= t.date) as partial, (select count(*) from t t3 where t3.pay = t.pay and t3.date <= t.date) as insta from table t;