Как переносить баланс на следующие дни?

Question

[Владимир]

Таблица активности пользователя за каждый час и балансом за этот час. Но пользователь может быть неактивен несколько дней и баланс должен перенестись в подсчетах на следующий день. Необходимо посчитать сумму балансов пользователей за каждый день.
Пронумеровал

ROW_NUMBER ( ) OVER ( PARTITION BY DATE :: DATE, userid ORDER BY DATE DESC )

и взял последнюю (максимальную) запись за день пользователя.
Получил на входе:

date | userid | balance
2022-01-01 | 123 | 1000
2022-01-05 | 123 | 500

Что хочу получить на выходе:

2022-01-01 | 123 | 1000
2022-01-02 | 123 | 1000
2022-01-03 | 123 | 1000
2022-01-04 | 123 | 1000
2022-01-05 | 123 | 500

lag() и lead() берут только по одной строке, а разрыв между активностями может быть больше. Как это сделать?

Comments

[Akina]

lag() и lead() берут только по одной строке, а разрыв между активностями может быть больше

Вообще-то запрос должен включать опорную таблицу календаря, содержащего все даты периода (используем генератор или рекурсивный CTE). А создание добавляемых записей лучше выполнять не с помощью оконных функций, а латеральным джойном имеющихся данных к календарю.

[Владимир]

Akina,
Я запустил генератор и сделал латеральный джойн. На выходе получил подобное:
date_generate || userid || balance_date || balance
2022-09-04 1112879 2022-09-01 4200
2022-09-04 1112879 2022-08-22 1700
Что дальше сделать, чтоб оставить только нужные мне даты и значения баланса?

Answer 1

[Дмитрий]

WITH

  balance(userid, date, balance_amount) AS (

    VALUES ('1112879', DATE '2022-08-05',700),

           ('1112879', DATE '2022-08-15',1500),

           ('1112879', DATE '2022-08-18',10100),

           ('1112879', DATE '2022-08-22',1700),

           ('1112879', DATE '2022-09-01',4200)

  )

select
    day::date,
	(select balance_amount from balance as t where t.userid = balance.userid and (day::date - t.date::date)>=0 ORDER BY abs(day::date - t.date::date) asc limit 1) as amount,
	balance.userid
from
    generate_series('2022-08-01'::date, now()::date, '1 day'::interval) as day
cross join (select userid from balance group by userid) as balance
ORDER BY day;

Comments

[Владимир]

Если я правильно интерпретировал Ваш код, то у меня скрипт не возвращает нужный результат. Значение баланса не переносится на следующий день. И в день активности берет первое значение баланса и то не всегда. В зависимости от дат начала и конца интервала в generate_series значение баланса разное за один и тот же день.

[Дмитрий]

Владимир,  закиньте тестовые данные например сюда - подправим

[Владимир]

Дмитрий, https://sqlize.online/sql/psql11/fe406c3f9982e6d30...
вроде так, видно?

[Дмитрий]

Владимир,

Такое пойдет?

Первые поля null - потому что раньше 5 числа у нас данных нет.

[Владимир]

Дмитрий, большое спасибо, сейчас все как нужно!